In general, I decided to copy the tables from one database to another database on the server.

I used the standard export Export/Import utility.

To my surprise, no keys, no restrictions, no indexes were copied ...

But since there were source tables, I generated the scripts and put everything back into place.

I later discovered that IDENTITY not copied. At first I tried to add it through the interface, to which I received a TIMEOUT error. It would seem that it may be easier to hang IDENTITY on the PRIMARY KEY , but no ...

Googling, I found that through the Alter Table it can not be added.

The choice was:

1) Add a new column with IDENTITY and forget about the old one. This option did not fit me, since the column was a PK and there were links to it => by generating a new IDENTITY one could lose something.

2) Create a new table where IDENTITY already exists and using IDENTITY INSERT to merge data from the old table. What I took advantage of, losing extra time.

Tell me, what are the reasons for such restrictions?

Or is it still possible to add IDENTITYT somehow through ALTER TABLE ?

  • It is possible to create a script table with all objects. You just need to remember to specify them. A list of what needs to be included is available by clicking the "Advanced" button - Nick Proskuryakov

1 answer 1

It is impossible to add IDENTITY through ALTER TABLE in any way - this is a common place, Microsoft has been made such requests more than once, but, unfortunately, the opportunity has not been implemented. What is the reason? It is not known for certain, perhaps the development team will be able to give an answer.

It is quite likely that the opportunity has not yet been added due to the fact that IDENTITY is now considered an obsolete way of obtaining new values ​​- starting with SQL Server 2012, sequences (SEQUENCE) are implemented - it is somewhat easier to work with them and, among other things, implement the script you need - SEQUENCE is not stitched into the table, DEFAULT CONSTRAINT is added to the table with the data received from SEQUENCE, with this command:

 CREATE SEQUENCE mySequence AS int START WITH 1 INCREMENT BY 1; GO ALTER TABLE myTable ADD CONSTRAINT IdSequence DEFAULT (NEXT VALUE FOR mySequence) FOR Id;
  • "IDENTITY is now considered an obsolete way" oh, oh, well, well, really ... - i-one
  • one
    Well, technically it does not, otherwise it would have been declared deprecated, which has not happened yet. So, I agree, this phrase is not entirely correct. It would be more correct to say that SEQUENCE is another way to obtain a sequence, which is better suited in some cases. - minamoto