Question on impact of updating clustered index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question on impact of updating clustered index

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-13 : 08:13:10

I'd like to hear from the mass what the impact of updating a clustered index to a new value on a table of a large size, say 1.4 million records. I have my thoughts but thought I'd see if they agreed with the gurus. The clustered index in this case is a GUID (UNIQUEIDENTIFIER).

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-13 : 09:01:51

You mean change the columns included?

It will have to reorganise all the records and rebuild the other indexes so expect it to take a long time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-13 : 09:51:10

To elaborate, here is the table structure (some columns excluded for simplicity):

CREATE TABLE dbo.ExtractEmployeeBenefit
(
InternalGUID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_ExtractEmployeeBenefit_EmployeeBenefitGUID DEFAULT (NEWID()),
ExtractKey INT NOT NULL,
ExtractNumber INT NOT NULL,
EmployeeKey INT NOT NULL,
BenefitTypeLookupCode NVARCHAR(5) NOT NULL,
EmployeeSSN INT NOT NULL,
ExtractStatus NVARCHAR(6) NOT NULL,
ClientBenefitKey INT NULL,

...more data columns...

CONSTRAINT PK_ExtractEmployeeBenefit PRIMARY KEY CLUSTERED (InternalGuid)
)
GO

CREATE INDEX IX_ExtractEmployeeBenefit ON dbo.ExtractEmployeeBenefit(ExtractKey, EmployeeKey, BenefitTypeLookupCode)
GO

CREATE UNIQUE INDEX IX_ExtractEmployeeBenefit_1 ON dbo.ExtractEmployeeBenefit(ExtractKey, ExtractNumber, EmployeeSSN, BenefitTypeLookupCode, ExtractStatus, ClientBenefitKey)
GO

CREATE INDEX IX_ExtractEmployeeBenefit_2 ON dbo.ExtractEmployeeBenefit(ExtractKey, ExtractNumber, ExtractStatus)
GO

Lets say I am processing record by record and I update the column InternalGUID for a single record to a new value. I hope that clarifies your question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources