1. Use Clustered Indexes
Having the clustered index on the primary key is sometimes not the most efficient place for the clustered index to be. A clustered index is the most performant type of index. The whole table is sorted according to the clustered index. If the table is involved in lots of joins based on the primary key, it is probably the USE place for it to be, but if you are continually filtering or grouping on other columns in a table, then you should possibly consider changing the primary key index to Non-Clustered, and putting the clustered index on those filtered or grouped columns.
The following statement removes and existing clustered index on the primary key and replaces it with a non-clustered index:
ALTER TABLE MySchema.SalesOrderHeaderDROP CONSTRAINT PK_SalesOrderHeader
GO
ALTER TABLE MySchema.SalesOrderHeaderADD CONSTRAINT PK_SalesOrderHeaderPRIMARY KEY NONCLUSTERED (SalesOrderID);
Then the following statement adds a new clustered index to a table.
CREATE CLUSTERED INDEX MyClusteredIndexON MySchema.SalesOrderHeader (OrderID)
2. Use Indexed Views
Indexed Views have been around for a while. A view is like a named query, and these days you can add indexes to them. If used correctly, they can cause a massive improvement in execution times, often better than a clustered index with covering columns on the original table. Also, in SQL Server Developer Edition and Enterprise Edition, a view index will also be automatically used if it is the best index even if you don’t actually specify the view in your query!
CREATE VIEW MySchema.SalesByCustomer
WITH SCHEMABINDING
AS
SELECT
soh.SalesTerritoryID, soh.CustomerID, SUM (sod.Quantity * sod.UnitPrice)
FROM
MySchema.SalesOrderHeader soh
INNER JOIN MySchema.SalesOrderDetail sod ON (soh.SalesOrderID = sod.SalesOrderID)
GROUP BY
soh.SalesOrderTerritory, soh.CustomerID
Note the use of the schema binding attribute. This prevents you from changing underlying tables while this view exists, and is necessary if you want to add an index. Some people avoid indexed views for this reason, as the maintenance becomes more complicated as further dependencies to the view are created. The following statement adds an index:
CREATE UNIQUE CLUSTERED INDEX IdxSalesOrderView ON MySchema.SalesByCustomer ( SalesTerritoryID, CustomerID )
Use Indexed Views (SCHEMABINDING cannot be specified if the view contains alias data type columns)
3. Use Covering Indexes
Covering indexes are a feature that was newly added to SQL 2005. Basically, you can create an index optimized for the query itself based on joins, filters and grouping, and then add additional columns that can be retrieved directly from the index for use in select statements, as follows:
CREATE NONCLUSTERED INDEX TestIndex
ON MySchema.SalesOrderDetail (OrderId) INCLUDE (Quantity, UnitPrice)
The above statement causes a non-clustered index to be created on the SalesOrderDetail table. If queries are executed on the OrderId column, the index will be used, and if the only other columns being retrieved are Quantity and UnitPrice, then the query optimizer doesn’t need to retrieve any extra columns from the underlying table. It can just use the index. Because the query optimizer doesn’t need to query the original table, performance is improved.
4. Restrict the queries result set
Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
5. Use views and stored procedures
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.
6. Avoid using cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub query or derived tables, if you need to perform row-by-row operations.
7. Use constraints instead of triggers.
Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.
8. Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.
9. Include SET NOCOUNT ON
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.
Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
10. use UNION ALL statement instead of UNION
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.
11. Avoid data type mismatch for index columns
AVOID
Select
barcode, price
From
products
Where
barcode = 5467;
USE
Select
barcode, price
From
products
Where
barcode = ‘5467’;
barcode is a type of CHAR (10)
12. Index Usage and Functions
AVOID
Select COUNT (*)
From products
Where SUBSTR (brand, 1, 3) =’Arr’;
USE
Select COUNT (*)
From products
Where brand like =’Arr%’;
The reason why application of a function renders the index seek operation useless is because the index pages do not store the data after the application of that function and thus the optimizer cannot perform a seek operation.
13. Composite Index
AVOID
Select
count (*)
From
products
Where
price < 1000
USE
Select
count (*)
From
products
Where
brand > ‘0’ AND price < 1000
Ensure all columns are specified when using composite index
14. Group By, Having
AVOID
Select brand, SUM (price)
From products
Group by brand
Having brand = ‘Arrow’
USE
Select brand, SUM (price)
From products
Where brand = ‘Arrow’
Group by brand
Move conditions from having clause to where clause
15. Nested Selects
AVOID
Select
COUNT (*)
From
products
Where
itemcode IN (
Select
itemcode
from
clothing
where
material = ‘Cotton’
)
USE
SELECT
COUNT (*)
FROM
PRODUCTS P
INNER JOIN CLOTHING C
ON P.BARCODE = C.BARCODE
AND C.MATERIAL = 'Cotton'
Use joins instead of nested selects, whenever possible
16. Multi table join
AVOID
SELECT
COUNT (*)
FROM
T-SHIRT T
INNER JOIN SHIRTS S
ON T.BARCODE = S.BARCODE
INNER JOIN CLOTHING C
ON S.BARCODE = C.BARCODE
INNER JOIN PRODCUT P
ON C.BARCODE = P.BARCODE
USE
SELECT
COUNT (*)
FROM
PRODUCTS P
INNER JOIN CLOTHING C
ON P.BARCODE = C.BARCODE
INNER JOIN SHIRTS S
ON C.BARCODE = S.BARCODE
INNER JOIN T-SHIRT T
ON S.BARCODE = T.BARCODE
When joining multiple tables the smallest table should be specified last
17. NOT IN
AVOID
Select
count (*)
From
Products
Where
barcode NOT IN (
Select
barcode
From
Clothing
)
USE
Select
COUNT (*)
From
Produ cts P
Where
NOT EXISTS (
Select
C.barcode
From
Clothing C
Where
C.barcode = P.barcode
)
Replace NOT IN by NOT EXISTS
18. NOT EQUAL TO
AVOID
SELECT
P.BRAND
FROM
PRODUCTS P
INNER JOIN CLOTHING C
ON P.BARCODE = C.BARCODE
INNER JOIN SHIRT S
ON C.BARCODE = S.BARCODE
WHERE
C.MATERIAL! ='COTTON’
USE
SELECT
P.BRAND
FROM
PRODUCTS P
INNER JOIN CLOTHING C
ON P.BARCODE = C.BARCODE
INNER JOIN SHIRT S
ON C.BARCODE = S.BARCODE
WHERE
C.MATERIAL < 'COTTON'
UNION
SELECT
P.BRAND
FROM
PRODUCTS P
INNER JOIN CLOTHING C
ON P.BARCODE = C.BARCODE
INNER JOIN SHIRT S
ON C.BARCODE = S.BARCODE
WHERE
C.MATERIAL > 'COTTON’
Replace “! =” by Union of “<” and “> “
19. COUNT(*)
AVOID
Select
COUNT (*)
From
PRODUCTS
USE
SELECT
TOP 1 ROWS
FROM
SYSINDEXES
WHERE
ID = OBJECT_ID('PRODUCT') AND INDID < 2