Indexes - part 2

We learned in part 1 some concept of indexes and let's continue by see different type of indexes.

So what is Clustered Index?
The clustered index stores the real data or rows at the leaf level of index.
Note:

• Only one clustered index can contain on a table or view.
•  The indexed values can be sorted in either ascending or descending order.
•  Data in a table is sorted only if a clustered index has been defined on a table.

Terms: A clustered table can be referred to a table has a clustered index and Heap table for a table does not have a clustered index. When you create PK by default Sql server create a clustered index for your table.

Nonclustered Index
The nonclustered index only contains the pointer to actual data rows that means there is additional step in order to get the actual data.

There are differences of nonclustered index if it's on clustered table or heap table so if it's on a clustered table the pointer points to the clustered index to move to actual data row (the leaf node of the nonclustered index contains the clustered index keys) and if it's on heap table the pointer points to actual data row.

Note:

•  nonclustered index cannot be sorted
•  you can create more than one nonclustered indexes for a table or view
•  SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999

Index types:

1. Composite index: you can include up to 16 columns in an index as long as the index does not exceed the 900 byte limit.

2. Unique index: to ensures the uniqueness of each value even if your index contains composite index. For example if you create an index on EmpId and DeptId columns so together must be unique. A unique index is defined when you create a primary key for one column or more or add unique constrain.

Term: Column Cardinality
The cardinality of column is referred to the uniqueness of data inside a particular column

How to design indexes
Some considerations before index you table:

1. More indexes means more disk space so you need to implement the necessary indexes
2. When you update data the indexes automatically updated so this can affect performance

You should consider the following guidelines when planning your indexing strategy:

• For tables that are heavily updated, use as few columns as possible in the index. 
•  If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance. 
•  Use indexes carefully on small tables because the query engine might take longer to navigate the index than to perform a table scan. 
•  For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values. This is why the primary key is often used for the table’s clustered index, although query considerations should also be taken into account when determining which columns should participate in the clustered index.
• The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs. 
•  For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = 'Fadi') should be listed first. 
•  You can also index computed columns if they meet certain requirements. For example, the expression used to generate the values must be deterministic.
•  Try to insert or modify as many rows as possible in a single statement, rather than using multiple queries. 
•  Create nonclustered indexes on columns used frequently in your statement’s predicates and join conditions. 
•  Consider indexing columns used in exact-match queries. 
•  Index should be created on columns used in WHERE clause, Order By, Group By, Distinct etc.
•  All columns used in WHERE clause should be included in single index to get best results.
•  Do not create indexes unless you need them, too many indexes will slow INSERT, UPDATE and DELETE.

Ref : i got the above tips from the following url

When to use it:

Clustered index:

•  For use on columns that are frequently searched for ranges of data
• For use on columns with low selectivity

Nonclustered index:

• For use on columns that are searched for single values
•  For use on columns with high selectivity