Wide index

Question

Post reply

Wide index

Mark F-428640

Hall of Fame

Points: 3659
More actions
August 15, 2007 at 7:33 am

#118123

How does one implement a wide index (a composite index of three or more columns)? Also how does one make the wide index clustered?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

I believe you need to read topic CREATE INDEX in BOL.

_____________
Code for TallyGenerator

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 2

Hey Sergiv,

I looked at that option previous to posting my question and the CREATE INDEX in BOL has no reference to a Wide Index unless it is called something else.

If you know how to do this please post.

Thanks,

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

It's the same as any other index, just with more columns. However, as Sergiy suggested, you might want to read up a little and better understand what you're going to be doing before doing it.

And make sure you need one. The larger the index, the slower the scanning or seeking through it.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

CREATE [unique] CLUSTERED INDEX IX_Whatever ON dbo.TableName (Col, Col2, Col3, Coln)

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 5

Thanks all I think I get it now i.e. create clustered index idx on (col1,col2,col3 etc...)

noeld SSC Guru Points: 96590 More actions · Answer 6

just keep in mind that the "wide index" can not be wider than 900 bytes

* Noel

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

Based on the fact that the default is for the Primary Key to get the clustered index and the fact that you're trying to create a different clustered index... I've gotta ask... does your table have a Primary Key?

--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)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

Related question--if a table needs a multi-column index as a primary key (to ensure uniqueness) should (some of) the same columns need a different index to support queries on those column values? In other words, can the SQL engine gain any advantage from the primary key index when only some of the primary key columns are involved.

(As I write this, I realize I should just experiment and show some query plans, but maybe someone else has been through this already.)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 9

Also, in addition to Jeff and Jim, the index need not be clustered.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 10

Depends which part of the primary key you are querying against. If you have col1, col2, col3, col4 in your PK, and your where clause references Col1 and Col2, the PK will be used, but if they only use Col2, 3, or 4 it will not. So yes you might need more indexes.

Worst case was one place I worked where the PK was 12 columns in a parent table, FK'ed into a child table that had the same 2 columns + 2 more to make it unique...... (and yes I tried to get the political will to change it, but didn't happen.)

On those tables I had multiple indexes on individual columns in the PK. Actually was even worse than that as it had one index that was the first 3 columns of the PK, that index was used in index hints all over the place so could not be removed easily (mostly it was inside old front end code that none wanted to touch).