Identity Values

Hello,
I have the Following Problem:
I have some tables on a SQL Server database that have primary keys
without the identity property.
This was necessary for importing data from old databases...
Now I want to change some primary key columns to be an Identity.
Of crourse the identity seed should be set to a value higher than the
highest existing value in the column.
I can do that easily with the Enterprise Manager, but how can I do that
with transact sql statements?
Regards FerdinandMake the changes in the table designer of EM but don't save them yet. Then
look on the toolbar for the button that is 3rd from the left. It will show
you how EM makes the changes.
Andrew J. Kelly SQL MVP
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys without
> the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand|||Why would you destroy a perfectly good primary key by replacing it with an
identity column?
I know there are different schools of thought on this one, but I have always
found a logical primary key based on the actual values in the table to be
far more intuitive than an identity field, which is little more than an
artificial row number in my book. Having keys based on real values makes
joining to other tables far easier, even if slightly more typing and storage
is used in the process.
From a programming and maintainability perspective, I would stick with the
original primary keys.
Of course, this is just my opinion. Some folks consider an identity field
to be a requirement on every table. I avoid using them as a general rule.
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys
> without the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand