Removing IDENTITY from existing column

I have been testing some code that has been inserting large amounts of data
into some tables. All of the tables have a Primary Key that is also an
Identity. After a bad run, I go through and DELETE all of the entries in
each table (I would TRUNCATE, but they all have at least three other tables
referencing them and you can't TRUNCATE a linked table). Only problem is
that the IDs are getting extremely large and make it harder for me to
determine if the code is working correctly. In the TSQL that I use to DELET
E
the contents of the tables, I would like to set the IDENTITY to NO, then bac
k
to YES so that the IDs increment from 1 again instead of some very large
number. The only way that I can see is to drop the column and then add it
again. I am not sure how well this would work since I have lots of foreign
keys referencing these PKs.
Thanks in advance.
--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsDrop the IDENTITY and not the column, then put the IDENTITY back on the
column -- one way.
Owen
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||See "DBCC CHECKIDENT" in BOL.
AMB
"Chris Lieb" wrote:

> I have been testing some code that has been inserting large amounts of dat
a
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other table
s
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to DEL
ETE
> the contents of the tables, I would like to set the IDENTITY to NO, then b
ack
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of foreig
n
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Try: DBCC CHECKIDENT (tableName)
Look it ub in BOL.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Use
DBCC CHECKIDENT ('table_name',RESEED,0)
to reset the IDENTITY value.
David Portas
SQL Server MVP
--|||I tried to add IDENTITY to an existing column, and it didn't work. I was
told on this forum that it can't be done.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Drop the IDENTITY and not the column, then put the IDENTITY back on the
> column -- one way.
> Owen
> "Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
> news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>|||Strange. I do that all the time (using Enterprise Manager)...
Owen
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
>I tried to add IDENTITY to an existing column, and it didn't work. I was
>told on this forum that it can't be done.
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
>|||Strange that you use Enterprise Manager to make changes to table
structures :-)
Paul is in fact correct that you cannot do this with a single statement
in TSQL. One of the (many) problems with using Enerprise Manager is
that to achieve this it will drop your table, constraints and indexes,
recreate and then copy all your data over. Definitely not recommended
on a system that's in use!
David Portas
SQL Server MVP
--|||That's because behind the scenes EM will jump through the hoops
of creating a temp table copy of the original table, creating a
new table with identity column assigned, inserting data from the
the temp table, and then dropping the temp table. There's no
easy way of doing it through Query Analyzer other than
what I've outlined.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
an
then
large
add
>|||EM does a lot of work behind the scenes.
A SIMPLE example on a table with no foreign key references where Identity is
removed:
It creates a new table, copies the data, drops the old table and renames the
new one.
If there where foreign key references, EM has to handle this too.
In EM, you have an icon in the table design view to save the script when you
make a change and before committing it.
Try it (on a test table of course).
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...