SQL Script tips for MS SQL Server

This posting will show you some script tips about MS SQL Server. 1. WaitforThe WAITFOR statement is specified with one of two clauses:(1) The DELAY keyword followed by an amount of time to pass before completing the WAITFOR statement. The time to wait before completing the WAITFOR statement can be up to 24 hours. For example,-- Wait for ten secondes before perforing a select statementWAITFOR DELAY '00:00:10'Select EmployeeID From Northwind.dbo.Employees

(2) The TIME keyword followed by a time to execute, which specifies completion of the WAITFOR statement.For example,-- Wait until 10:00 PM to perform a check of the pubs database to make sure that all pages are correctly allocalted and used.Use pubsBEGINWAITFOR TIME '22:00'DBCC CHECKALLOCEND

2. Enable SQL Debugging-- The SP_SDIDEBUG stored procedure is used by SQL Server for debugging Transact-SQL statementsUse masterGrant Execute on SP_SDIDEBUG to Username

3. Execute a dynamically built string(1) EXECUTE statementWith the EXECUTE statement, all parameter values must be converted to character or Unicode and made a part of Transact-SQL string. For example,DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)/* Build and execute a string with one parameter value. */SET @IntVariable = 35SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' + CAST(@IntVariable AS NVARCHAR(10))EXEC(@SQLString)/* Build and execute a string with a second parameter value. */SET @IntVariable = 201SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' + CAST(@IntVariable AS NVARCHAR(10))EXEC(@SQLString)

(2) SP_ExecuteSQLUsing sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.sp_executesql supports the setting of parameter values separately from the Transact-SQL string:

DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once. */SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'/* Specify the parameter format once. */SET @ParmDefinition = N'@level tinyint'

/* Execute the string with the first parameter value. */SET @IntVariable = 35EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable/* Execute the same string with the second parameter value. */SET @IntVariable = 32EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable

4. SP_HelpTextPrints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.-- This example displays the text of the employee_insupd trigger, which is in the pubs databaseUse PubsExec sp_helptext 'employee_insupd'