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 123»»»

Nonclustered Indexes Expand / Collapse
Author
Message
Posted Monday, December 6, 2010 9:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,254, Visits: 13,552
Comments posted to this topic are about the item Nonclustered Indexes


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1030993
Posted Monday, December 6, 2010 11:36 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,971, Visits: 369
Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks
Post #1031047
Posted Monday, December 6, 2010 11:43 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
ziangij (12/6/2010)
Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks


Sort of yes. See http://msdn.microsoft.com/en-us/library/ms190197%28v=SQL.100%29.aspx

/T
Post #1031050
Posted Tuesday, December 7, 2010 12:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:02 AM
Points: 1,020, Visits: 1,290
ziangij (12/6/2010)
Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks

Yes it is.
If you check "CREATE INDEX (Transact-SQL)" in BOL, it mentions that: " Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes."


Thanks
Post #1031062
Posted Tuesday, December 7, 2010 12:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:02 AM
Points: 1,020, Visits: 1,290
Straight forward question. Thanks

Thanks
Post #1031063
Posted Tuesday, December 7, 2010 12:34 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Hardy21 (12/7/2010)
ziangij (12/6/2010)
Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks

Yes it is.
If you check "CREATE INDEX (Transact-SQL)" in BOL, it mentions that: " Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes."


Not entierly accurate. The clustered index have an exception to the 16 columns limit. It can be 15 in some cases. From the link in my previous post
"Clustered index is limited to 15 columns if the table also contains a primary XML index or any spatial indexes."

/T
Post #1031067
Posted Tuesday, December 7, 2010 1:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 13,261, Visits: 10,138
Nice and easy. Thanks!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1031076
Posted Tuesday, December 7, 2010 2:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:12 AM
Points: 1,610, Visits: 5,482
Why does the question specify SQL 2008 when the limit for this, AFAIK, was the same in earlier versions? Thought it might be a tricksy question about filtered indexes till I saw the answers!
Post #1031086
Posted Tuesday, December 7, 2010 2:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
Good question, thanks.

Further info on size limits can be found here


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1031089
Posted Tuesday, December 7, 2010 3:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:23 AM
Points: 449, Visits: 1,856
Slightly misleading wording. It should read "how many columns can be used in a nonclustered index key". The important part is to make clear that you are talking about the key columns of the index. Using the word "included" can be misinterpreted, especially since SQL 2005 where that is a keyword in indexing.


Regards,

WilliamD
Post #1031101
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse