[ACCEPTED]-Difference between clustered and nonclustered index-non-clustered-index

A clustered index alters the way that the 15 rows are stored. When you create a clustered 14 index on a column (or a number of columns), SQL 13 server sorts the table’s rows by that column(s). It 12 is like a dictionary, where all words are 11 sorted in alphabetical order in the entire 10 book.

A non-clustered index, on the other 9 hand, does not alter the way the rows are 8 stored in the table. It creates a completely 7 different object within the table that contains 6 the column(s) selected for indexing and 5 a pointer back to the table’s rows containing 4 the data. It is like an index in the last 3 pages of a book, where keywords are sorted 2 and contain the page number to the material 1 of the book for faster reference.

You really need to keep two issues apart:

1) the 30 primary key is a logical construct - one of the candidate 29 keys that uniquely and reliably identifies 28 every row in your table. This can be anything, really 27 - an INT, a GUID, a string - pick what makes 26 most sense for your scenario.

2) the clustering key (the 25 column or columns that define the "clustered 24 index" on the table) - this is a physical storage-related 23 thing, and here, a small, stable, ever-increasing 22 data type is your best pick - INT or BIGINT 21 as your default option.

By default, the 20 primary key on a SQL Server table is also 19 used as the clustering key - but that doesn't 18 need to be that way!

One rule of thumb I 17 would apply is this: any "regular" table 16 (one that you use to store data in, that 15 is a lookup table etc.) should have a clustering 14 key. There's really no point not to have 13 a clustering key. Actually, contrary to 12 common believe, having a clustering key 11 actually speeds up all the common operations 10 - even inserts and deletes (since the table 9 organization is different and usually better 8 than with a heap - a table without a clustering 7 key).

Kimberly Tripp, the Queen of Indexing has a great many excellent articles 6 on the topic of why to have a clustering 5 key, and what kind of columns to best use 4 as your clustering key. Since you only get 3 one per table, it's of utmost importance 2 to pick the right clustering key - and not just 1 any clustering key.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

Marc

You should be using indexes to help SQL 28 server performance. Usually that implies 27 that columns that are used to find rows 26 in a table are indexed.

Clustered indexes 25 makes SQL server order the rows on disk 24 according to the index order. This implies 23 that if you access data in the order of 22 a clustered index, then the data will be 21 present on disk in the correct order. However 20 if the column(s) that have a clustered index 19 is frequently changed, then the row(s) will 18 move around on disk, causing overhead - which 17 generally is not a good idea.

Having many 16 indexes is not good either. They cost to 15 maintain. So start out with the obvious 14 ones, and then profile to see which ones 13 you miss and would benefit from. You do 12 not need them from start, they can be added 11 later on.

Most column datatypes can be used 10 when indexing, but it is better to have 9 small columns indexed than large. Also it 8 is common to create indexes on groups of 7 columns (e.g. country + city + street).

Also 6 you will not notice performance issues until 5 you have quite a bit of data in your tables. And 4 another thing to think about is that SQL 3 server needs statistics to do its query 2 optimizations the right way, so make sure 1 that you do generate that.

A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s 22 say that we have a non-clustered index on 21 the EmployeeID column. A non-clustered index 20 will store both the value of the

EmployeeID

AND 19 a pointer to the row in the Employee table 18 where that value is actually stored. But 17 a clustered index, on the other hand, will 16 actually store the row data for a particular 15 EmployeeID – so if you are running a query 14 that looks for an EmployeeID of 15, the 13 data from other columns in the table like 12

EmployeeName, EmployeeAddress, etc

. will 11 all actually be stored in the leaf node 10 of the clustered index itself.

This means 9 that with a non-clustered index extra work 8 is required to follow that pointer to the 7 row in the table to retrieve any other desired 6 values, as opposed to a clustered index 5 which can just access the row directly since 4 it is being stored in the same order as 3 the clustered index itself. So, reading 2 from a clustered index is generally faster 1 than reading from a non-clustered index.

In general, use an index on a column that's 19 going to be used (a lot) to search the table, such 18 as a primary key (which by default has a 17 clustered index). For example, if you have 16 the query (in pseudocode)

SELECT * FROM FOO WHERE FOO.BAR = 2

You might want 15 to put an index on FOO.BAR. A clustered 14 index should be used on a column that will 13 be used for sorting. A clustered index is 12 used to sort the rows on disk, so you can 11 only have one per table. For example if 10 you have the query

SELECT * FROM FOO ORDER BY FOO.BAR ASCENDING

You might want to consider 9 a clustered index on FOO.BAR.

Probably the 8 most important consideration is how much 7 time your queries are taking. If a query 6 doesn't take much time or isn't used very 5 often, it may not be worth adding indexes. As 4 always, profile first, then optimize. SQL 3 Server Studio can give you suggestions on 2 where to optimize, and MSDN has some information1 1 that you might find useful

faster to read than non cluster as data 5 is physically storted in index order we 4 can create only one per table.(cluster index)

quicker 3 for insert and update operation than a cluster 2 index. we can create n number of non cluster 1 index.