1.
Avoid * in SELECT statement
Practice to avoid * in Select statement since SQL Server
converts the * to columns name before query execution. One more thing, instead
of querying all columns by using * in select statement, give the name of
columns which you required.
1.
-- Avoid
2.
SELECT * FROM tblName
3.
--Best practice
4.
SELECT
col1,col2,col3 FROM tblName
2. Use
EXISTS instead of IN
Practice to use EXISTS to check existence instead of IN since
EXISTS is faster than IN.
1. -- Avoid
2. SELECT Name,Price FROM tblProduct
3. where ProductID IN (Select distinct ProductID from tblOrder)
4. --Best practice
5. SELECT Name,Price FROM tblProduct
6. where ProductID EXISTS (Select distinct ProductID from tblOrder)
3. Avoid Having Clause
Practice to avoid Having Clause since it acts as filter over
selected rows. Having clause is required if you further wish to filter the
result of an aggregations. Don't use HAVING clause for any other purpose.
4.
Create
Clustered and Non-Clustered Indexes
Practice to create clustered and non clustered index since indexes
helps in to access data fastly. But be careful, more indexes on a tables will
slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes
on a table.
5.
Keep
clustered index small
Practice to keep clustered index as much as possible since the
fields used in clustered index may also used in nonclustered index and data in
the database is also stored in the order of clustered index. Hence a large
clustered index on a table with a large number of rows increase the size
significantly. Please refer the article Effective Clustered Indexes
6.
Avoid
Cursors
Practice to avoid cursor since cursor are very slow in
performance. Always try to use SQL Server cursor alternative. Please refer the
article Cursor Alternative.
7. Use UNION ALL inplace of UNION
Practice to use UNION ALL in place of UNION since it is faster
than UNION as it doesn't sort the result set for distinguished values.
8.
Use
Schema name before SQL objects name
Practice to use schema name before SQL object name followed by
"." since it helps the SQL Server for finding that object in a
specific schema. As a result performance is best.
1. --Here dbo is schema name
2. SELECT col1,col2 from dbo.tblName
3. -- Avoid
4. SELECT col1,col2 from tblName
9.
Avoid
unnecessary columns in the SELECT list and unnecessary tables in join
conditions
·
Selecting unnecessary
columns in a Select query adds overhead to the actual query, specially if the
unnecessary columns are of LOB types.
·
Including unnecessary
tables in join conditions forces the database engine to retrieve and fetch
unnecessary data and increases the query execution time.
10.Try
not to use COUNT(*) to obtain the record count in a table
·
To get the total row
count in a table, we usually use the following
Select statement:
SELECT COUNT(*) FROM dbo.orders
This query will perform a full table scan to get the row count.
·
The following query
would not require a full table scan. (Please note that this might not give you
100% perfect results always, but this is handy only if you don't need a perfect
count.)
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('dbo.Orders') AND indid < 2
10.
With
(NOLOCK)
