| Author |
Topic |
|
lee_h
Starting Member
36 Posts |
 Posted - 2003-03-13 : 01:26:21
|
| I have a Data Warehouse fact table, where I have created the clustered index over the Time Period column (most of the queries will be on a date range). I am fairly certain this is the most effective clustering column, however "Clustered indexes must be unique"Therefore I assume (yes, i know the saying...) that it appends the primary key (which I have set up as an identity) to the index.The question: Is it better to specify the primary key as part of the index or let SQL determine it? Or is there no difference? |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-13 : 01:33:56
|
quote:
"Clustered indexes must be unique"
Let me be brief..... That is completely incorrect. A Primary Key is a logical concept while an index is physical.DavidM"SQL-3 is an abomination.." |
 |
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-03-13 : 01:36:49
|
| A clustered index is the actual data, ordered according to the values in the index, thus SQL Server must have a way of uniquely identifying the position of each data row in the index. |
|
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-03-13 : 01:40:58
|
| Sorry, didn't mean the primary key, I meant the primary key column. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-13 : 07:30:31
|
| It's still not true, you can have clustered indexes on non-unique columns, I do it all the time. SQL Server handles the identification process internally, and it doesn't have any affect on how you structure your tables or what you use for primary key.Think of the phone book. It's clustered on last name, first name, but the primary key is phone number. It would be pretty useless if it was clustered on phone number, and equally useless if it only allowed one person to have each unique first name and last name. There would be lots of John Smith's with no phone number. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-13 : 12:19:16
|
| I Thought as table could have only 1 Clustered Index. Rob are you infering that there can be more than 1?Brett8-) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-13 : 13:22:17
|
No, there can be only one. <insert lame Highlander joke here>  I never suggested there could be more than one. I said that the clustered index DOES NOT have to be on the primary key, or that it even has to be unique. The phone book illustrates both concepts nicely. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-13 : 13:38:35
|
quote:
I Thought as table could have only 1 Clustered Index. Rob are you infering that there can be more than 1?Brett8-)
One index on two columns ...Jay White{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-03-13 : 14:39:50
|
| I'm not buying this analogy. Phone books are denormalized, and deliberately so. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-13 : 14:47:38
|
| How is it denormalized?Phone, LastName, FirstName, City, State, ZipAre you going to suggest that there should be a table per column with an identity column, and the Phonebook should be a list of id's?Brett8-) |
|
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-03-13 : 17:57:16
|
| OK robvolk, I understand what you are saying and I think you are disagreeing with my assumption; it doesn't invisibly append the primary key column, it sort's it out itself internally. My question however still stands:"Is it better to specify the primary key as part of the index or let SQL determine it? Or is there no difference?"create clustered index cli_pb_last_nameon phone_book(last_name)orcreate clustered index cli_pb_last_nameon phone_book(last_name, phone_number) |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-03-13 : 18:59:35
|
| SQL Server adds a "uniqueifier" to the clustered index under the covers if it is not defined as unique. This is a 4 byte value used as a secondary sort key and is appended at all levels of the index only to those values that are not unique.As previously stated, primary key is a logical concept, and index is a physical structure thus your question doesn't make a lot of sense. Primary keys are implemented physically by sql server as indexes (well constraints to be more precise but the physical on-disk structure is that of an index be it clustered or non clustered). If your question is whether you should create a covering index with your clustered index to incorporate the primary key then no it doesn't make sense to do so. In fact the non clustered index supporting the primary key constraint will contain both the primary key and clustered index keys (including "uniqueifier")at its leaf level. Not sure if this is what you are after ?HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-13 : 19:19:02
|
| Nice work Jasper...Lee,The information contained in BOL for the CREATE INDEX statement contains hidden wonders.. Specifical, look at the DROP_EXISITING argument.DavidM"SQL-3 is an abomination.." |
|
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-03-13 : 19:37:45
|
| Cheers guys. I've got a good understanding of SQL and Database Design, but all of my Admin type skills (such as creating/maintaining indexes) is from trial and error, so I'm trying to pick up as much information as I can about what is going on behind the scenes. The question was prompted by a white paper I read, and it contained the original quote that I cited.Basically the answer is, don't create your clustered index as a covering index containing the column you have created the primary key constraint on, let sql sort out the uniqueness. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-03-14 : 04:33:10
|
quote:
How is it denormalized?
...C D Jones 123 New Road, New Invention, SY7 0BS 015884 55555...A B Smith 123 New Road, New Invention, SY7 0BS 015884 55555... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-14 : 07:19:04
|
| That's a nice phone book you have there, they don't dupe phone numbers in the U.S. (usually...I've never seen one). Besides, they're still two different rows that share all but 2 attributes. Each row is still unique. How would you "normalize" it anyway? You'd still end up with a CD Jones and AB Smith sharing at least one attribute. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-03-14 : 08:36:31
|
Hmmm, seems they don't in the UK either -- only lists the person who rents the line.Ah well...I suppose I was thinking:CREATE TABLE Telephones ( number phonenumber PRIMARY KEY, address phoneaddress)CREATE TABLE TelephoneNames ( number phonenumber NOT NULL REFERENCES Telephones(number), name phonename NOT NULL, PRIMARY KEY (number, name)) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-14 : 08:41:56
|
quote:
The question was prompted by a white paper I read, and it contained the original quote that I cited.
Hi Lee,Do you have a URL to pass on for that white paper you mentioned?Sam |
|
|
|
|
Clustered Index Question