Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unique Constraints involving two columns. Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 7:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:26 PM
Points: 24, Visits: 183
I have a Table called EmployeeDepartment,it has EmployeeDepartmentId as Primary Key as well as Identity,EmployeeId which is a foreign key from Employee Table and DepartmentId which is also a foreign key from Department Table.
I have created a Unique Constraint on EmployeeId and DepartmentId.As I want to keep a check that only one Employee will be assigned to one Department.

1)Now when I run this query,
SELECT EmployeeDepartmentId ,EmployeeId ,DepartmentId FROM EmployeeDepartment WHERE DepartmentId =12
the Execution Plan says it is an Index Scan.

2)Now when I run this second query,
SELECT EmployeeDepartmentId ,EmployeeId ,DepartmentId FROM EmployeeDepartment WHERE EmployeeId =100
the Execution Plan says it is an Index Seek-Non-Clustered.

Any idea why the first query does and Index Scan and the second does Index Seek-Non-Clustered.

How can the first query make use of the Index or have I done something wrong when specifying the Unique Constraint?

Thanks for all the help.










Post #1434724
Posted Sunday, March 24, 2013 10:33 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:01 PM
Points: 445, Visits: 1,165
Because DepartmentID is the second column in the index (unique indexes are created automatically to enforce unique constraints), the only option available to the query processer is to scan the index. Think of it this way, picture a list of employees ordered on Last Name, First Name. Now look for every employee whose first name is 'Jerry'. How would you find them? By looking through the entire list.

Eddie Wuerch
MCM: SQL
Post #1434745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse