SQL Server– Unique Constraint VS Unique Index
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
Object Name Starts With sp_
Yesterday night I was playinng around with sp_.
When you create a table , view or procedure in master database prefix with sp_ , it is accessible from other user databases without using fully qulify name.
Lets try …..
Use master
Go
Create Table sp_Table1 (C1 Int, C2 char(1))
Go
Insert Into sp_Table1 Values ( 1,‘A’)
Insert Into sp_Table1 Values ( 2,‘B’)
Go
– Go to one of your user database
Use SCOM
Go
Select * from sp_Table1
It will return 2 records.
Lets try something with the procedure name.
When we create a procedure starts with sp_ in user database and run the procedure, SQL Server look for a stored procedure begining with sp_ in following order
1- Master Database
2- It will use fully qualified name to locate the sp
3- If fully qualify name is not given then it will check in current database with dbo as owner
SP name prefix with “sp_” in the userdb, the master database is always checked first, this slow down the performance.
Now a question for you,
Try to answer befor you try the below code
use master
Go
Create Procedure sp_ShowResult
As
Select ‘This is executed from Master Database’
go
Use test
go
Create Procedure sp_ShowResult
AS
Select ‘This is executed from User database’
go
Exec sp_ShowResult
What will be the output?
About
Sandip Pani is a MSBI/SQL Developer. He holds a Masters of Computer Application degree from IGIT, Sarang, Odisha, India and a number of certifications such as MCTS, MCITP. Befor Joining QSIHealthcare he was working for IBM India Pvt. Ltd and DELL Services. He has passion to contribute to MS-SQL Server community so he is committed to SQL.
You can contact me at s4sql@sqlcommitted.com
SQLcommitted-
Archives
- March 2013 (1)
- January 2013 (3)
- December 2012 (2)
- November 2012 (1)
- August 2012 (4)
- July 2012 (2)
- June 2012 (2)
- May 2012 (1)
- April 2012 (5)
- March 2012 (1)
- February 2012 (1)
- January 2012 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
