What is SQL indexing?

Indexes in SQL databases can have the biggest positive impact on the performance of your SQL Server, or they can be your worst nightmare, depending on how you create them.

The original intent with having an index is to reduce the lookup time by making queries run faster and to improve SQL query performance. Without an index, the SQL Server Query Optimizer has to scan the entire table to process your query request. The problem can get worse when the table gets larger. While the correct indexes can boost performance, the poor ones can really slow own the SQL Server operations in both queries and writes.

Two main kinds of indexing:

(1) Clustered: Clustered indexes sort and store the data rows in the table or view based on their key values. There can be only one clustered index per table.

(2) Non-clustered: A non-clustered index contains the non-clustered index key values; each ley value entry has a pointer to the data row that contains the key value.

SQL indexing basics.

Non-unique and non-clustered index on numbers column.

Create table and insert, add to database, create index on numbers column, add to database, table seek (database searches every row and column): 5 IOs to finish the search.

Non-unique and clustered index on numbers column.

Create table and insert, add to database, create clustered index on numbers column, add to database, rewrite database, table seek (database searches every row and column): 4 IOs to finish the search.