drew.allen (1/5/2012)
IDENTITY is a number that is unique for a given table. PERIOD.
Not without something to enforce that uniqueness it isn't. 😛
CREATE TABLE #T (id int IDENTITY(1,1) NOT NULL)
INSERT #T DEFAULT VALUES
SELECT * FROM #T
DBCC CHECKIDENT(#T, RESEED, 0)
INSERT #T DEFAULT VALUES
SELECT * FROM #T
SET IDENTITY_INSERT #T ON
INSERT #T (id) VALUES (1)
SET IDENTITY_INSERT #T OFF
SELECT * FROM #T
DROP TABLE #T
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi