Article: Understanding Identity Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Understanding Identity Columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-09 : 18:21:02

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

628 Posts

Posted - 2002-03-11 : 16:59:02

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.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-15 : 11:52:18

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...

idur022
Starting Member

1 Post

Posted - 2005-01-24 : 14:59:18

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-24 : 16:00:49

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

Kristen
Test

22859 Posts

Posted - 2005-01-24 : 22:48:33

[code]
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
[/code]
Kristen

r937
Posting Yak Master

112 Posts

Posted - 2006-06-27 : 08:35:30

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/

koshyis
Starting Member

1 Post

Posted - 2007-07-16 : 01:15:19

The identity column of a table can be reset with the following:

DBCC CHECKIDENT (Table_Name, RESEED, 0)

Regards...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources