To use or not to use identity on a lookup table

Question

In SQL Server I have the following lookup table that holds degree levels:

create table dbo.DegreeLevel 
( 
    Id int identity not null
       constraint PK_DegreeLevel_Id primary key clustered (Id),
    Name nvarchar (80) not null
       constraint UQ_DegreeLevel_Name unique (Name)
)

Should I use identity on the ID?

When should I use identity or a simple int in a lookup table?

Answer 1

After dealing with multiple environments where we move changes from one environment to the next, I'd say not to use identity columns on look up tables.

Here's why: if you need to reference an ID as a "magic #", you need consistency. Ideally, you wouldn't ever reference a magic #, but in reality, that is not what is commonly done. And it's a pain to correct when the IDs are out of sync. And it's really not much more effort to insert the table's data with an ID.