Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.zaYou can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>