SQL Server index performance

From the fastest to the slowest:

  1. Non-clustered covering index with included non-key columns
  2. Non-clustered covering index
  3. Clustered index
  4. Non-clustered non-covering index
  5. No Index
  • No index is not always the slowest.
  • If the table is small enough, SQL Server will load the whole table in memory.
  • If the whole table is loaded in memory, SQL Server will **almost** always do a table scan. The table scan is the fastest is the table is small enough.

Table scan and memory

How small of a table is small enough to use a table scan instead of any index? That depends on how much RAM is available, the SQL Server load, the table size, the links ...

You will be able to check it according to the execution plan, CTRL-L or CTRL-M in the Visual Studio.

Indexes

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( COLUMN [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]

Clustered index

  • Only 1 per table.
  • Physically sorts the data in the table.
  • Best to use on columns with low selectivity [Low selectivity means that the percentage of returned rows is high (25%+ of the table).]

Non-Clustered Index

  • Up to 249 allowed per table
  • Best to use on columns that are searched for a single value.
  • Best to use on columns with high selectivity [high selectivity means that the percentage of returned rows is low (<1% of the table).]

Non-Clustered Included Column Index

  • These are Non-Clustered Indexes with non-key columns.
  • Columns that would not be allowed in the index because of either data type or data size to get bigger than 900 bytes [max index key size].
  • The optimizer will use all the columns for searching.