Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
What is so special about an Identity column?
I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL.
So what is so special about an Identity column that this can't be easily added in retrospect?
Why is this not just a single int/bigint flag somewhere that is being incremented every time you insert a new row?
Is there anything physically stored per row that this is a difficult process?
If I understand your question correctly, you are not asking how to create a new column with fresh IDENTITY values. You are asking why we cannot issue ALTER TABLE tbl ALTER COLUMN col int IDENTITY on an existing column.
As you say, all it is is effectively a DEFAULT constraint with a counter.
The answer I think is simply that it was never implemented, clearly not enough people asked for it. So we have to use workarounds.
I don't think there is any major difficulty in adding this feature, other than the fact it would have to be spec-ed, implemented and tested. So if you want this feature, do vote for the existing feedback issue on Azure.
So what is so special about an Identity column that this can't be easily added in retrospect?
An identity does not insert values on a table by itself, it depends on an INSERT command and the old rows on your table would require an UPDATE. If you try to UPDATE an identity column, you get an error like this:
Msg 8102, Level 16, State 1, Line 8
Cannot update identity column 'ColumnName'.
Every feature has a purpose and limitations. The purpose of the identity is not for updating values.
Why is this not just a single int/bigint flag somewhere that is being incremented every time you insert a new row?
In fact it is something like that. The CREATE TABLE (Transact-SQL) IDENTITY (Property) says:
SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart.
So your IDENTITY column has something like a flag that asks SQL Server for one of those values when inserting a new row.
From the Remarks section of the IDENTITY doc you can see the feature has some restrictions and the reason is:
These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations.
You can't add an identity to an existing column but you can start using a sequence.
Maybe this can give you an alternative.
Keeping in mind the first and last paragraph of Ronaldo's answer, another thing to note is there's just no mechanism in place to add an IDENTITY to a tabld after the fact. The IDENTITY column has a couple parameters around it such as the seed and increment value which can even both be negative numbers. An IDENTITY column doesn't need to be part of the primary key nor does it depend on it.
If one were able to add an IDENTITY column to a table after the fact, then there would be no way to correlate which rows the IDENTITY values should belong to. There would need to be additional mechanisms programmed in SQL Server to provide a way to specify how the IDENTITY can generate the appropriate value for each row, perhaps with an ORDER BY clause.
But even that doesn't solve every problem, because if the user specifies a non-unique set of fields to ORDER BY (but wants the IDENTITY to be unique) then what should the IDENTITY do?...it could pick one at random like the ROW_NUMBER() window function does but then it would make the IDENTITY function non-deterministic which isn't a great characteristic for an IDENTITY column. Or it could just generate dupe IDENTITY values and not guarantee uniqueness, which is fine, but doesn't solve the problem of the user.
Long story short, if Microsoft wanted to provide a way to add an IDENTITY column to a table after it's been populated with rows, it is possible, but it would require a little bit of thought on how it should be done. It also is a feature that isn't very often needed so the current design that restricts the ability to do so is a generally acceptable one.
You can create a new empty table with an IDENTITY column and use ALTER TABLE SWITCH to switch the rows to the new table definition as a metadata only operation, so this can be used to effectively toggle the identity property in a cumbersome way.
The IDENTITY property is just a metadata property in some system tables and this could easily be updatable if implemented. In the meantime the fact that ALTER TABLE ... SWITCH does not require this property to be the same in source and target can be used to effectively achieve this.
You still have to drop and rename, but it helps because it just changes the table metadata. There is no movement of rows, and in the end you end up with the exact same data and index pages associated with a table now containing an IDENTITY column.
See Why is removing the Identity property on a column not supported for an example.
