IDENTITY Problem 1

[Warnie]

Hi,

I have a table XYZ which has a column as Item_Id. I had to create this with an identity seed but missed out. Now the table has been created in production. I want to alter this column to have a IDENTITY.
I tried the following syntax

Alter Table XYZ
Alter column Item_Id int Identity(1,1)
Go

But I am having a problem with the syntax. Can anybody help me with solving this small problem?

Thanks,
Warnie

 

[nigelrivett]

You'll have to replace the column. You can't add an identity property to a column once it's created.

Try it in enterprise manager and look at the script it generates.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Warnie]

Nigel,

If I drop that column, I will be losing data. I reckon that I will need to drop the column and then add it. But the column is the first of the many columns in the table. So if I recreate the column, I will end up with the column moved to the last. Am I right in my understanding?

Warnie

 

[r937]

the only time it matters that the new column is "last" in the row is if you code SELECT * and refer to columns positionally instead of by name

you don't do that, do you?



rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

You can change a column to an identity column in Enterprise manager because what Enterprise Manager does is create a new table, copy the records to it, and then drop the original table and rename the new one. Clearly this is a BAD idea on a production system unless it is a very small table. (If you have lots of records, it will probably time out anyway.)

So what to do? First add an identity column. The use the set_identitytinsert On command to allow you to insert the number values from the existing column (if you want to use them). Then use update to update the new column with the old values. Then drop the old column (dropping any relationships first) and rename the new column. Then give the new column the correct relationships and indexes needed. Then set_identityinsert Off and you are good to go.

 

[nigelrivett]

>> If I drop that column, I will be losing data. I reckon that I will need to drop the column and then add it. But the column is the first of the many columns in the table. So if I recreate the column, I will end up with the column moved to the last. Am I right in my understanding?

Look at the code generated by enterprise manager. It creates a new table so the columns will be in the same order then copies all the data and renames tables.
But if you are relying on column order then you are heading for problems.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.