ict.ken.be

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;