Sleep Command in T-SQL

Question

Is there to way write a T-SQL command to just make it sleep for a period of time   I am writing a web service asynchronously and I want to be able to run some tests to see if the asynchronous pattern is really going to make it more scalable   In order to  mock  an external service that is slow  I want to be able to call a SQL server with a script that runs slowly  but isn t actually processing a ton of stuff

User · Answer

You can also  WAITFOR  a  TIME        RAISERROR  Im about to wait for a certain time      0  1  WITH NOWAIT     WAITFOR TIME  16 43 30 000      RAISERROR  I waited    0  1  WITH NOWAIT

User · Answer

Look at the WAITFOR command   E g   -- wait for 1 minute WAITFOR DELAY  00 01   -- wait for 1 second WAITFOR DELAY  00 00 01    This command allows you a high degree of precision but is only accurate within 10ms - 16ms on a typical machine as it relies on GetTickCount  So  for example  the call WAITFOR DELAY  00 00 00 001  is likely to result in no wait at all

User · Answer

Here is a very simple piece of C  code to test the CommandTimeout with  It creates a new command which will wait for 2 seconds  Set the CommandTimeout to 1 second and you will see an exception when running it  Setting the CommandTimeout to either 0 or something higher than 2 will run fine  By the way  the default CommandTimeout is 30 seconds   using System  using System Collections Generic  using System Linq  using System Text  using System Threading Tasks   using System Data SqlClient   namespace ConsoleApplication1     class Program         static void Main string   args              var builder   new SqlConnectionStringBuilder          builder DataSource    localhost         builder IntegratedSecurity   true        builder InitialCatalog    master          var connectionString   builder ConnectionString         using  var connection   new SqlConnection connectionString                   connection Open             using  var command   connection CreateCommand                        command CommandText    WAITFOR DELAY  00 00 02              command CommandTimeout   1             command ExecuteNonQuery

User · Answer

WAITFOR DELAY  HH MM SS    I believe the maximum time this can wait for is 23 hours  59 minutes and 59 seconds   Here s a Scalar-valued function to show it s use  the below function will take an integer parameter of seconds  which it then translates into HH MM SS and executes it using the EXEC sp executesql  sqlcode command to query  Below function is for demonstration only  i know it s not fit for purpose really as a scalar-valued function   -       CREATE FUNCTION  dbo   ufn DelayFor MaxTimeIs24Hours             sec int           RETURNS     nvarchar 4      AS     BEGIN       declare  hours int    sec   60   60     declare  mins int     sec   60  -   hours   60      declare  secs int     sec -    hours   60    60   -   mins   60        IF  hours  gt  23      BEGIN     select  hours   23     select  mins   59     select  secs   59     --  maximum wait time is 23 hours  59 minutes and 59 seconds       END       declare  sql nvarchar 24     WAITFOR DELAY   char 39  cast  hours as nvarchar 2       CAST  mins as nvarchar 2       CAST  secs as nvarchar 2   char 39        exec sp executesql  sql      return        END   IF you wish to delay longer than 24 hours  I suggest you use a  Days parameter to go for a number of days and wrap the function executable inside a loop    e g        Declare  Days int   5     Declare  CurrentDay int   1      WHILE  CurrentDay  lt    Days     BEGIN      --24 hours  function will run for 23 hours  59 minutes  59 seconds per run       ufn DelayFor MaxTimeIs24Hours  86400      SELECT  CurrentDay    CurrentDay   1     END

[sql-server] Sleep Command in T-SQL?

Examples related to sql-server

Examples related to tsql

Examples related to asynchronous

Examples related to sleep