Wish you a very Happy New Year 2013. Today is a special day for me to celebrate the New Year because today my blog post turned 1 year old. I can’t believe that I have been blogging for a year now. This blog was just created with an intension to share what I learned during my daily work. I’m happy that I initiated to contribute to the SQL Server community.
I posted 24 blog posts in 2012 that is one post in every 2 weeks. Many have their New Year resolutions and I’m not the exception in this case. One of my New Year resolutions is to write at least one blog post per week.
Both Unique constraint and Unique Index enforces uniqueness of the column. When we define an Unique constraint, SQL server creates an Unique non clustered index on the column where Unique constraint is defined.
Even though both of them can be used for same purpose but consider below points before you decide which one to use.
1 - From SQL server 2008 onwards we have Filtered index , Include columns which can’t be define on index which is created while defining Unique constraint but we can define it while defining Unique Index. We can have better control over the Unique index.
2 - Derived from point 1, sometime we need our column should be unique but it should allow multiple NULL values in that case by implementing Unique filtered Index we can achieve it. Also we can avoid Key lookup by adding included columns in an Unique Index but not in Unique constraint.
3 - Unique constraint always creates non clustered index. Unique Index can be defined either Clustered or non Clustered.
4 - You can’t drop only the index associated with the Unique constraint.
Leave your comment, If you have any points which we should consider before we decide which one to use between Unique Constraint and Unique Index.
If you liked this post, do like on Facebook at https://www.facebook.com/s4sql
Wishing You All a Very Happy and prosperous New Year 2012.
Today I am pleased to publish my website sqlcommitted.com.
In my career I realize the importance of sharing knowledge and Experience. According to me a good book will show you the path towards your goal and gives you detail information but someone’s experience will guide you how to achieve your goal. Whenever I face any technical problem, I always look into blogs first rather than to book to solve it. In my blog I’ll try to put all my experience and knowledge in SQL server which will help SQL server professionals in their job.
Experience comes if you start experiencing it.
I need all of your support to make this site useful and better.
Looking forward to your feedbacks.