Resetting Identity

[JaedenRuiner]

How do I force the reset of the Identity field. For instance, I'm testing the application now, and occasionally there are errors, I purge the database to redo a full test (from the beginning) and even though there are no records in my table, it's starting the identity column at 45. How do I tell the database to restart the identity from my "seed" value?

Thanks

 

[JohnH]

check these two options: MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED

 

[JaedenRuiner]

awesome thanks!

<edit>

Well, sort of. Is there anything on the VB side that would keep the truncate from fully resetting the Identity? I did set the Typed Dataset that manages this table to have that field as an "Identity" field in the DataSource Designer.
I've tried running the truncate normally, but It has problems with Foreign Key Constraints even when the PK doesn't point to any FK records in the child table. When the table is empty it returns fine, but it hasn't reset the identity, which means either the Foreign Key is getting in the way, or VB is keeping the Identiy "alive" with the typed dataset in memory and I need to close down or something.

Thanks

 

[JaedenRuiner]

Well,

How do Foreign Key constraints affect Truncate? Because here's the thing, the Delete Rule is set to "Set Null", the Update is set to "Cascade" i've deleted from the parent table, and from the child table so there are no rows anywhere, but truncate still says it can't do it due to a foreign key constraint.
What's that all about, eh?

<edit>
Never mind, looked into the DBCC commands and that worked beautifully.


Thanks

 

[JohnH]

This is what the Transact-SQL Reference documentation says:

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause
<snip>blah blah logging and speed<snip>
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause.

If you want to retain the identity counter, use DELETE instead.

So it's clear on foreign keys, while I don't agree when it says "functionally identical". For this the DBCC should be of help.