What columns are eligible to be in non clustered index

Question

What columns are eligible to be in non clustered index

xzd000

SSC Veteran

Points: 298
More actions
July 3, 2010 at 11:05 am

#220667

Hi,
I'd like to know if there is any criteria to be able to reference a column in a non clustered index - Like, should it be /not be a Primary key?
I recently came across this question.
If a table has 5 columns , which are not primary, then how many non clustered indexes cam be created for that table?
Can you provide answer for that?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 1

You can create more than 200 NC indexes on any table if you don't mind duplicates on small tables like this one. With a 5 column table, though, I believe the answer is 2⁵-1 without duplicates. Should you do such a thing? I would think not.

Where did you come across such a question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

xzd000 (7/3/2010)
If a table has 5 columns , which are not primary, then how many non clustered indexes cam be created for that table?

Can be or should be?

Can be? 249 nonclustered indexes (and nothing prevents the primary key columns from being used in nonclustered indexes)

Should be? Impossible to answer that without seeing the queries that run against the table. Indexes should be created to support the queries that run against the table, not for the shear sake of creating indexes.

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

xzd000 SSC Veteran Points: 298 More actions · Answer 3

Well, what about the property of the column to be used as such?

The emphasis is that - The table does not have primary key column. Does that fact affect the creation of non clustered index in any way?

This was asked in interview. (so no ones creating 2^5 indexes :))

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

xzd000 (7/3/2010)
The emphasis is that - The table does not have primary key column. Does that fact affect the creation of non clustered index in any way?

No. The presence or absence of a primary key is irrelevant to the choice of indexes. (other than that you should decide whether the primary key is enforced by a clustered or nonclustered index)

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

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

p.s. Tell the interviewer that this is a stupid question.

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

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

p.p.s. The limitation on indexable columns is clearly laid out in Books Online.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

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

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 7

xzd000 (7/3/2010)
Well, what about the property of the column to be used as such?
The emphasis is that - The table does not have primary key column. Does that fact affect the creation of non clustered index in any way?
This was asked in interview. (so no ones creating 2^5 indexes :))

No... having or not having a primary key in a table doesn't affect the creation of non-clustered indexes. What does affect non-clustered indexes is whether or not a clustered index exists. The interviewer probably doesn't know that a PK only defaults to being the clustered index but doesn't have to be.

So, I agree with Gail... it's a stupid question because the interviewer probably doesn't know the correct answer to his own question. My recommendation is to lookup Primary Key, Clustered Index, and Non Clustered Index in Books Online and see for yourself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (7/3/2010)
What does affect non-clustered indexes is whether or not a clustered index exists.

And even then it just affects the internal structure of the index, not how you create it, which columns you can create one on or how many can be created.

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

xzd000 SSC Veteran Points: 298 More actions · Answer 9

GilaMonster (7/3/2010)
Jeff Moden (7/3/2010)
What does affect non-clustered indexes is whether or not a clustered index exists.
And even then it just affects the internal structure of the index, not how you create it, which columns you can create one on or how many can be created.

Basically the point that non clustered indexes will have to be recreated if a clustered index is created AFTER non clustered index, right?

Thanks Jeff & Gila.

Just posting from the link, for completeness of this thread.

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.
Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

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

xzd000 (7/3/2010)
GilaMonster (7/3/2010)
Jeff Moden (7/3/2010)
What does affect non-clustered indexes is whether or not a clustered index exists.
And even then it just affects the internal structure of the index, not how you create it, which columns you can create one on or how many can be created.
Basically the point that non clustered indexes will have to be recreated if a clustered index is created AFTER non clustered index, right?

Yes, and that the clustering key is always part of a nonclustered index, either key or include depending on whether the nonclustered index is unique or not. (which is why the nonclustered index will have to be recreated if a clustered index is added)

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

DataDog Hall of Fame Points: 3305 More actions · Answer 11

I think there are 325 distinct non-clustered indexes to choose from

given 5 columns - if you ignore the ASC DESC options

some examples:

create index IX_Blah

on dbo.Table (a, b, c, d, e)

create index IX_Blah

on dbo.Table (e, a, b, c, d)

create index IX_Blah

on dbo.Table (a, b, c, d)

create index IX_Blah

on dbo.Table (b, c, d, e)

create index IX_Blah

on dbo.Table (a, b, c)

create index IX_Blah

on dbo.Table (e, b, c)

create index IX_Blah

on dbo.Table (a, b)

create index IX_Blah

on dbo.Table (a, e)

create index IX_Blah

on dbo.Table (a)

create index IX_Blah

on dbo.Table (d)

so I bet the question is designed to check the student understands that

create index IX_Blah

on dbo.Table (a, b)

create index IX_Blah

on dbo.Table (b, a)

are completely different indexes with different performance characteristics

amongst other things

so it is quite a good question - as the answer will be min(325, 249)

and anybody who thinks the answer is less than 249 either doesn't understand composite indexes

or didn't read bol

xzd000 SSC Veteran Points: 298 More actions · Answer 12

How did you arrive at the number of 325?

Lets consider a 4 column table for this example now.

Foll are the possibilities (64 in all).Now whats the math formula for this?;-)

abcd

abdc

acdb

acbd

adbc

adcb

24 (similarly for b,c,d)

abc

acb

acd

adc

adb

abd

24 (similarly for b,c,d)

ab

ac

ad

ba

bc

bd

ca

cb

cd

da

db

dc

12

a

b

c

d

4

24 + 24 + 12 + 4 = 64

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

http://en.wikipedia.org/wiki/Combinatorics

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

DataDog Hall of Fame Points: 3305 More actions · Answer 14

for 5

5 * 4 * 3 * 2 * 1 = 120

for 4

5 * 4 * 3 * 2 = 120

for 3

5 * 4 * 3 = 60

for 2

5 * 4 = 20

for 1

5 = 5

120 + 120 + 60 + 20 + 5 = 325

but for all COMPOSITE indexes you have to consider ASC and DESC also

so the true answer is way higher:

10 * 8 * 6 * 4 * 2 = 3840

10 * 8 * 6 * 4 = 1920

10 * 8 * 6 = 480

10 * 8 = 80

5 = 5

TOTAL = 6325 // total unique non-clustered indexes to choose from

[edit] although you can consider ASC ASC ASC == DESC DESC DESC so you can ignore all indexes with all DESC sort orders

[edit] another thought - asc and desc aren't equivalent - as mass storage reads are faster if blocks are read sequentially