What Is SQL Indexing

Transcript

Expand

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.


What is SQL Indexing?

The original intent with having an index is to reduce the look up 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 down the SQL server operations in both queries and writes.

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.
A Non-Clustered index contains the non-clustered index key values; each key value entry has a pointer to the data row that contains the key value.

In this infographic find out what is the main difference between a clustered index and a non-clustered index and how they help you in your SQL query searches. Learn More at →

Start a FREE Trial of SQL Diagnostic Manager
Share This
Contact IDERA: