Nonclustered Indexes

Question

Post reply

Nonclustered Indexes

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

UMG Developer

SSChampion

Points: 13482

December 7, 2010 at 10:22 am

#1259680

Thanks for the question!

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 2

ops

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

SanDroid SSChampion Points: 10068 More actions · Answer 3

GilaMonster (12/7/2010)
Another phrasing problem with the question - the maximum size of an index is not 900 bytes. The maximum size of the key columns is 900 bytes. The index row can technically be up to 8000 bytes.

Although what you say is correct, your wording also seems misleading.

The actual limitation is listed in the reference material as:

Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SanDroid (12/7/2010)
Although what you say is correct, your wording also seems misleading.

Why?

GilaMonster (12/7/2010)
Another phrasing problem with the question - the maximum size of an index is not 900 bytes. The maximum size of the key columns is 900 bytes. The index row can technically be up to 8000 bytes.

Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.
😎

What's different about what you said and what I said?

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

UMG Developer SSChampion Points: 13482 More actions · Answer 5

GilaMonster (12/7/2010)
SanDroid (12/7/2010)
Although what you say is correct, your wording also seems misleading.
Why?
GilaMonster (12/7/2010)
Another phrasing problem with the question - the maximum size of an index is not 900 bytes. The maximum size of the key columns is 900 bytes. The index row can technically be up to 8000 bytes.
Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.
😎
What's different about what you said and what I said?

I think he is trying to say the difference between "total index key size of 900 bytes" and "maximum size of the key columns is 900 bytes". I don't know but can the "total key size" ever be larger than the "size of the key columns"? (Like in the case of a uniquefier automatically added to a clustered index?)

SanDroid SSChampion Points: 10068 More actions · Answer 6

GilaMonster (12/7/2010)
SanDroid (12/7/2010)
Although what you say is correct, your wording also seems misleading.
Why?

He left out the word "key" in his question, you left the word "index" out of your rebuttle.

😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Edit: Never mind.

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

SanDroid SSChampion Points: 10068 More actions · Answer 8

da-zero (12/7/2010)

Furthermore, even if you did take in account included columns, no answer could have been correct.

Considering that you can have up to 1023 (max columns in a table -1) non-key included columns in an index that would be a very differant answer.

Has anyone experianced any limitations when working with non-key columns included in an Index?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events