August 8, 2007 at 12:49 am
Hello everyone, me using SQLServer 2000,i want to ask a question it is a simple but for me its creates confusin, kindly tell me in Table , if i have fileds and my PK is combination of other fields then the TableID is identity or not.
For Example Table is EmployeesProfile
EmployeeProfileID
DepartmentID
ShiftID
Name
Now this table had ID but i made PK with the combination of DepartmentID and Shift ID. Now PK beocmes (EmployeeID,DepartmentID,ShiftID)in this can i have EmployeeID is identity and other twos are not , IS this a good practice or not if yes then y and if not then y?Kindly explain me.
Thanx in Advance.
August 8, 2007 at 5:06 am
yes you can have employeeid as identity. being an identity column does not automatically mean that it becomes a primary key.
people usually use an identity column as the primary key, because it will be unique if left to its own vices.
in your case you don't gain anything by having a composite key which contains an identity column. better have the empid as the PK
August 8, 2007 at 10:14 pm
Something else to consider wrt IDENTITY columns is that, because they are typically much smaller than the equivalent primary key they are a good candidate for being the clustered index on a table.
The reason for this is any non-clustered index has the clustered index at it leaf level. Hence, have a small clusted index means that the non-clustered indexes are also smaller which will help the performance of queries using these indexes.
Similar argument goes for using the identity columns in joins - things are just quicker which, hopefully, means that you end up with fewer performance problems.
This does not need to impact the choice of columns that you define as the primary key - just the choice of what column should be in the clustered index for the table.
August 9, 2007 at 8:00 am
Good advice above. There are two thoughts from most people: Use identity because it's easy, small, etc. as the PK, or use the natural key, meaning the fields that uniquely identify each row.
I'd lean towards just identity because it's simple and easy and works well for most of us. Plus every once in awhile your business rule changes and the data you thought was unique isn't any longer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply