Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa
> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it stil
l
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
>> What gives? When I enter a value in the column...it starts with 0. I want
>> it
>> to start with 1!!! Any suggestions?
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column

Hi,

I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.

Do you mean turn it off permanently or what exactly? What are your exact, particular circumstances?

|||
To turn off identity temporarily and allow explicit inserts to the table, you have to issue SET IDENTITY_INSERT TableName ON. Note that you can have only one table with this property set to ON in a session. This will be in effect until you issue
SET IDENTITY_INSERT TableName OFF to come back to the identity.

If you wish to permanently delete the identity property then you may have to go for other solution, like adding a new column without identity to the table, insert the values for the existing data, delete the old column with identity property and rename the column back to the original name. Or you can create a new table with almost same structute except the idenity and export the data, drop the old table, rename the table name.

As a sidenote, why do you want to remove the identity property. As you can see, it can be done but its pain to do it.