ict.ken.be
SQL SERVER - Best Practices
Categories
.Net
(22)
Android
(1)
Angularjs
(2)
Azure
(5)
BitCoin
(1)
CSS
(4)
Docker
(2)
EF
(6)
Gatsbyjs
(1)
Git
(5)
GraphQL
(2)
HTML
(5)
IIS
(12)
Javascript
(17)
Kubernetes
(2)
Linq
(1)
Linux
(5)
Mercurial
(6)
Messagent
(17)
Mobile
(4)
mojoPortal
(2)
Network
(14)
Nodejs
(7)
Notes
(23)
Patterns
(8)
Reactjs
(3)
Red5
(1)
RegEx
(1)
Security
(12)
Shortcuts
(5)
SQL Server
(23)
Testing
(10)
Visual Code / VSCode
(1)
Visual Studio
(16)
WebApi
(7)
Windows
(19)
Related Posts
Enable TCP/IP on mssql
Delete database with name containing (c#)
Drop all the tables, stored procedures, triggers, constraints and all the dependencies
Restore mssql database checklist
GETDATE vs SYSDATETIME
Categories:
SQL Server
Previous Post <<
>> Next Post
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;
Previous Post <<
>> Next Post