Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andrwo
Starting Member
1 Post |
 Posted - 2010-04-19 : 23:45:17
|
| Hi all,Got some questions that have been puzzling me, hope some gurus out there can help answer. I got a table in this form:CREATE TABLE T (KEY1 int not null,KEY2 int not null,KEY3 int not null,iDATA int not null)I need to search on unique tuple (KEY1,KEY2,KEY3) only, and retrieve iDATA, and I would like to save space if possible. There will be no other indexes, and no foreign key will ever point to this table, and no new columns will ever be added.What is the *storage* difference of the entire table (index+data) if I were to declare the primary key as (KEY1,KEY2,KEY3), or (KEY1,KEY2,KEY3,iDATA)? [ I know there are also differences in uniqueness constraint, which I'll ignore ] Will the storage requirements be the same for both situations, or different? If the Table=Primary-Key, does it end up storing a duplicate of the data at the leaf node?Thanks for your help and advice.Regards,Andrew |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-20 : 03:24:45
|
| Table does not = primary key. A clustered index, when created on a table, contains the entire table and the primary key is, by default, a clustered index.If the combination of Key1, Key2, Key3 needs to be unique, create your primary key on those three fields only. If you create the primary key on all three plus the data, you're allowing duplicate values for the 3 keys to be entered.The storage differences are just the higher levels of the index where, in the first case the non-leaf levels contain just the 3 ints and in the second, 4 ints. Not large, but it is there. This article may help.http://www.sqlservercentral.com/articles/Indexing/68563/ (part 2 of a 3-part series)--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
Table = Primary Key