Ashish Gupta

· Datatype for a primary key :- uniqueidentifier
· Association tables should have a primary key
EmployeeDepartment should have ID, EmployeeId, DepartmentId.
· Avoid dynamic query. Use Stored procedures only.
· Use SET NOCOUNT ON in the beginning of Stored procedures and Triggers.
· Avoid Cursors wherever possible. Use SET based operations over row-by-row operations. Some of the scenarios are listed below:-
a) If rows in a table needs to be updated on a condition, rather than using a cursor or loop, used UPDATE with Case statements.

UPDATE Table table
SET Column1
Case
WHEN Column2=’1’
THEN Column1=’New value1’
WHEN Column3 = ‘2’
THEN Column1=’New value2’
END
WHERE
Column2=’1’ OR Column3=’2’
[Example]

b) If records in the table need to be updated or deleted, use UPDATE or DELETE with JOINs rather than cursors and loops.
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);

DELETE ab, b
FROM Authors AS a
INNER JOIN AuthorArticle AS ab ON a.AuthID=ab.AuthID
INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
WHERE AuthorLastName=’Tom’;

· Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement.
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE <Condition>

· Create a copy of the table with out any data following way:-
SELECT TOP (0) * INTO EmployeeTest FROM Employee

· COUNT(*) and COUNT(1) are same performance-wise. Use either of them.

· Temparary Tables, Table variables and Common Table Expressions (CTE)
o Temporary tables should be used when the data you hold in them is pretty large. Creating indexes in them would make the data retrieval faster.
o Table variables are faster and good for smaller sets of data.
o Table variables do not have statistics on them and indexes cannot be created on them so they are not good for holding large data.
o Common Table Expressions are good for having smaller sets of data which need not be updated because it’s similar to a derived table but has better readability and you can use the same CTE in the different places in the batch.
o Use CTE for creating recursive queries.