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.
| Author |
Topic |
|
billsox
Yak Posting Veteran
74 Posts |
 Posted - 2002-05-14 : 10:01:31
|
| Hi,Can someone explain to me why the results of the following two code snippets are different?--Snippet 1:CREATE TABLE #TMP (p_id INT IDENTITY(1, 1))INSERT INTO #TMP DEFAULT VALUES INSERT INTO #TMP DEFAULT VALUES DELETE FROM #TMP DBCC CHECKIDENT (#TMP, RESEED, 0)INSERT INTO #TMP DEFAULT VALUES INSERT INTO #TMP DEFAULT VALUES INSERT INTO #TMP DEFAULT VALUES SELECT * FROM #TMP--Snippet 2:CREATE TABLE #TMP2 (p_id INT IDENTITY(1, 1))DBCC CHECKIDENT (#TMP2, RESEED, 0)INSERT INTO #TMP2 DEFAULT VALUES INSERT INTO #TMP2 DEFAULT VALUES INSERT INTO #TMP2 DEFAULT VALUES SELECT * FROM #TMP2To me, it seems like the dbcc command should force the identity column to start its numbering at zero. But in Snippet 1, it starts at one. Any ideas why?Bill |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 10:21:40
|
| According to Books Online, this is the correct behavior:The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table. |
 |
|
|
|
|
|
|
|
DBCC CHECKIDENT