If you've ever tried testing SQL stored procedures and functions, you've undoubtedly run into situations where you needed to clear tables of data to load fixtures into related tables. In MySQL, this would be accomplished by using TRUNCATE on the child tables, going up the hierarchy; in MS SQL, you cannot use TRUNCATE on tables that are referenced by foreign keys.

To remedy the situation, consider the scenario where you have two tables – Child and Parent. The Child table contains a foreign key referencing the primary key in Parent. In this case, you can use the following code snippet to truncate both tables:

TRUNCATE TABLE Child;
DELETE FROM Parent;
DBCC CHECKIDENT(Parent, RESEED, 0);

This will clear the table data and reset the primary key increment variable to 0. Any new data in the tables will now start enumeration on autoincrement numeric primary keys with 1.