Sql Server Indexes

The concept behind indexes is to change the order of the data (clustered index)
or to add metadata (non-clustered index) for improving the performance of queries.

SQL Server use indexes to find data quickly when a query is processed.

Clustered indexes

Clustered indexes define the physical sorting of a database table’s rows in the storage media.
For this reason, each database table may have only one clustered index.
If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table,
SQL Server automatically creates a clustered index on the primary key.

* Physically stored in order (ascending or descending)
* Only one per table
* When a primary key is created a clustered index is automatically created as well.
* If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
* Columns with values that will not change at all or very seldom, are the best choices.

Non-clustered indexes

* Up to 249 nonclustered indexes are possible for each table or indexed view.
* The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
* Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
* Foreign keys should be non-clustered.
If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s