" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi

Get Time Difference in Hours, Minutes, Seconds and milliseconds in SQL Server

Posted on November 24th, 2015 by Palash Mondal

Use the following script to get time difference in hours, minutes, seconds and milliseconds in SQL Server. — Declare the Start and End date DECLARE @SDATE AS DATETIME TART_DATE AS DATETIME DECLARE @END_– Set Start and End date SET @START_DATE = GETDATE() SET @END_DATE = DATEADD(SECOND, 3910, GETDATE()) — Get the Result in HH:MI:SS:MMM(24H) format […]

Find all Objects(Procedures Functions) where Key is Used

Posted on November 24th, 2015 by Ankit Arora

This Query can be used if you want to find the list of objects that are using a particular keyword. For example, You have a table and want to find all the procedures using that table.   DECLARE @Keyword varchar(255) SET @Keyword =’Keyword’ SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects […]

Default behaviour of the entity datamodel while setting the default value of a field in SQL’s field designer

Posted on November 24th, 2015 by Swati Gupta

What should be the default behavior of LINQ-to-Entities while setting the default value of a field in SQL’s field designer ? Sometimes it’s difficult to come up with a topic that we feel inspired to write about. I am writing about the problem I encountered while working with entity datamodel. After discussing with the team […]

Did I Really Write Such A Complex Query?

Posted on November 24th, 2015 by Sathyan R

Why would the Error The query processor ran out of stack space during query optimization. Please simplify the query come? Hi Everyone!   I wanted to share a funny and a tricky experience we had a few days back, we wrote a simple query, to delete certain records from a table. So the Query read […]

FileTable in SQL 2012

Posted on November 24th, 2015 by Sathyan R

FileStream is a very powerful feature that was introduced in SQL Server 2008. This is a super hero feature which destroyed many performance villains associated with File System based work in SQL. This is a nice whitepaper on FileStream if you are new to it. SQL 2012 introduces FileTable, which is a great addition, we […]

TRY_PARSE In SQL Server 2012

Posted on November 24th, 2015 by Sathyan R

Many of the programming languages provide a try parse function today to enable easy error handling associated with numeric conversions. These make our life easier while dealing with error prone conversions. SQL Server 2012 introduces the same in SQL space and here is a quick look. Basics: SELECT TRY_PARSE(‘TechZeus’ AS DATETIME) SELECT TRY_PARSE(’00-22-2321′ AS FLOAT) […]

Manipulating SQL SERVER database table’s data with XML XPath and XQuery

Posted on November 24th, 2015 by Bishwaranjan Sandhu

Sometimes we need SQL Server tables’s data(tables with Primary-Foreign key relationship) as a XML type to workout some of our need. With the introduction of the XML datatype in SQL SERVER 2005, it provides the flexibility to generate XML type from the data contained inside sql server tables. Here in this tip, I will be […]

3 Steps to Change Default Value for Select OR Edit Top n Rows in SQL Server Management Studio

Posted on November 24th, 2015 by Swati Gupta

Often we open table in SSMS to view/edit the content by right clicking on the table and NOT by writing simple select or alter query. Almost all of us do that on a daily basis. And now comes the next question How many of you are aware of that you can change the default values […]

Some Useful Date Time conversion Tips in SQL

Posted on November 24th, 2015 by Monalisa Pradhan

1. How to convert Military time to standard time In SQL server 2008 there is one datatype present called “time”. So when we create a column with this datatype, generally it store time in 24 hours format. At the time of selecting also we get that 24 hours time. Using following query helps in getting time […]

How to INSERT, UPDATE or DELETE record in one statement

Posted on November 24th, 2015 by Monalisa Pradhan

Generally we write separate statements to INSERT, UPDATE or DELETE data based on certain conditions. But now in SQL Server 2008 there is a new feature present called MERGE statement using which we can INSERT, UPDATE or DELETE record in one statement. MERGE is a new feature which Microsoft has introduced with SQL Server 2008 […]

New Line character and Forming a comma Separated String in SQL Server

Posted on November 24th, 2015 by Monalisa Pradhan

New Line character in SQL Server Generally we use ‘\n’ as new line character. If we write the same in SQL server Query Analyzer it won’t work. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. […]

Set the Recovery model of the database to Simple/Full

Posted on November 24th, 2015 by Kavita Asnani

The following code iterates through all the databases in SQL Server 2000and sets their Recovery model to “SIMPLE”   USE master GO — Declare a variable to store the value [database name] returned by FETCH. DECLARE @dbname sysname, @cmd varchar(1000) — Declare a cursor to iterate through the list of databases DECLARE db_recovery_cursor CURSOR FOR SELECT […]

How to relocate database from C#.NET (SQL Server 2000)

Posted on November 24th, 2015 by Kavita Asnani

To Move a database from one location to another we basically need to do three things Detach the Detabase from SQL Server Move the datafile and log file to the new location Attach the database back to SQL Server   I am using three System Store Procedures   sp_helpfile is a System Store Procedure that […]

Use of cross apply to show ‘,’ separated values(SQL Server)

Posted on November 24th, 2015 by Manish Tiwari

The following approach can be helpful while showing multiple columns(‘,’ seperated) in a single row of a grid for a particular value.   SELECT Column1,Column1, –Column for which you need ‘,’ seperated values State = substring(EL.ORDER_list, 1, datalength(EL.ORDER_list)/2 – 1) from Table1 c cross apply (select state + ‘,’ as [text()] from Table2 where Condition FOR XML PATH(”)) […]

Importance of Max Degree of Parallelism in SQL Server

Posted on November 24th, 2015 by Arijit Rath

Max Degree of Parallelism is an important setting in a multi processor environment. A value of 0 indicates that all processors will be involved in the query execution process of large queries which consume a lot of cpu. This can sometimes cause issues on the server and result in degraded performance. This setting can be […]

SqlServer Query Optimization Tips

Posted on November 24th, 2015 by Naibedya Kar

Tip 1: Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing the network traffic. While scanning the whole it will lock the Table […]

SQL Server : Multiple record insert

Posted on November 24th, 2015 by Amrit Pattanayak

We may face some situation where we need to insert multiple records in a table.   Using MS SQL Server 2000/2005, following is the most common way. USE [Database Name] GO INSERT INTO Table_Name (Column1, Column2, Column3) VALUES (Valu11, Value21, value31) INSERT INTO Table_Name (Column1, Column2, Column3) VALUES (Valu12, Value22, value32) INSERT INTO Table_Name (Column1, Column2, […]

SQL Server: Restrict the duplicate insertion of records to the database using Store Procedure

Posted on November 24th, 2015 by Rasmita Mohanty

There are several ways we can avoid duplicate record insertion in the database using various programming languages. But, it doesnt have to be from programs always we can avoid duplicate insertion of records from the database too.   Here I am going to explain one of the methods by which we can avoid duplicate record insertion in the SQL Server database […]

Run the existing SQL Script programmatically with SMO

Posted on November 24th, 2015 by Shibani Shubhadarshini

SMO ======== SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server.   We can integrate SMO into any .NET based applications. SMO is also compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, which makes it easy to manage a multi-version environment. Following code is […]

Deleting Duplicate records in SQL Server

Posted on November 24th, 2015 by Rashmita Devi

As I have mentioned in one of my previous tips that using ROW_NUMBER() we can get the serial number which is here. Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table. Lets say I have a Users Table which is having duplicate records. I need to delete […]

Road To SQL Azure

Posted on November 24th, 2015 by CHANDAN KUMAR

With the release of DotNetNuke 6.0 DNN has also joined cloud bandwagon.Here is a short description to the process of shifting of SQLServer  2005/2008 to SQL Azure.   The following steps deals with the simple preocedure to migrate your SQL Server 2005/2008 databases to SQL Azure. 1.In order to access SQL Azure, Microsoft SQL Server Management […]

Forming Comma Separated String in SQL Server

Posted on November 24th, 2015 by Manaswinee Mohapatra

To get the values of a particular field separated by commas in sql server we can use below queries. For handling null values in sql server we can use either COALESCE() or ISNULL() functions.COALESCE function Checks for the first not null in the list. If all arguments are NULL, COALESCE returns NULL. Below is the example […]

Using Having clause with Count function in SQL

Posted on November 24th, 2015 by Mohanish Das

While working on a query to count some rows based upon a condition I found an interesting thing.If you were to use the COUNT function in a query for a condition then we have to use the HAVING clause instead of  WHEREclause. So the following query would return error: $wrong_query = “SELECT count( `fk_mp` ) FROM […]

T- SQL: Query with case-sensitve functionality

Posted on November 24th, 2015 by Rashmita Devi

In SQL Server when we try to get the records by checking the data of a particular column having the Datatype as a Varchar type, then it does not check for Case-Sentitivity.   Lets say I have a Users Table having a column named as “FirstName” ofnChar DataType. It contains following records. PK        FirstName         LastName        PhoneNumber ——-   ————     […]

How to create folders in SQLReport server dynamically ?

Posted on November 24th, 2015 by Rupesh Nayak

In SQLReporting  we can create folders, datasource and deploy reports mannually by opening the report manager. Sometimes it becomes  very cumbersome process to access the report manager and create folders, datasources, deploy reports manually if we have a lots of things to do at same time.   Imports System Imports System.IO Imports CreateReportFolderDemo.RSService Imports System.Web.Services.Protocols […]

MSSQL-Insert records for columns where IDENTITY is set

Posted on November 24th, 2015 by Sankalan Panda

Imagine We have a database table where we have a column set as Primary Key, Identity ON and auto incremented by 1. By any chance we need to insert few records into this table (including the Primary Key column) by selecting from some other existing. What should we do?    We will remove the Identity […]

T-SQL: Finding Top and Bottom records

Posted on November 24th, 2015 by Rashmita Devi

Sometimes we may need to fetch the Top Most and Bottom Most records from a Table.   Lets say we have an Users table having the following records :-   PK              FirstName       LastName ————- —————– ———– 1                John            Hardwick 2                Adwin           Ratzz 3                Charle          Hardwick 4                Rashmita        Devi 5                John            Bette 6                John            Rattz 7                Steve           Ratttz If we want to […]

How to select Random rows in SQL Server using TABLESAMPLE

Posted on November 24th, 2015 by Monalisa Pradhan

Sometimes our table contains large number of records where we need to retrieve some of them (randomly). The TABLESAMPLE clause in SQL Server allows to extract a sampling of rows from a table in the FROM clause. It limits the number of rows returned from a table in the FORM clause to a sample number […]

Selecting records from more than One Database Using Single Query

Posted on November 24th, 2015 by Monalisa Pradhan

Suppose there is requirement for selecting records from more than one Database. Let’s say, you have different databases for different branch (say: BRANCH1_DB, BRANCH2_DB, BRANCH3_DB). For getting all employees name write the following query: SELECT (FIRST_NAME + ‘  ‘ + LAST_NAME) AS EMPLOYEE_NAME FROM BRANCH1_DB.dbo.EMPLOYEE_INFO UNION ALL SELECT (FIRST_NAME + ‘  ‘ + LAST_NAME) AS […]

SQLSERVER Transaction rollback

Posted on November 24th, 2015 by Chandrasekhar Nayak

In SQL SERVER, modifications to data can be rolled back using BEGIN TRANSACTION statement. In T-SQL programming, after the BEGIN TRANSACTION statement if data has been changed using any DML statement, then all these modifications can be rolled back to the previous consistent state which was present before to the BEGIN TRANSACTION statement. But once […]

TRY- CATCH in SQL SERVER 2005/2008

Posted on November 24th, 2015 by Amit Ranjan

With SQL Server 2005’s TRY – CATCH block error handling is now  a cheese bite. Lets have an example CREATE PROCEDURE spGetAccountDetails @bookName varchar(100), @ISBN bigint AS BEGIN END GO BEGIN TRY –Starts the Try Block.. BEGIN TRANSACTION — transaction..starts UPDATE BookShelf  SET BookName= @bookName WHERE ISBN= @ISBN UPDATE Student SET Withdrawed_Book= @bookName WHERE StudentID= @StudentID COMMIT TRAN — Success! END […]

Renaming Database Objects

Posted on November 24th, 2015 by Dipali Chakra

A lot of times we need a change . While working on an existing database,  we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds. In SQL Server this can be done in this manner : 1. Renaming a […]

How to Escape Wildcard Characters While Searching in SQL Server

Posted on November 24th, 2015 by Devi Das

This tip demonstrates how to use special characters like _, %, [  to perform a wildcard search on string values stored in database (SQL Server in this instance) Let us take the example of a table named “TIPS“, which contains a TITLEcolumn (that holds the title of all Tips ). Now, If you want to search a […]

How to swap data between columns in SQL Server

Posted on November 24th, 2015 by Dipali Chakra

At times, we may accidently insert data into a wrong column if we have two identical columns (both having the same data type). To fix this later on we can delete the data from the wrong column and insert it in the proper one, but that becomes a lot of work if you know how to swap data between the two […]

Using SQL JOIN to get data from different tables conditionally

Posted on November 24th, 2015 by Mritunjay Kumar

Last day i came across a situation, where i needed to write a SELECTstatement which will use join with different tables but according to some conditions.  Lets take the below situation:   We have three tables as below : Table : MEMBERS; Columns : MEMBERID, MEMBERNAME, MEMBERPHONE Table : COMPANY; Columns : COMPANYID, FKMEMBERID, WORKPHONE Table : […]

SET and SELECT in SQL Server

Posted on November 24th, 2015 by Dipali Chakra

As most of us know we  have two ways of assigning values to local variables –  SET and SELECT statements. DECLARE @Var1  INT, @Var2 INT SET  @Var1  = 1 SELECT @Var2 = 2   However, there are some differences between the two statements.   1. SET is the ANSI standard which can assign only one variable at a time. If […]

Dealing with NULL values in SQL Server

Posted on November 24th, 2015 by Devi Das

In RDBMS missing data is represented by NULL. Generally it indicates that the data has not been entered into the Database or technically it indicates ‘value is unknown’. Since NULL is unknown so any operation with NULL will return NULL. E.g: SELECT 1 + NULL The above query will return NULL as result. NULL is […]


Posted on November 24th, 2015 by Dipali Chakra

We have two functions in Sql server LEN()  and  DATALENGTH()  which give the same output at most of the time  but differ in some situations.                            LEN() returns the number of characters in the string or variable while  DATALENGTH() returns the number of bytes used to represent a string or expression. We have 4 different cases in which […]

Make Your “Order By” Clause Dynamic

Posted on November 24th, 2015 by Dipali Chakra

SQL Server returns data in a random order. A simple SELECT returns data in the order they were entered.. Although we can have our results returned in the order in which they were entered, but the only way to ensure the order is the use of ORDER BY clause. The following shows how to sort the […]

Fixing Reporting System UI issue in IE8

Posted on November 24th, 2015 by Sachin Kumar

Problem : Reporting service is a powerful Sql Server tool for generating neat and robust reports. It has also good cross-browser compatibility except for some issues with IE8.In IE 8.0, report columns are cut off as they reach the maximum width of the control,so alignment of report in report viewer is not proper, etc. Solution […]

DateTime Mystery In SQL Server

Posted on November 24th, 2015 by Mukesh Nayak

In this tip I want to share one of the more interesting aspects of DateTime datatype which I came across while working in the recent past. We have worked on various aspects and format of DateTime datatype in C# and SQL Server. But here in this section we will discuss about the tick value of […]

How to return Modified data in SQL Server

Posted on November 24th, 2015 by Devi Das

In SQL Server, there is a cool functionality by which you can get the modified data as a result set. Suppose there is a table containing rating information about any post and we want to get user’s previous rating before updating the current / new rating. Then we might write our query like – Here […]

SQL Server Index – V (Index Fragmentation)

Posted on November 24th, 2015 by Pradip Sukla

Most of us are already aware about the benefits of indexes which can speed up the execution of queries, but in this tip we will discuss on the overhead that is associated with it and will also discuss on the way to reduce or fix it. Index Fragmentation is one of them.  Lets discuss what […]

Search datetime data in SQL Server (using ‘where’ clause?)

Posted on November 24th, 2015 by Mayukh Roy

Suppose we need to search all messages by a given date from a Database table, named ‘Message’ we might write the query as given below: SELECT * FROM messages WHERE Date = ‘2003-01-06’ But we shouldn’t be surprised at being returned an empty set because we have got the fundamentals wrong about how dates are stored in database. […]


Posted on November 24th, 2015 by Devi Das

This tip elucidates the difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() in SQL SERVER When we set IDENTITY for a column, the column is known as AUTO INCREMENT column whose value is auto incremented by SQL Server on each insert. When we insert data into a table which has an IDENTITY column then for the IDENTITY column we do […]

Using Pivot Operator in SQL- Server

Posted on November 24th, 2015 by Chittaranjan Nahak

PIVOT can be used to generate cross tabulation reports to summarize data as it creates a more easy understandable data in a user friendly format. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output i.e it rotates a rows to columns and […]

Table variables in SQL Server

Posted on November 24th, 2015 by Subhashish Chaki

Table variables are alternative of temporary tables. As,for instance, temporary tables are table variables store a set of record. The syntax of table variable shown below:   DECLARE @EmpForm TABLE ( EmpId int NOT NULL, EmpName varchar, Bonus decimal(15, 3) )   We can insert values using INSERT INTO clause in table variable. We can use […]

Fetch Nth Column of a Table

Posted on November 24th, 2015 by Mayukh Roy

We have N number of columns in a database table. We need to fetch 4th column of the table directly. We can fetch it easily using the field name(say, nth_field): Select nth_field from tablename But, what if we need to make it dynamic? If we need to fetch the n’th column and end-user inputs the […]

Common Table Expression in SQL Server

Posted on November 24th, 2015 by Subhashish Chaki

Common Table Expressions (CTE) are most powerful feature of SQL Server. This simplifies most of the complex and impossible queries in T-SQL, provides one medium to deal with recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.  The scope of The Common Table […]

How to add row number in a query result set.

Posted on November 24th, 2015 by Subhashish Chaki

Sometimes we may need to add row number in query resultset. We can use the below query for automatically addition of row number in resultset. Suppose we have a table student. SELECT * FROM STUDENT; Output First_Name Last_Name Address M Das BBSR R Chakraborty Cattak F Purokayat Kolkata We need to add row number in […]

Temporary Tables or Table Variables: Choose One!

Posted on November 24th, 2015 by Mayukh Roy

Need a Dynamic Table inside Stored Procedure? We have two options:             a.Temporary Tables             b.Table Variable Which one is better to use? Which one can provide better performance? Lets have a look at both of them by the means of comparison: One: Transaction Logs are not recorded for Table Variables. This simply means, transaction or rollback […]

Calculate Geographical Area using SQL Server

Posted on November 24th, 2015 by Mayukh Roy

Yes, we can calculate area using SQL Server. We can calculate length, area for simple geometry shapes as well as Geographical distance and area (calculating geometrical data is not just the same as dealing with Geographical data, as our earth is not flat!). We have set of points(latitude, longitude) on earth and we need to […]

Limiting the retrieval of data in SQL Server 2008

Posted on November 24th, 2015 by POOJA PANDEY

I was given this task of retrieving limited number of rows by changing the Sql query ofSQL Server 2008. It is simply very easy if we try it in MySql. Here we just need to add a “LIMIT” clause after we write “ORDER BY” clause. For e.g. we can see here : Suppose we have […]

Use Stored Procedure’s result set in Inline Query or in another stored procedure

Posted on November 24th, 2015 by Srikant Biswal

There might be situations where we feel it would be very helpful if we can use the result sets returned from SQL stored procedure on an inline query or another stored procedure. Is it possible in SQl Server ?  Yes it is possible. Please find the below methods, how to implement/achieve this. Lets use the […]

How to find those tables which do not have any auto-incremental column in SQL Server

Posted on November 24th, 2015 by Subhashish Chaki

In our project we need to find out those tables which do not have any auto-incremental column. We have approx 100 tables in our project. It is quite difficult task to search all the table one by one. So how does one resolve this problem? We have used the below query to solve this problem.  ; SELECT […]

How to delete duplicate rows in a table using CTE

Posted on November 24th, 2015 by Subhashish Chaki

Suppose we have a table named SalesOrderDetails. SELECT * FROM SalesOrderDetails;   SalesOrderNo    ItemNo     Qty     UnitPrice           1                 1             1       10000.00           1     […]

How to drop primary key constraint in SQL Server

Posted on November 24th, 2015 by Subhashish Chaki

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key. There are multiple ways to create, alter, drop a primary key constraint. We […]

Remove Multiple Spaces from a string using SQL Server

Posted on November 24th, 2015 by Suman Kumari

In order to replace multiple spaces in a string with a single space, we can use in built function REPLACE. e.g. DECLARE @TestString AS VARCHAR(20) SET @TestString = ‘Test   String’ To remove the extra spaces we need to use SET @TestString= REPLACE(REPLACE(REPLACE(@TestString,’ ‘,’ %’),’% ‘,”),’%’,”) This will result into removal of extra multiple spaces giving result as ‘Test String’ […]

Dynamic Where Clause

Posted on November 24th, 2015 by Damodaranaidu Betha

Some times we may encounter situations like getting the data from data base with numerous parameters which become optional based on the conditions. For example ,   Case 1. Get All The Employees in a specific department.   Case 2. Get All The Employees in a specific department and whose salary is >= 30,000 $. […]

How To Encrypt All Store Procedure In SQL Server Database

Posted on November 24th, 2015 by Joydeep Mukherjee

By encrypting all stored procedures user can only execute but not see or modify any of the SPs   CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), sptext NVARCHAR(MAX) NOT NULL, spname NVARCHAR(100) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ) ) DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), […]

How to take backup of SQL Server database schedule wise

Posted on November 24th, 2015 by Manas Mohapatra

We can take SQL Server database backup in various processes. This is one method which will take database backup daily with time basis automatically. To achieve above the functionality we have to follow some steps. #1. Create a procedure in database with below code for taking database backup.   CREATE PROCEDURE usp_BackupDatabase @databaseName VARCHAR(100) AS […]

Secure Way Of Executing The Update Statement In Sql Server

Posted on November 24th, 2015 by Sisir Patro

There are a few people or we can say there not even a single person who does no mistakes in his life. Mistakes are unwelcomed by everybody. Nobody wants to do mistakes in his/her life. But the other part of this statement is also true, mistakes made people learn the exact things and made people […]

Display Dates In A Range

Posted on November 24th, 2015 by Damodaranaidu Betha

Showing reports is very important thing in most of the  applications. Reports can be in monthly,quarterly,half yearly or yearly. Normally Applications stores transactions in database along with the datetime/date. If  there is no transaction in a day then there is no record in the DB table. Reports can be shown based on the date normally. […]

SQL Query Optimization

Posted on November 24th, 2015 by Sneha Chatterjee

SQL Query Optimization tip using OPTION As we all know that SQL performs 2 types of searching depends upon the indexes you have creted and the Predicates in the query 1.Seek(higher Selectivity) 2.Scan(poor selectivity) See the example, Declare @Title varchar(50)                        //Declared a variable Set @Title = ‘Sales Representative’   // […]

GO (Transact-SQL)

Posted on November 24th, 2015 by Subhashish Chaki

GO is the default batch separator keyword for T-SQL. It signals the end of a batch of Transact-SQL statements to the SQL Server utilities. Actually, GO is not a Transact-SQL statement. It is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. The SQL Server utilities interpret GO […]

Empty or Shrink the database

Posted on November 24th, 2015 by Ritesh Kumar

Sometimes we may get following error message when we will open the sql server: Database cannot be opened due to inaccessible files or insufficient memory or disk space. Solution: Step1 : Check the log file drive. Sql Query : SELECT D.[Name] AS [Database],   Type_desc,   Physical_name,[Size] FROM Sys.Master_files M,Sys.Databases D WHERE M.Database_id= D.Database_id Note: Physical_name represents […]

Use of SET NOCOUNT in stored procedures

Posted on November 24th, 2015 by Subhashish Chaki

Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure. But this message creates an extra overhead on the network. By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the […]

Right Join Using Left Join Clause

Posted on November 24th, 2015 by Dipali Chakra

Most of us we have thought of doing some unusual things. Now one question comes to our mind whether it is possible to do a right join using left join clause??? The answer is YES…but then how? We can do it by using the query below. Note: This will not be possible if duplicates are there […]

Getting List Of tables With Number Of Records Of Each In SQL Server

Posted on November 24th, 2015 by Sachin Kumar

Use the following script to get the number of records present to each table in a database. CREATE TABLE #counts ( table_name varchar(255), row_count int ) EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT ”?”, COUNT(*) FROM ?’ SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC  

Importing SQL Data from SQL Server to Filemaker

Posted on November 23rd, 2015 by http://www.mindfiresolutions.com/filemaker-development.htm

If you want to import SQL Data to Filemaker, first of all you need to create a ODBC Connection to the SQL Server.(if already available use it) Open the ODBC data source administration , Go to the System DSN and Add SQL server you want to connect with proper Login ID and password.Then it will […]

Cross tab generation without using PIVOT

Posted on November 23rd, 2015 by Damodaranaidu Betha

Generation Cross tab results without using PIVOT in all versions of Sql server One of my friends asked me to write a query to get the cross tabs(displaying one of the Column’s Values as columns of a result set /Pivot). Here are the inputs. CREATE TABLE [dbo].[SalesOrderDetails]( [SalesOrderDetailsId] [bigint] NOT NULL, [SalesOrderId] [bigint] NOT NULL, […]

How to install .NET Framework assemblies in SQLServer 2005 for CLR integration

Posted on November 23rd, 2015 by Rupesh Nayak

Some times we need to refer the .NET Framework assemblies and namespaces that are not by default available for development of CLR database in SQLServer. In such scenarios we can register the assemblies in to the SQLServer and then can access them in our CLR objects. The System.Management  namespace/assembly which is not available in SQLServer […]

Limitation of list attribute in cfqueryparam and the methods to overcome | ColdFusion

Posted on November 23rd, 2015 by Abhisek Das

Limitation:       I was working on a project on ColdFusion 9 and SQL Server 2005. I had to pass a very long list into the IN condition of a WHERE clause, so I used a query like this. <cfquery name=”get_emp” datasource=”#APPLICATION.DSN#”> SELECT * FROM employee WHERE clientid = <cfqueryparam value=”#SESSION.clientid#” cfsqltype=”cf_sql_integer”> AND employeeid IN (<cfqueryparam value=”#employee_list#” […]

Insert bulk data using XML serialization and XQuery

Posted on November 23rd, 2015 by Priyojit Mondal

Suppose I have a grid showing the entries of all employees (ID, Name, Exp etc.) and I have to store the checked row’s ID , Name, Address, Contact to a different table. So 1000+ row checked at a time will be a common senario. So there is a very simple and efficient way to insert […]

How to Restore SQL Backup file through .NET Code

Posted on November 23rd, 2015 by Om Pathak

If you need to restore a Sql backup file programmatically through .NET code then you can use the following  lines of code. But first you must remember to include    Microsoft.SqlServer.Management.Smo Dim serverName As String = cbSqlServers.Text.ToString Dim l_objRestore As Restore = New Restore() Dim serverConnection As New Microsoft.SqlServer.Management.Common.ServerConnection serverConnection.ServerInstance = serverName serverConnection.LoginSecure = False serverConnection.Login = txtUserName.Text.Trim.ToString […]

Sending Multiple Records As XML To SQL Server Stored Procedure

Posted on November 23rd, 2015 by Srinivasa Alapati

We may need to send multiple records to database from code behind, at that situation we will make multiple requests to db. If the number of records increased than it may take performance penalty. If we send all the records at once to database then performance will be increased. One of the method to send […]