Identity Seed

Can you run an alter table script to change the seed on a table or do you
have to redefine the table DDL?
I want the seed to be 0 instead of 1Check for the DBCC CHECKIDENT in BOL and see if it helps.
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E7B08050-BD84-4AD1-8E4C-D3C6192C39B7@.microsoft.com...
> Can you run an alter table script to change the seed on a table or do you
> have to redefine the table DDL?
> I want the seed to be 0 instead of 1|||thanks MC but I want to cheange the seed not the identity
Can I update the INFORMATION_SCHEMA.TABLES Seed value'|||Itried this but that aint gunna work.
Any other ideas?
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
and TABLE_NAME in ('Pot_lu_rd_county')
update INFORMATION_SCHEMA.TABLES
set IDENT_SEED(TABLE_NAME) = 0
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
('Pot_lu_rd_county')|||You can change the seed value by using DBCC CHECKIDENT (table, reseed, new
seed value) options. Perhaps I misunderstood something?
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8D21B11D-46D9-46B0-B397-843015B31F86@.microsoft.com...
> Itried this but that aint gunna work.
> Any other ideas?
> SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
> and TABLE_NAME in ('Pot_lu_rd_county')
> update INFORMATION_SCHEMA.TABLES
> set IDENT_SEED(TABLE_NAME) = 0
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
> ('Pot_lu_rd_county')|||marcmc wrote on Thu, 13 Apr 2006 04:52:01 -0700:

> thanks MC but I want to cheange the seed not the identity
DBCC CHECKINDENT is for changing the seed. Read the description again. The
seed is the value to be used for the first row if the table is empty, or the
next value to use -1 if the table has rows (ie. if you set the seed to 10
and the table has a row in it already, the next new row will be given the
value 11 for the identity)
Or do you mean you want to change the increment?
Dan|||Hi,
I copied the contents of the table to a temp table and ran
DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
sp_help still says the seed is 1
and populated the table again. The first id is still 1.
I want the tables first id to be 0|||There doesn't seem to be a way apart from dropping the table & redefining th
e
ddl with the following'
IDENTITY (0, 1)|||Did you try setting the seed to -1
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6BDD2FF5-0619-4EE4-86AB-BB8C3F80A4E6@.microsoft.com...
> Hi,
> I copied the contents of the table to a temp table and ran
> DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
> sp_help still says the seed is 1
> and populated the table again. The first id is still 1.
> I want the tables first id to be 0

Identity columns

Hi,
Quick question, Can I use the 'Alter table mytable alter column' to
change the Identity column to include the 'not for replication' clause? Or
do I need to drop and recreated the table?
thanks,
Nope, but you can use this:
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
this will fix your identity keys on all of your tables for the not for
replication switch
If you want to limit it to a set of tables you are best to do this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0 and id=object_id('tablename')
go
sp_configure 'allow updates', 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <jkdriscoll@.qg.com> wrote in message
news:d2hjin$2kfu$1@.sxnews1.qg.com...
> Hi,
> Quick question, Can I use the 'Alter table mytable alter column' to
> change the Identity column to include the 'not for replication' clause? Or
> do I need to drop and recreated the table?
> thanks,
>
|||Hilary,
Thank you for the response. I can't begin to tell you how much work this
will save me. We've had this one SQL Server, that is production and was
originally only used a little bit. But, as you can guess, over time it's
grown in use. Now we need to set up replication. And what a project this will
be. This server now as 23 production databases, none of which where designed
with replication in mind.
So, once again I say Thanks.
Joe.
"Hilary Cotter" wrote:

> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||I have thought of a follow up question.
Will this sql affect any current or future Identity column values?
thanks again.
Joe
"Hilary Cotter" wrote:

> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||no it won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JD" <John316@.online.nospam> wrote in message
news:3715E8B9-22F3-40EB-BDBF-09036E45A71D@.microsoft.com...[vbcol=seagreen]
> I have thought of a follow up question.
> Will this sql affect any current or future Identity column values?
> thanks again.
> Joe
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
clause? Or[vbcol=seagreen]

Identity Column

I have created an identity column in a table. Now I want to drop the
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Identity Column

I have created an identity column in a table. Now I want to drop the
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.