Home > SQL Challenge > SQL – Index Clustered vs Non-Clustered

SQL – Index Clustered vs Non-Clustered

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

In SQL server, indexes can be clustered indexes and non-clustered indexes.

When you create a clustered index on a table, all the rows in the
table are “physically” stored in the order of the clustered index key. So, there can be only one clustered index per table.

Non-clustered indexes have their own storage separate from the table data storage. Data are only “logically” stored in the order (with a row locator).

Appropriate indexes improve the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan.

However, too many indexes can slow down other data modification operations (such as INSERT, UPDATE, DELETE). This is because every time data changes in the table, all the indexes need to be updated. Another disadvantage of having too many or unnecessary indexes is that, indexes need disk space, the more indexes you have, more disk space is used.

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: