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 […]

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 […]

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 […]

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 […]

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 […]

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) […]

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 […]

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 […]

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 […]

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 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. […]

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 […]

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 […]

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(”)) […]

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 […]

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 […]

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, […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 ——-   ————     […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 : […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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. […]

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 […]

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 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 […]

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 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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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

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 […]

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’ […]

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 $. […]

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), […]

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 […]

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 […]

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 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 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 […]

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 […]

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 […]

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 […]

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  

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 […]

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, […]

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:       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#” […]

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 […]

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 […]

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 […]