SQL Server - Clustered key and Nonclustered Key

Hi,
         I like to know the difference between Clustered and non clustered key index deeply, Which will improve
the performance  for select,Insert and Update statements

Clustered indexes store the records in the table in key sequence.  That means that if your index is in an identity column, new rows will be added at the end of the table space.  However, if the index is on a user-created field, records are added where they would logically fall in sequence.  In order to do this, the database engine must retreive the physical record that will hold the new record and reorder the existing records so the new one is added in its correct location.  The updated physical record is then written back to the tablespace.  If there is not enough free space in the physical record, two records will be created, each containing some free space.   Here is a very rudimentary example it is not intended to show the precise logic of row insertion but is a high-level concept example.  In the example, physical records (used to be called blocks in mainframe file systems) are separated by semi-colons and records (rows) are separated by comas.

rec10, rec20, r3c30, rec35, rec40;   rec60, rec70, freespace;   rec80, rec100, rec110,freespace;
you want to add rec25.  The new physical record will be
rec10, rec20, rec25 r3c30, rec35, rec40;
If there is no free space to add the new row, then two physical records are created.
rec10, rec20, rec25, freespace;   r3c30, rec35, rec40,freespace;

So to summarize, clustered indexes work best when the index is on an identity column so that all inserts happen at the end of the tablespace which minimizes the block splitting activity and most searches are done using that same identity column.  It is a trade-off of physical database design whether to add a clustered index to optimize row retrieval or not and I haven't begun to cover the considerations.

As you can see, it is only possible to have a single clustered index on any table since the table rows can only be in one physical sequence at a time. 

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

There can be only 1 Clustered index in a table where as nonclustered index can be upto 249

Clustered is physical sorted index.. non clustered index is faster than clustered index
clustered index the data rows are sorted in sequence for the index key. The data rows are essentially the bottom level of the clustered index. For a non-clustered index, the data rows are not sorted in the index key sequence. The bottom level of the non-clustered index is a set of pointers to whatever rows match each key in the index.

hi,

First the things that they have in common.
Both clustered and nonclustered indexes can be made up of more than one column. The columns are put in the index in the order you specify them in the CREATE INDEX statement (or the order they are shown in the UI). They are also sorted in this order as well. Indexes are first sorted on the first column in the index, then any duplicates of the first column and sorted by the second column, etc. You can have up to 16 columns specified as indexed columns.Neither clustered or nonclustered indexes will guarantee the sort order of the data when it is being returned to you. If the order of the data matters to you, you should always sort the data with the ORDER BY clause in your select statement.Both clustered indexes, and nonclustered indexes take up additional disk space. The amount of space that they require will depend on the columns in the index, and the number of rows in the table. The clustered index will also grow as you add columns to the table (keep reading, it’ll make sense later on).Adding indexes (both clustered and nonclusterd) will increase the amount of time that your INSERT, UPDATE and DELETE statement take, as the data has to be updated in the table as well as in each index. If you have filtered indexes in SQL Server 2008 and the records you are updating are not included in all your indexes, SQL Server should only have to update the values in the indexes which the records are stored within.
Columns of the TEXT, NTEXT and IMAGE data types can not be indexed using normal indexes. Columns of these data types can only be indexed with Full Text indexes.
If you wish to rebuild your indexes online (without locking the table) and have Enterprise edition do not index TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) data types as including columns with these data types will require that you rebuild the index offline.

Clustered Index

• Only one per table
• Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

• Can be used many times per table
• Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95% (ie 95% of the records are unique values)

Hi,

I have found two good links for you to understand the concept

http://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

under FAQ in eggheadcafe

http://www.eggheadcafe.com/sample-code/OracleDatabase/fc720cbf-8234-438f-b055-88e061c20ceb/difference-between-clustered-index-and-non-clustered-index.aspx

In addition to Pat's answer, you also need to selectively choose the key-and non-key columns for your indexes that are used to find and select data. 

Although - there are many blogs and articles that push you towards a clustered index by default - do not underestimate the power of a non-clustered index that can optimize your query performance to a great extent. 

Here is an http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/ that gives precise advice on the point that I mentioned above.. 

So, coming back to your question, which you may followup this thread with - yes, there are many guidelines for creating a clustered and a non-clustered index. Please read MSDN for insight into both. 

http://msdn.microsoft.com/en-us/library/ms190639.aspx
http://msdn.microsoft.com/en-us/library/ms179325.aspx

Hope this helps.

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. As you would expect, the data we will insert into the table will be returned in the same order. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don’t need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.