March 24, 2013 at 7:26 pm
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.
March 24, 2013 at 10:33 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply