1. Every table should have Primary Key Constraint on column with Unique Values.
2. Every table should have a Clustered Index preferably (not mandatory) on Primary Key column.
3. Explicitly define the Foreign Key constraint on Foreign key column.
4. Every Foreign Key should have a Non Clustered Index.
5. You can create Composite Index but try to avoid too many columns in a Composite Index.
6. Define the Unique constraint on unique column or set of columns.
7. Create the Covering Index on mostly referred columns to avoid look ups.
8. Create Filtered Index on column with known value which is going to be used most frequently.
9. Define the Default Constraint wherever applicable.
10. Define the Check Constraint on columns with fixed set of values.
11. Define the extended properties for the columns to add description about the table, column, value it will hold etc. so that other people can understand the schema easily.
12. Avoid creating Clustered Index on CHAR, VARCHAR or NVARCHAR columns.
13. Do not create Index on column with LOB Data Type.
14. In case of Composite Index, the first column of Index should be with good Cardinality & Selectivity.

Cardinality refers to the uniqueness of data values contained in a column. High cardinality means that the column contains less number of duplicate values. Low cardinality means that the column contains a lot of duplicate values.

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer.

All data and information provided on this site by Author is for informational purposes only. Author makes no representations as to accuracy, completeness, correctness, suitability, or validity of any information on this site and will not be liable for any errors, omissions, or delays in this information or any losses, injuries, or damages arising from its display or use.

Please don’t use any of the examples discussed in this article in Production without evaluating it based on your need.

• Database Design & Architecture
• Performance Tuning
• SQL Server