Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexes Expand / Collapse
Author
Message
Posted Wednesday, April 8, 2009 11:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 2:09 AM
Points: 8, 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?
Post #693746
Posted Thursday, April 9, 2009 12:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 21, 2012 9:26 AM
Points: 1,861, Visits: 652
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.

Post #693754
Posted Thursday, April 9, 2009 12:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #693755
Posted Thursday, April 9, 2009 12:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:54 AM
Points: 153, Visits: 610
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

Post #693765
Posted Thursday, April 9, 2009 1:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #693786
Posted Thursday, April 9, 2009 1:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #693788
Posted Thursday, April 9, 2009 8:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, 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



Post #694050
Posted Saturday, April 11, 2009 11:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #695389
Posted Friday, September 7, 2012 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 10:54 PM
Points: 2, 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

Post #1355905
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse