SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes


Indexes

Author
Message
goodlaugh
goodlaugh
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 50
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?
SQL_DBA_3
SQL_DBA_3
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2344 Visits: 658
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.
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3471 Visits: 1865
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"
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 620
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88927 Visits: 45284
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/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88927 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1911 Visits: 409
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

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16074 Visits: 11355
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
SQLblog.com
@SQL_Kiwi
meena.4316
meena.4316
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search