Indexing issue
-
This is prompted by the article in todays blurb.
I have a fact table, used solely for reporting, with approx 25 cols, 4 index files covering 8 of the cols. There is no primary key and no clustered index.
A primary key of existing cols would require a complex key of 4-5 cols (I have always avoided complex keys).
Should I
A. Create an identity field to have a primary key (purely for the sake of having a PK as it would not participate in any reports)
B. Create the complex primary key based on 4-5 cols (this makes my skin crawl).
C. Leave the bloody thing alone
I want to take advantage of any performace gain I can get. I have 5.4mil rows in 2 months processing.
-
A. no. If there is no point to do it, don't
B. If creating a PK of 4 - 5 columns does that to you why not create a CLUSTERED UNIQUE INDEX? These do the same and are helpful to queries.
C. Not if you want to avoid table scans
Good Hunting!AJ Ahrens
webmaster@kritter.net -
AJ
What is the difference between a 4 column primary key (what I call a complex PK) and a CLUSTERED UNIQUE INDEX of 4 columns?
-
pretty much nothing. A composite (or covered/covering) PRIMARY KEY IS a UNIQUE INDEX. The key is the CLUSTER which will re-arrange your data physically.
PLEASE NOTE: Don't forget to include FILL FACTOR. IF you do this via QA the index will fill 100% of the page, EM will automatically set FILL to 80%. This will allow for less fragmentation and will need to be re-indexed less often...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Handling of NULLs. Primary Key can only have one particular combination with a NULL (consider the NULL a value like you would "1" or "Me"). Unique index can have multiple occurences of a combination involving a NULL. It just counts them as duplicates.
K. Brian Kelley
@kbriankelley -
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply