Be aware when you use date to filter the records

 When you execute the below query, as you expect, the result will be “September 26th 2049”

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/49'
SELECT @DATE

Now, Lets see what happen when we pass 50 instead of 49

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/50'
SELECT @DATE

You will get the result as “September 26th 1950”; this is because by default, SQL Server interprets two-digit years based on a cutoff year of 2049.

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/2050'
SELECT @DATE

Now, as you expect you will get the result as “September 26th 2050”; so it is always good to specify the four-digit year.

Notes from BOL

“By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.”

How to insert values into an Identity column in SQL Server

If the column is defined as identity then SQL Server will automatically insert the values in to identity column whenever new records get inserted. In some situation we may need to insert values into the identity column explicitly. In the following section I have demonstrated how we can achieve this. For demonstration purpose let’s create two tables with identity column and populate with some records.

CREATE TABLE TBLIDENTITY (IID INT IDENTITY (1, 1), ENAME VARCHAR (20) DEFAULT 't-s-q-l.BlogSpot')

INSERT INTO TBLIDENTITY DEFAULT VALUES
INSERT INTO TBLIDENTITY DEFAULT VALUES
INSERT INTO TBLIDENTITY DEFAULT VALUES

CREATE TABLE TBLIDENTITY_TEMP (IID INT IDENTITY (4, 1), ENAME VARCHAR (10) DEFAULT 't-s-q-l')

INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES
INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES
INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES

let’s assume, we want to insert the records from the table TBLIDENTITY_TEMP to TBLIDENTITY whilst we need to preserve the IID column value of TBLIDENTITY_TEMP in TBLIDENTITY. In order to do this, we have to set IDENTITY_INSERT ON.

SET IDENTITY_INSERT TBLIDENTITY ON
INSERT INTO TBLIDENTITY (IID, ENAME)
SELECT * FROM TBLIDENTITY_TEMP
SET IDENTITY_INSERT TBLIDENTITY OFF

Concatenate column values from multiple rows into a single column in SQL Server

I have created a simple table to demonstrate how to concatenate the values from multiple rows in to one column.

CREATE TABLE #tblProduct (product_Id INT IDENTITY(1,1), product_name VARCHAR(10))

Insert some sample records in to this temporary table

INSERT INTO #tblProduct (product_name) VALUES('AAA')
INSERT INTO #tblProduct (product_name) VALUES('BBB')
INSERT INTO #tblProduct (product_name) VALUES('CCC')
INSERT INTO #tblProduct (product_name) VALUES('DDD')
INSERT INTO #tblProduct (product_name) VALUES('EEE')
INSERT INTO #tblProduct (product_name) VALUES('FFF')
INSERT INTO #tblProduct (product_name) VALUES('GGG')

Once you have populated the table with data, Run this query to get the row values in to one column.

DECLARE @productarr VARCHAR(8000)
SET @productarr = ''
SELECT @productarr = @productarr + '"' + CAST(product_Id AS VARCHAR) + '",' FROM  #TBLPRODUCT
ORDER BY product_Id

SELECT LEFT(@productarr,LEN(@productarr)-1)

DROP TABLE #tblProduct

That’s all you have done with the job

How to find which query is currently running in SQL Server 2000.

The below given function will return the currently running query for the process id.

STEP1: Create the below function in your server database.

CREATE FUNCTION SHOW_MY_PROCESS (@SPID INT)
RETURNS VARCHAR(8000)AS
BEGIN   
    DECLARE @sql_handle BINARY(20), @handle_found BIT   
    DECLARE @stmt_start INT, @stmt_end INT   
    DECLARE @line NVARCHAR(4000), @wait_str VARCHAR(8)   
    DECLARE @sql_process AS VARCHAR(8000)   
    SELECT  @sql_handle = sql_handle, @stmt_start = stmt_start/2,
    @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END   
    FROM master.dbo.SYSPROCESSES   
    WHERE spid = @spid AND ecid = 0
    SELECT @sql_process = SUBSTRING(TEXT, COALESCE(NULLIF(@stmt_start, 0), 1),
    CASE @stmt_end WHEN -1 THEN DATALENGTH(TEXT) ELSE (@stmt_end - @stmt_start)END)
    FROM ::fn_get_sql(@sql_handle)
    RETURN @sql_process
END

STEP2: Run the below query

SELECT dbo.SHOW_MY_PROCESS(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50

How to find the Date is first day of the Week in SQL.

Some time you asked to find the given date is first day of the week or not?
here is the code.

IF DATEPART(DW,GETDATE()) = @@DATEFIRST
BEGIN
    PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS FIRST DAY OF THE WEEK'
END
ELSE
BEGIN
    PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS NOT A FIRST DAY OF THE WEEK'
END

How to find whether the date is end date of the month in SQL

To know the given date is end date of the month, You can use the dateadd and day functions. that is, you should add 1 day to the given date and check whether the result date's day is 1 or not. If the result date's day is one then the given date is end date of the month otherwise it is not the end date of the month.

SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIOD

Tips to write better SQL

There are different ways of doing optimization

  1. Change the logic to get the output faster
  2. Write or re-write the code in better way.

Let me start with the second point first i.e. “Write or re-write the code in better way”.
There are many tricks to improve the performance of application but it purely depends on business logic of the application.

  • Make sure the dataset (table) you use for querying is always smaller in size.The reason behind this is, smaller the table size lesser the physical I/O operation.
  • Create INDEX when it is required. Because index creation itself will take time. So before you create an index, compare the time difference.
  • Use the index properly. Eg; If you create index on col1, col2, col3. Try to use all the three columns in the WHERE clause in the same order.
  • Don’t use User defined functions (UDF) in the select query.
  • Have the practice of checking the execution plan of the any query you write.
  • If the data in the table is unique then don’t issue the DISTINCT as this will reduce the sorting operation. “Still It depends on your business need”
  • Joining the same table multiple times may become worse and that depends on the table volume
  • When you create index use WITH SORT_IN_TEMPDB
You can find more SQL Server topics in this blog. continue reading…