April 30, 2010 at 8:05 am
hi,
I need to add an included column to an existing index.doing this operation in SSMS drops the index then recreates. but this index is used 500000 times a day. can we do this index operation online and how?
Regards,
MShenel
April 30, 2010 at 1:24 pm
No, you cannot modify the index that way. You have to drop and recreate it to add additional included columns.
What you could do - and I am not guaranteeing anything here, is create a new index with the appropriate included columns. Once that is done, then you can remove the existing index. I would definitely schedule something like this in off hours.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 1, 2010 at 8:55 am
Jeffrey Williams-493691 (4/30/2010)
No, you cannot modify the index that way. You have to drop and recreate it to add additional included columns.
In Enterprise Edition (and equivalents) an index can be modified this way (with a few restrictions) while remaining fully available to concurrent activity.
See CREATE INDEX (Transact-SQL) for full details, but here's a simple demonstration:
USE tempdb;
GO
CREATE TABLE dbo.Test
(
A INTEGER NOT NULL PRIMARY KEY CLUSTERED,
B INTEGER NULL,
C INTEGER NULL,
D INTEGER NULL
);
GO
INSERT dbo.Test (A, B, C, D) VALUES (1, 2, 3, 4);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.Test (A, B) INCLUDE (C);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.Test (A, B) INCLUDE (C, D)
WITH (
DROP_EXISTING = ON,
ONLINE = ON
);
GO
DROP TABLE dbo.Test;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2010 at 9:31 am
Is that new in 2008 - or can you do it in 2005 also? I knew about drop existing, but did not think you could do it online also. Good to know - thanks.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 1, 2010 at 9:53 pm
Jeffrey Williams-493691 (5/1/2010)
Is that new in 2008 - or can you do it in 2005 also? I knew about drop existing, but did not think you could do it online also. Good to know - thanks.
Yes, it is available in SQL Server 2005 Enterprise and equivalents too.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply