Developing Matt

My Technical Journal

Is there an Identity seed on this table?

with one comment

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
GO
DROP TABLE dbo.seedtest
GO
EXECUTE sp_rename N’dbo.Tmp_seedtest’, N’seedtest’, ‘OBJECT’
GO
CREATE UNIQUE CLUSTERED INDEX ix_ucl_yrid ON dbo.seedtest
    (
    …
    )

Advertisements

Written by matt

June 5, 2009 at 6:42 am

Posted in Sql Server, T-Sql

One Response

Subscribe to comments with RSS.

  1. ummmm… ctrl, alt, delete.
    Must have clicked wrong link…
    My head hurts now.

    Elizabeth

    June 23, 2009 at 9:45 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: