Object Type |
Convention |
Example |
Tables |
<ObjectName> |
Employee |
Association tables |
<Obeject1><Object2> |
EmployeeDepartment |
Stored Procedures |
<TableName>_<OperationOnTable> |
Employee_Select |
Views |
vw<TableName> |
|
Scalar User-defined functions |
<TableName>_<OperationOnTable> |
Employee_GetTotalHrsWorked |
Table valued user defined functions |
||
Triggers |
TR_<TableName>_<action><description> |
TR_<Employee>_<UpdateEmailAddress> |
Indexes |
IX_<TableName>_<ColumnNamesSeparatedBy_> |
IX_Employee_ID |
Primary Key Constraints |
PK_<TableName> |
PK_Employee |
Foreign Key Constraint |
FK_<TableName1>_<TableName2> |
FK_Employee_Department |
· 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.