Thread: Resetting Identity

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

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

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

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.