Database Performance Considerations

While designing your database, keep performance in mind. You can't really tune performance later when your database is in production.

1. Normalize and De normalize Tables.
2. Create Table Columns with maximum row size.(If number of columns larger than row size, split table into multiple tables with relations one to one). For column with extended size like binary, ntext, and...etc We prefer to put in separate table.
3. Create a primary key on each table. Each primary key is clustered index.
4. Add calculated columns which you need to avoid complicated queries.
5. Create an index on any column that is a foreign key.
6. Create indexes for your query, Indexes will eliminate rows scanning and locking problems:
   • Plan your indexes. Indexes enhance performance but increasing them cause drawback. Choose indexes make big filtration of your records.
   • Create simple index (indexes have only one column) for Where condition.
   • For sort create composite indexes(indexes have multiple columns).
7. Create Indexed View if Possible.
8. Use owner qualify of your objects (as much as possible) when you reference them in TSQL. Use owner.table instead of just table. 
9. Use set nocount on at the top of each stored procedure and set nocount off at the bottom. 
10. If needn't locking your database table, you take a chance on a dirty read? Ways of dirty work:
    
    • Use the NOLOCK hint.
    • Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
11. Return the columns and the rows you need.use select column1, coulmn2,.. instead of select *
12. Use transactions when appropriate, decrease transaction time.
13. Avoid temp tables as much as you can.
14. Indexes will not work with Functions for example, where LTRIM(Name) = 'Products'.
15. Avoid using OR in your query as much as possible (Cause unexpected plan), Use join instead of it.
16. Avoid negative query NOT IN (Use outer join),<> (Indexes will not work with Negative query). Same thing when you use like as like '%val'.
17. If you use dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC). 
18. Reduce the number of round trips to the server.