SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Understanding Identity Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/09/2002 :  18:21:02  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Karla writes "How can I reset an Identity column and not start where it left?" I've been getting quite a few questions about identity columns lately. This article should cover everything I know about them. I'll cover creating them, populating them, resetting them and a few other goodies.

Article Link.

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 03/11/2002 :  16:59:02  Show Profile  Reply with Quote
Graz mentioned that deleting the rows in a table won't reset the identity value. This is true, however TRUNCATING the table, at least in SQL 7.0, does reset the identity values.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 03/15/2002 :  11:52:18  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Graz,

GREAT article! It's been available for a week now and I kept skipping over it thinking, "I already know how to use and reset IDENTITY columns. I don't need to read that." But this morning I decided to read it anyway and guess what? I learned two new functions: SCOPE_IDENTITY() and IDENT_CURRENT()! . And now that I've just wrapped up the migration of our production server from SQL 7 to SQL 2000, I can use these new functions.

Thanks again!

Mark

------------------------
GENERAL-ly speaking...
Go to Top of Page

idur022
Starting Member

1 Posts

Posted - 01/24/2005 :  14:59:18  Show Profile  Reply with Quote
Quote: "It's also possible for an insert to fail and "use up" an identity value"
Does that mean that if I check the identity value at the start of a transaction (x), insert y rows that the identity at the end is NOT guaranteed to be y+x?
Cheers,
Bill
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/24/2005 :  16:00:49  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
yes. try it. add some rows to your table. delete the last 5 rows you just added, and then add 5 more. see how this affects the identity column.

- Jeff
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/24/2005 :  22:48:33  Show Profile  Reply with Quote

SET NOCOUNT ON
CREATE TABLE #TEMP
(
	ID	int IDENTITY(1,1),
	foobar	varchar(10)
)

INSERT INTO #TEMP VALUES ('#1')

SELECT '(A)', * FROM #TEMP

BEGIN TRANSACTION
INSERT INTO #TEMP 
SELECT '#2a' UNION ALL
SELECT '#3a' UNION ALL
SELECT '#4a' UNION ALL
SELECT '#5a'
ROLLBACK

SELECT '(B)', * FROM #TEMP

INSERT INTO #TEMP 
SELECT '#2b' UNION ALL
SELECT '#3b' UNION ALL
SELECT '#4b' UNION ALL
SELECT '#5b'

SELECT '(C)', * FROM #TEMP
GO

DROP TABLE #TEMP
Go
SET NOCOUNT OFF

Kristen
Go to Top of Page

r937
Posting Yak Master

Canada
112 Posts

Posted - 06/27/2006 :  08:35:30  Show Profile  Visit r937's Homepage  Reply with Quote
article should be edited

it says in several places that such-and-such "is available only in sql server 2000"

this could give people the (wrong) idea that it doesn't work in sql server 2005

rudy
http://r937.com/
Go to Top of Page

koshyis
Starting Member

1 Posts

Posted - 07/16/2007 :  01:15:19  Show Profile  Reply with Quote
The identity column of a table can be reset with the following:

DBCC CHECKIDENT (Table_Name, RESEED, 0)

Regards...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000