A couple of things I’ve discovered lately while working with the identity property of a column.
1. How to check to see if an identity seed exists on a table and what the column is:
SELECT object_name(id) as table_name, name
FROM syscolumns
WHERE columnproperty(id, name, ‘IsIdentity’) = 1
and object_name(id) = ‘mytable’ –table name (leave this line off just to show them all)
2. How to check to see what the identity values are for that column (what is the base seed and what is the increment value):
select table_name, ident_seed(table_name) as seed, ident_incr(table_name) as incrment
from information_schema.tables where ident_seed(table_name) IS NOT NULL and table_name = ‘mytable’
3. You can only have one identity column on a table.
4. You can reseed it at any time with any new seed.
DBCC CHECKIDENT (tablename, reseed, 0)
5. You can set your own number on an insert with Identity_Insert turned on (without the mess in #6).
6. You can just ‘turn off’ and ‘turn on’ that property in SSMS. Easy. Pie. Simple. Even FUN! But what goes on behind the scene? A lot of ugliness. Don’t do it unless you really need to. It creates a new table. inserts into your new table. drops your original, renames, re-creates indexes, etc. Basically, it just throws up all over your server and data:
CREATE TABLE dbo.Tmp_seedtest
(
…
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_seedtest SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest ON
GO
IF EXISTS(SELECT * FROM dbo.seedtest)
EXEC(’INSERT INTO dbo.Tmp_seedtest (year, id, projectname)
SELECT year, id, projectname FROM dbo.seedtest WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest OFF
GODROP TABLE dbo.seedtest
GO
EXECUTE sp_rename N’dbo.Tmp_seedtest’, N’seedtest’, ‘OBJECT’
GOCREATE UNIQUE CLUSTERED INDEX ix_ucl_yrid ON dbo.seedtest
(
…
)
