Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


unique versus non-unique index in terms of performance


unique versus non-unique index in terms of performance

Author
Message
Mani-989491
Mani-989491
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 101
In terms of performance which one should be created between unique and non-unique index. Let us say I want to make a Non clustered index on columns which is idenetity column. As I alredy know this columns value is unique so if I create a non-unique index will that have any significant performance over the unique index.

My concern is as unique index will check for any duplicate existing row for every new entry and I have some 70000 records added to the table every day so will my upload into the table will take more time after I create this unique index. Also which index will have better query performance.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17580 Visits: 32260
It depends on the queries being run. The optimizer will take into account when an index is unique and it can improve performance, but it really does depend on the query. It might not affect things at all.

It will slow down inserts slightly, but probably not enough to notice. It doesn't have to check everything single record against every other record. It's going to use the index itself for the check which will be quite fast.

In general, if you've determined that an index will help and the data being indexed is going to be unique, I'd say go ahead and make it a unique index. It won't cost much and you will receive benefits.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Mani-989491
Mani-989491
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 101
Thanks Grant
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
I'd go the complete opposite. If the data requiresa unique index (for data quality), then go ahead and create the index. I wouldn't do a unique index just because it might go faster and not consider wheter or not it could bug the applications using it.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17580 Visits: 32260
Oh no, if it's going to bug the apps, then clearly the data is not guaranteed unique. I'm just saying if the data is going to be unique, there's no reason to not make the index unique and several reasons to make it that way.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1623 Visits: 409
I Agree with Ninja's decision. If the data requires to have Unique Index then we should have Unique Index. In this world anything can happen with Data Quality. So always impose contraints on this to avoid any future Data Quality Issues.

Thanks -- Vijaya Kadiyala
http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search