ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

SQL SERVER - Best Practices

Categories: SQL Server
SELECT queries
  • use (NOLOCK)

  • use EXISTS instead of IN
    eg. SELECT * FROM HumanResources.Employee E WHERE E.EmployeeID = ( SELECT EA.EmployeeID FROM HumanResources.EmployeeAddress EA WHERE EA.EmployeeID = E.EmployeeID)
    SELECT * FROM HumanResources.Employee E WHERE EXISTS ( SELECT EA.EmployeeID FROM HumanResources.EmployeeAddress EA WHERE EA.EmployeeID = E.EmployeeID)

  • check if left join is faster
    eg. SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder);
    SELECT p.ProductID FROM Production.Product p LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID WHERE w.ProductID IS NULL;

INSERT queries
  • use UNION ALL
    eg. INSERT INTO MyTable (FirstCol, SecondCol)
    SELECT 'First' ,1
    UNION ALL
    SELECT 'Second' ,2
    UNION ALL
    SELECT 'Third' ,3

  • Use INSERT TOP where order is not important
    eg. INSERT INTO InserTesttValue (ID) SELECT TOP (2) ID FROM TestValue
    INSERT TOP (2) INTO InsertTestValue (ID) SELECT ID FROM TestValue

STORED PROCEDURE
  • include SET NOCOUNT ON statement

  • use schema name with object name

  • use IF EXISTS (SELECT 1) instead of (SELECT *)
    eg. IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'MyTable' AND type = 'U')

  • try to avoid using SQL Server cursors whenever possible

  • keep the Transaction as short as possible

  • use TRY-Catch for error handling
    eg. BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH

  • do NOT use the prefix “sp_” in the stored procedure name

  • call the stored procedure with qualified name (with schema)

  • use the sp_executesql stored procedure instead of the EXECUTE statement
    eg. DECLARE @Query NVARCHAR(100); SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'; EXECUTE sp_executesql @Query, N'@Age int', @Age = 25;