Time Delay While Running T-SQL Query – Sleep Facility in SQL Server

Today, When I was created one store procedure, I fetched one problem. I have created on store procedure which will
generate 25 digits Primary key based on date and time.

I have to generate the primary key for 100k records to insert into another table.
I was using my Store procedure to generate the ID, but it gives me error which is very familiar for us.

Violation of PRIMARY KEY constraint

Now i have searching for function or way to wait for seconds or mili seconds in sql which is vary familiar in our programming language Sleep method to wait for some time before completion of code.


Today I will explain one very small but interesting feature of SQL Server. This is not much known feature of SQL Server. In SQL Server sometime, there are requirement like I described above when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.

Official explanation of WAITFOR clause from Book Online is “Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.”

Please run the following query into your sql server query analyzer.


SELECT GETDATE() WAITFOR DELAY '00:00:00:010' 
-- wait for 10 mill seconds to run the below code SELECT GETDATE()


--
Happy programming