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.