Clustered Indexes Effects on performance can be notably improved or detrimentally negative based on appropriate use of indexes. This plays an important part in SQL Server development and on the exam, so we placed this related information into a topic of its own, which is covered in Chapters 10 and 12. As seen in the previous sections, a clustered index is a type of index in which the logical order of the key values determines the physical order of the data stored on disk. Because a clustered index shows how the physical data of a table is stored, there can be only one clustered index per table. Nonclustered Indexes Nonclustered indexes are indexes in which the logical order of the key values in the index is different from the physical order of the rows in the indexed table. Index Selectivity To find the selectivity of a query, use the following formula: [View full width] [View full width] Selectivity ratio = [100 * (Total number of distinct index rows)] / (Total number of rows  in the table)] Here are some guidelines you should look at when choosing not to index a column: -
If the index is never used by the query optimizer. -
If the column values exhibit low selectivity, often greater than 95% for non-clustered indexes. -
If the column(s) to be indexed are very wide. -
If the table is rarely queried. -
Do not index columns that are not used in WHERE clauses, aggregated, or used in sorting. Indexed Views Here are some of the benefits of indexed views: -
They increase performance of queries that use joins and aggregates to process numerous rows. -
Data residing in an indexed view is automatically updated when a base table gets modified. -
Views that aggregate and join data are improved because when an index is created on a view, the view is executed and the resultset is stored in the database. -
The first time you create an index on a view, it has to be of type unique clustered; after this has been created, additional nonclustered indexes can be created. Indexed Views Requirements and Restrictions There are some prerequisites that need to be taken before creating an indexed view: -
The first index on a view must be a clustered index. -
Non-deterministic functions are not allowed in the view's definition. -
The view must be created using WITH SCHEMABINDING (which prevents the dropping and altering of tables participating in the indexed view). NOTE Non-Deterministic Functions Are Not Allowed on Indexed Views Deterministic functions are functions that return the same result each time they are called. ABS is a deterministic function because it always returns the same outcome each time a new argument is supplied. GETDATE , on the other hand, is non-deterministic because it always results in a new value each time it is evaluated.
-
Modification of session-level settings are required. These are outlined in Table 8. Table 8. Mandatory Settings | Session Level Setting | Value | | ANSI_NULLS | On | | ANSI_WARNINGS | On | | ARITHABORT | On | | CONCAT_NULL_YEILDS_NULL | On | | QUOTED_IDENTIFIERS | On | | ANSI_PADDING | On | | NUMERIC_ROUNDABORT | Off |  |
