Difference between clustered and nonclustered index

Question

I need to add proper index to my tables and need some help   I m confused and need to clarify a few points    Should I use index for non-int columns  Why why not I ve read a lot about clustered and non-clustered index yet I still can t decide when to use one over the other  A good example would help me and a lot of other developers     I know that I shouldn t use indexes for columns or tables that are often updated  What else should I be careful about and how can I know that it is all good before going to test phase

User · Answer

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

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

User · Answer

A comparison of a non-clustered index with a clustered index with an example  As an example of a non-clustered index  let   s say that we have a non-clustered index on the EmployeeID column  A non-clustered index will store both the value of the      EmployeeID   AND a pointer to the row in the Employee table where that value is actually stored  But a clustered index  on the other hand  will actually store the row data for a particular EmployeeID     so if you are running a query that looks for an EmployeeID of 15  the data from other columns in the table like      EmployeeName  EmployeeAddress  etc     will all actually be stored in the leaf node of the clustered index itself   This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values  as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself  So  reading from a clustered index is generally faster than reading from a non-clustered index

User · Answer

You really need to keep two issues apart   1  the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table  This can be anything  really - an INT  a GUID  a string - pick what makes most sense for your scenario   2  the clustering key  the column or columns that define the  clustered index  on the table  - this is a physical storage-related thing  and here  a small  stable  ever-increasing data type is your best pick - INT or BIGINT as your default option    By default  the primary key on a SQL Server table is also used as the clustering key - but that doesn t need to be that way    One rule of thumb I would apply is this  any  regular  table  one that you use to store data in  that is a lookup table etc   should have a clustering key  There s really no point not to have a clustering key  Actually  contrary to common believe  having a clustering key actually speeds up all the common operations - even inserts and deletes  since the table organization is different and usually better than with a heap - a table without a clustering key    Kimberly Tripp  the Queen of Indexing has a great many excellent articles on the topic of why to have a clustering key  and what kind of columns to best use as your clustering key  Since you only get one per table  it s of utmost importance to pick the right clustering key - and not just 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

User · Answer

faster to read than non cluster as data is physically storted in index order we can create only one per table  cluster index   quicker for insert and update operation than a cluster index  we can create n number of non cluster index

User · Answer

In general  use an index on a column that s going to be used  a lot  to search the table  such as a primary key  which by default has a clustered index   For example  if you have the query  in pseudocode   SELECT   FROM FOO WHERE FOO BAR   2   You might want to put an index on FOO BAR  A clustered index should be used on a column that will be used for sorting  A clustered index is used to sort the rows on disk  so you can only have one per table  For example if you have the query  SELECT   FROM FOO ORDER BY FOO BAR ASCENDING   You might want to consider a clustered index on FOO BAR   Probably the most important consideration is how much time your queries are taking  If a query doesn t take much time or isn t used very often  it may not be worth adding indexes  As always  profile first  then optimize  SQL Server Studio can give you suggestions on where to optimize  and MSDN has some information1 that you might find useful

User · Answer

You should be using indexes to help SQL server performance  Usually that implies that columns that are used to find rows in a table are indexed   Clustered indexes makes SQL server order the rows on disk according to the index order  This implies that if you access data in the order of a clustered index  then the data will be present on disk in the correct order  However if the column s  that have a clustered index is frequently changed  then the row s  will move around on disk  causing overhead - which generally is not a good idea   Having many indexes is not good either  They cost to maintain  So start out with the obvious ones  and then profile to see which ones you miss and would benefit from  You do not need them from start  they can be added later on   Most column datatypes can be used when indexing  but it is better to have small columns indexed than large  Also it is common to create indexes on groups of columns  e g  country   city   street    Also you will not notice performance issues until you have quite a bit of data in your tables  And another thing to think about is that SQL server needs statistics to do its query optimizations the right way  so make sure that you do generate that

[sql-server] Difference between clustered and nonclustered index

Examples related to sql-server

Examples related to database-design

Examples related to indexing

Examples related to clustered-index

Examples related to non-clustered-index