resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

Resetting / deleting all the users in aspnet_* tables

Hi,

How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?

Thanks

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

|||

Assuming you don't have any other tables in the database that have foreign key constraints to the aspnet_* tables, you could simply rerun the T-SQL scripts that create those tables (since those scripts first drop the tables, if they exist). You can find the T-SQL files in %WINDOWS%\Microsoft.NET\Framework\v2.0.50727 or you can execute them using the aspnet_regsql.exe command line tool.

UPDATE: To provide more clarity... the T-SQL scripts in that folder are named like Install*.sql. InstallMembership.sql, for example, contains the scripts for the membership-related tables; InstallRoles.sql for the roles. InstallCommon.sql has them all.

|||

Sweeperq:

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

The only difficulty with this approach is since the aspnet_* tables have a number of foreign key constraints among themselves, if there is data in the tables you have to truncate (drop) the tables in the correct order, otherwise you will get foreign key constraint errors and the truncate will fail.

|||

So TRUNCATE does not trigger cascading deletes?

|||

The stored procedure aspnet_users_deleteuser takes care of the relationships and constraints - it's what the Membership.DeleteUser method itself calls. You can call it manually. Here's anarticle on deleting users from membership using the stored procedure.

But this deletes one user at a time. You want to delete all users? You can use SQL to cursor through the aspnet_users table, calling the stored procedure each time.

Otherwise, you need to truncate in the correct order which would be this: aspnet_usersinroles, aspnet_profile, aspnet_personalizationperuser, aspnet_membership, aspnet_users.

|||

Sweeperq:

So TRUNCATE does not trigger cascading deletes?

It does, if I'm not mistaken. Problem is, the foreign key constraints on the aspnet_* tables do not cascade deletes. So you have to manually delete the "child" tables first before you can delete the data from the parent table.

Reseting the Auto-Number for a table back to zero and Compact/Repair or Unload/Reload for

After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help

Try to use truncate instead of delete to remove records from your table, it will remove records and reset identity fields to starting values.

Thanks

reset sql 2000 sequential number

Hello, I have a table where I'm deleting the contents before populating the table with new data. I have an ID column that is autogenerating a sequential number. I would like to reset this number back to 1 when I delete the contents of the table. How can this be accomplished?you could TRUNCATE the table. check out Books On Line for more info.|||Hey ndinakar! Thanks for responding. That's what I came up with too. But, I've heard that truncating a table was bad news; not a best practice. All I know is that it works so I will go with it. Have a good one.|||If TRUNCATE TABLE had not worked in your scenario (and in somescenarios it will not), you can delete all of the records in the tableand then runDBCC CHECKIDENT to reset the identity value.

reset next identity value

For a set of tables many of which contain FK constraints related to identity
cols in the foreign tables:
When deleting all data (dropping FK constraints first) and then repopulating
data from scripts , how to instruct SS to reset the next identity value to
the identity seed ?TRUNCATE TABLE, or check out DBCC CHECKIDENT (TableName, RESEED)
Roy
On Mon, 27 Feb 2006 17:14:18 -0800, "John A Grandy"
<johnagrandy-at-yahoo-dot-com> wrote:

>For a set of tables many of which contain FK constraints related to identit
y
>cols in the foreign tables:
>When deleting all data (dropping FK constraints first) and then repopulatin
g
>data from scripts , how to instruct SS to reset the next identity value to
>the identity seed ?

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

sql