July 2, 2009 at 11:34 am
I have a non-normalized "Lines" table with a clustered index but no primary key. The clustered index is on 3 columns that are highly needed in queries, but are not guaranteed unique. The table has a column called LinesId that is unique by definition (bigint, incrementing by 1), but the field is never used in any lookup (nor as a foreign key). (This HUGE table is what it is.)
Is there anything to be gained in this scenario by creating a primary key (whether on LinesID alone or by adding it to the clustered index or...)?
July 2, 2009 at 11:54 am
There are those out there that will tell you that every table should have a PK. There will even be those who may even advocate that it should be the clustered index. I'm not always there. Yes a Primary Key should probably be declared. In your case, the LineID column sounds like a good candidate for a nonclustered primary key. And personally, I wouldn't add it to your clustered index if it doesn't help it.
July 2, 2009 at 12:09 pm
I really appreciate your insight and help.
I'm guess I'm wondering what I get in exchange for declaring a primary key (in my peculiar setting).
On the con side,
(1) Slowdown on inserts: every insert will have to maintain this additional index.
(2) Added space: Adding the index alone adds almost a gig to the size of the indexes I have now.
On the "pro" side, what is there? Does a declared primary key fulfill some need in SQL on a non-normalized table like this?
July 2, 2009 at 12:14 pm
Yes, it will take additional space. This could be a vaild concern. You have to be the judge on this.
Yes, there is the overhead to maintain the index. Again, valid, but unless you have many indexes on the table, the impact on the inserts and deletes will most likely be minimal.
July 2, 2009 at 12:42 pm
I'm totally with Lynn here :w00t:
One other thought of the day:
Whay are you defining the clustering index on a 3-column ix ?
- Are those columns always used (all 3 ) ?
or the first col
or the first col and the second col
- do they support set level prefetches ? (between ...)
- could the ix columns by themselves be couvering your query needs ?
If the first 2 Q are Yes: keep your current clustering definition.
Else, you may need to examine another Clustering algorithm to support your data usage !
Having a to wide clustering definition, will have its impact on non-clix !
Maybe even test to separate the columns (because they are non unique anyway ) only retaining one for clix and have the others as NCI.
Having a unique row identifier (PK/UK) will have its advantages in the long run, unless you actually are 112,5% sure you will never need to manipulate the single row if duplicate keys exist.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 2, 2009 at 2:11 pm
dcorbitt (7/2/2009)
I have a non-normalized "Lines" table with a clustered index but no primary key. The clustered index is on 3 columns that are highly needed in queries, but are not guaranteed unique. The table has a column called LinesId that is unique by definition (bigint, incrementing by 1), but the field is never used in any lookup (nor as a foreign key). (This HUGE table is what it is.)
What is the point of the duplicate data in the three clustered columns if the other column is never used? If you eliminate the extra weight of the redundant data you will have simpler more efficient queries (presumably your queries must either be returning duplicate rows or must use DISTINCT). You would also have a smaller table and maybe you wouldn't want that extra column at all.
"Huge" tables are of course exactly the ones where normalization is the most help.
July 2, 2009 at 2:36 pm
Thank you all for your help. I can't redesign the table structures, at least at this time, but I can tweak the indexes. You've helped me know what to weigh on the purpose of a primary key as it applies to this table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply