Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>