[SQL Server] Unique Constraints involving two columns.

---

Unique Constraints involving two columns. Posted: 24 Mar 2013 12:26 PM PDT 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 =12the Execution Plan says it is an Index Scan.2)Now when I run this second query,SELECT EmployeeDepartmentId ,EmployeeId ,DepartmentId FROM EmployeeDepartment WHERE EmployeeId =100the 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.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610