| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
MuadDBA
Aged Yak Warrior
USA
623 Posts |
 Posted - 03/11/2002 : 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
USA
3246 Posts |
Posted - 03/15/2002 : 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 Posts |
Posted - 01/24/2005 : 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
USA
7423 Posts |
Posted - 01/24/2005 : 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
United Kingdom
22191 Posts |
Posted - 01/24/2005 : 22:48:33
|
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 |
|
|
|
r937
Posting Yak Master
Canada
112 Posts |
Posted - 06/27/2006 : 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 Posts |
Posted - 07/16/2007 : 01:15:19
|
The identity column of a table can be reset with the following:
DBCC CHECKIDENT (Table_Name, RESEED, 0)
Regards...
|
|
|
| |
Topic |
|
Article: Understanding Identity Columns
