Most systems have static data lookup tables. These are tables that are not inserted into from the GUI or any programmatic process and usually contain a master list of ‘kinds’ or ‘types’.
These ‘types’ mean something to the business but because they either never change, change infrequently or require logic to be written against them there is no GUI to edit them. Quite often records in these tables get displayed in dropdown lists we see onscreen.
Two simple examples…
… so should we use auto-increment identity IDs in these tables or normal non identity IDs? Others may disagree but I recommend NOT using identity columns with these tables.
Identity columns are not needed
Identity columns are auto-increment columns which are most useful when we are inserting new records programmatically and need automatically generated IDs which haven’t been used previously.
For static lookup tables we have all the items ahead of time so can assign arbitrary IDs to them. We don’t need auto-increment IDs as the lookup records are inserted as part of the development and deployment process not programmatically.
Identity columns can lead to bugs
The contents of static lookup tables are often programmed against in our code. To use the membershipType table above as an example we might have logic that shows extra functionality if a members membershipType is premium…
We’ve used an identity column for the membershipType ID column and no problems so far…. on DEV and UAT everything works. We push to PROD and since the premium membership is a new feature for this release we need to insert an entry for it into the MembershipType table.
The developer, unaware of the potential for mishap and knowing the IDs are automatically generated by the identity column scripts the insert without reference to an explicit ID.. eg…
The insert works fine, no complaints from SQL Server Management Studio..
as there was a couple of membership types previously inserted and deleted on the PROD table the premium option now gets an ID of 10. Remember identity IDs are not guaranteed to be contiguous but a lot of developers won’t know this.
Our logic expects that premium membership is ID 3 so we now have a bug which means our premium members won’t get to see the extra stuff.
I see the above kind of thing A LOT and although it’s not the fact we’re using identity but rather how we scripted the insert that caused the issue we could have avoided it in the first place by not using identity. For a static lookup table whose IDs we code logic against we don’t gain anything from using identity IDs and they actually require extra effort to use (as we have to include SET IDENTITY_INSERT).
What about static lookup tables which we don’t code logic against?
If we have a table which is not coded against, the case against identity is less strong as we don’t NEED to align IDs across environments. Even though we don’t need it aligning IDs gives us a few advantages…
We can write logic against the lookup type much easier in the future without having to rebuild our environments.
We can delete a lookup item by ID across environments.
We can select FK records by ID across environments.
If we decide that we’re going to align IDs then using identity is just extra work as we need to set IDENTITY_INSERT on/off each time we add records. If we really don’t care about ID alignment the one advantage of using identity is easier inserts as we don’t have to include the ID column.
In summary… if you don’t need identity IDs I wouldn’t use them and in particular if you need to attach any sort of meaning to IDs it’s best IMHO to stick with explicitly chosen IDs.