Sql Server – Coding Guidelines and best practices

Object Type

Convention

Example

Tables

<ObjectName>

Employee
Department

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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: