April 8, 2009 at 11:41 pm
When i am creating a new table how to identify whether i should create a Clustered or Nonclustered Index on this table? In terms of performance which one is better?
April 9, 2009 at 12:06 am
If you have a primary key set on that table then a clustered index will be created .The other columns which are widely used in the queries and which are used in the 'where' clause you can create non-clustered indexes on them.But the creation of indexes should be balanced and you need to optimise it by testing .More number of indexes results in slow inserts and also increases the maintenance costs.
April 9, 2009 at 12:08 am
You need info about different things before deciding on the indexes. Some of them are
Volume of data in the table (IMP),
Type of queries that are going to hit the table (IMP)
Purpose of the data , Connectivity , Concurrent connections, Server Hardware..... There are other considerations also but offhand i can think of these.
Of these the first two are most important and index design is influenced the most by them.
Clustered index keep the actual data in the leaf pages of the index in a sorted order. In many of the cases clustered index also functions as the Primary key/unique index and so you would want your most "selective" columns to be part of the clust index.
"Keep Trying"
April 9, 2009 at 12:38 am
best practices are
* frequently used tables must have a clustered index
* no need create index on tables having little records or columns having duplicates (like country, gender etc).
* avoid duplicate index sets like (col1,col2, col3) and (col1, col3,col2)
* create indexes on columns which are frequently used in WHERE and JOINS.
guidelines for selecting clustered indexe key
* columns that contain large number of unique values
* queries that returns range of values.
* columns that are accessed sequentially
* queries that returns large resultset
* columns which are not updated frequently.
guidelines for selecting nonclustered indexe key
* queries dont return large resultset.
* columns frequently involved in WHERE having exact match
April 9, 2009 at 1:47 am
suhasini.m (4/8/2009)
When i am creating a new table how to identify whether i should create a Clustered or Nonclustered Index on this table? In terms of performance which one is better?
It's not a case of which is best. Both are usually needed. There can be only one clustered index on a table and it is recommended practive to have a clustered index on every table.
As for nonclustered indexes, create as necessary to support the queries that run against the table.
For some insight into choosing indexes, take a look at these:
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 1:58 am
geramkumar (4/9/2009)
* no need create index on tables having little records or columns having duplicates (like country, gender etc).
Not necessarily. If the columns are filtered on often it may be very beneficial to have an index on the column even if there are lots of duplicates. The index may need to be covering for SQL to use it.
* avoid duplicate index sets like (col1,col2, col3) and (col1, col3,col2)
Those aren't duplicates. For two indexes to be duplicates they must have the same columns in the same order. There are queries that could effectivly used the first of those indexes, but not the second and vis versa.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 8:28 am
Choosing the index is purely based on how the table is being queried and what is are business requirement that table is addressing.
check out the below link for some considerations:
http://www.mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm
April 11, 2009 at 11:50 pm
With one notable exception, the misinformation in this thread is a bit scary!
Rather than try to write my own mini-article, I would point the interested reader to the excellent TechNet resources at http://technet.microsoft.com/en-us/library/ms189271(SQL.90).aspx
The articles under that root are some of the clearest and best explanations I have seen.
/Paul
edit: broken link
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2012 at 5:18 am
Ramkumar (LivingForSQLServer) (4/9/2009)
best practices are* frequently used tables must have a clustered index
* no need create index on tables having little records or columns having duplicates (like country, gender etc).
* avoid duplicate index sets like (col1,col2, col3) and (col1, col3,col2)
* create indexes on columns which are frequently used in WHERE and JOINS.
guidelines for selecting clustered indexe key
* columns that contain large number of unique values
* queries that returns range of values.
* columns that are accessed sequentially
* queries that returns large resultset
* columns which are not updated frequently.
guidelines for selecting nonclustered indexe key
* queries dont return large resultset.
* columns frequently involved in WHERE having exact match
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply