Removing Identity property SQL2K5

What is the fastest way to remove identity property of a column.?
I traced Enterprise Manager way of doing, it creates a replicate of the
table and copies all rows and constratints to it and deletes old table and
renames the new table with the original column.This is taking long time
Please suggest.
thxThat's probably pretty much it. i doubt SQL Server can do it any other way.
Anything else would involve the same thing, but in different ways e.g.
DTSing the data into a duplicate table, dropping the old table and renaming
the other one.
"skg" <skg@.yahoo.com> wrote in message
news:%23RcwXTSRGHA.424@.TK2MSFTNGP12.phx.gbl...
> What is the fastest way to remove identity property of a column.?
> I traced Enterprise Manager way of doing, it creates a replicate of the
> table and copies all rows and constratints to it and deletes old table and
> renames the new table with the original column.This is taking long time
> Please suggest.
> thx
>|||I guess you could add another column, copy the data over using an UPDATE sta
tement, drop the old
column and rename the column. But the columns would no be in the same order
as they were originally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gb
l...
> That's probably pretty much it. i doubt SQL Server can do it any other way
.
> Anything else would involve the same thing, but in different ways e.g.
> DTSing the data into a duplicate table, dropping the old table and renamin
g the other one.
> "skg" <skg@.yahoo.com> wrote in message news:%23RcwXTSRGHA.424@.TK2MSFTNGP12
.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTn$x4fRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I guess you could add another column, copy the data over using an UPDATE
>statement, drop the old column and rename the column. But the columns would
>no be in the same order as they were originally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
> news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

Removing clustered index on Primary key.

Hi,
I want to remove clustering of the Primary Key and make a foreign key in the
table the clustered index. Can I do this with a replicated database? I
suppose I hav to do this then on the Publisher and Subscriber database. Does
anybody has any experience with this?
TIA,
Stefan
You have to:
1. exclude this table from publication
2. do the required manipulations of table schema on publisher and
subscribers
3. add table back to publication.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:uA3bA6LZEHA.728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to remove clustering of the Primary Key and make a foreign key in
the
> table the clustered index. Can I do this with a replicated database? I
> suppose I hav to do this then on the Publisher and Subscriber database.
Does
> anybody has any experience with this?
> TIA,
> Stefan
>
|||Stefan,
when I tested this, I found it was possible in merge but not in
transactional replication. Using merge, you should be able to do it directly
on the publisher and using sp_addscriptexec on the subscribers. For
transactional, you'll need to drop then recreate the publication after
making the changes.
HTH,
Paul Ibison
|||I was able to do it by dropping the publication modifying the table and then
replicating it.
Not sure if this is what you want or not.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBMjuFQZEHA.3112@.tk2msftngp13.phx.gbl...
> Stefan,
> when I tested this, I found it was possible in merge but not in
> transactional replication. Using merge, you should be able to do it
directly
> on the publisher and using sp_addscriptexec on the subscribers. For
> transactional, you'll need to drop then recreate the publication after
> making the changes.
> HTH,
> Paul Ibison
>