June 9, 2011 at 3:08 pm
i did some quick research, but i have to log off my computer.
Can someone help me find some information that I can read later or their own knowledge on this topic.
If i have a unique non clustered index and when I do the inserts into the table the column that is unique is random so not in sequence, what happens? does sql server have to search through the index for each insert to make sure it it unique first? What are the performance ramifications of inserting into this table with this unique nonclustered index.
thanks
June 9, 2011 at 3:28 pm
Yes there are comparison checks performed by SQL Server to ensure that the value is unique if you are inserting a value into a unique index. Performance ramifications will vary depending on environment, table size, amount of inserts, activity on the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 6:34 pm
The index itself is still ordered (in a balanced tree structure) even if it is not the clustered index.
The special thing about a clustered index is that it is also setting the order of the rows in the table. This is a bit simplified, but that's the fundamental difference.
June 9, 2011 at 8:48 pm
Thanks for both responses so far.
I still haven't had time to read any BOL on the subject but I will tomorrow.
I was looking for more of a performance information when using Unique Non-Clustered vs Non-Unique Non-Clustered. I guess it doesn't matter if it's unique or not because when the row is inserted it still has to read the non clustered index to update it based on the new record.
The table of issue has about 700MM rows of data. It doesn't have a clustered index(i suggested one) but the other dba insists that it takes to do inserts. I'm 'new' so i'm like well maybe use an identity key for the clustered index (since they are doing an upgrade and now would be a good time to fix that issue). But the PK on that table is unique non clustered. I'm trying to think of a better way to enforce uniqueness on a table with that many rows. I have to find out tomorrow more on the business side, like does the process ever fail because the uniqueness was violated, if not then maybe not even have the index (i'm not sure if its used for any read operations), and enforce uniqueness some other way....
any advice?
June 9, 2011 at 9:49 pm
tar (6/9/2011)
Thanks for both responses so far.I still haven't had time to read any BOL on the subject but I will tomorrow.
I was looking for more of a performance information when using Unique Non-Clustered vs Non-Unique Non-Clustered. I guess it doesn't matter if it's unique or not because when the row is inserted it still has to read the non clustered index to update it based on the new record.
The table of issue has about 700MM rows of data. It doesn't have a clustered index(i suggested one) but the other dba insists that it takes to do inserts. I'm 'new' so i'm like well maybe use an identity key for the clustered index (since they are doing an upgrade and now would be a good time to fix that issue). But the PK on that table is unique non clustered. I'm trying to think of a better way to enforce uniqueness on a table with that many rows. I have to find out tomorrow more on the business side, like does the process ever fail because the uniqueness was violated, if not then maybe not even have the index (i'm not sure if its used for any read operations), and enforce uniqueness some other way....
any advice?
If you have a clustered index where the clustering key is unique, narrow, static, and ever increasing (eg, an identity column or some naturally static increasing key) then it can help your inserts quite a lot by reducing fragmentation on insert. That is to say, since new rows are always being put at the end under such a scenario you won't have page splits. It used to be the case that this created a "bad" hot spot, where multiple sessions could fight over locks, but with row level locking this is not an issue.
The question of whether or not you should have a unique or non unique index (be it clustered or non clustered) should not involve performance, only integrity. If you have something that is supposed to be unique, make sure it is by making a unique constraint.
It's not a big deal whether or not the PK is clustered. It often is, but that's often coincidental with the use of surrogate keys.
June 10, 2011 at 8:17 am
If you have a clustered index where the clustering key is unique, narrow, static, and ever increasing (eg, an identity column or some naturally static increasing key) then it can help your inserts quite a lot by reducing fragmentation on insert. That is to say, since new rows are always being put at the end under such a scenario you won't have page splits. It used to be the case that this created a "bad" hot spot, where multiple sessions could fight over locks, but with row level locking this is not an issue.
--Yes that is good to know. So my question is beyond the clustered index.
The question of whether or not you should have a unique or non unique index (be it clustered or non clustered) should not involve performance, only integrity. If you have something that is supposed to be unique, make sure it is by making a unique constraint.
It's not a big deal whether or not the PK is clustered. It often is, but that's often coincidental with the use of surrogate keys.
--Well performance is important because I can 'enforce' uniqueness by using logic in code rather than a unique constraint, especially if i'm dealing with a 12 hour load process that never fails due the unique contraint being broken. This is where I'm looking for advice. I was just wondering the performance on a non clustered index -- does making the index unique end up causing the inserts slower than making it non unique. Maybe I should put this question in the design forum or dw forum?
June 10, 2011 at 8:56 am
I don't think it will make a noticeable difference. Either way the engine will run down the index to the required leaf to add data. If it's unique there isn't a lot to do to check at this point. It'll either already be at the right leaf, or at worst need to move to the next leaf (possibly by going up a level of the tree and back down to the adjacent leaf? Not sure whether leaf pages are themselves a linked list). In absolute terms yes, I suppose a unique index does require at least one more operation from the engine.
If I was designing it I would accept the (afaik) minor performance hit and enforce uniqueness with the constraint.
June 10, 2011 at 9:29 am
thanks for the feedback on this.
--now let me throw in the curve ball, as i said originally the table doesn't even have a clustered index, just a uninque non clustered. i was trying to get some design tips together to convince them to add the cluster and maybe drop the non cluster. is there any documentation that says non clusters work better with clusters or is it all based on the fact that a cluster index makes the table more efficient in general.
The argument was that the column was not sequential so hence not using it as a cluster for the table. i suggested use an identity key.
June 10, 2011 at 9:43 am
In my opinion adding a unique clustered index on an identity column is quite reasonable. (But when doing so you just have to remember that you're doing it to "take control" of some disk optimization by taking control of the storage order, and it doesn't have anything to do with maintaining integrity. I have to include this disclaimer because I'm pretty strict with my devs on keeping things "properly relational").
Large heaps generally aren't good things to keep around, hence so much advice around the place to give your tables unique clustered indexes. Tangentially, if you try to make a non-unique clustered index SQL will make it unique behind the scenes by adding some additional data... it has to be unique since it's setting the sort order of the rows on disk.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply