It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post.
This is a question that came up yesterday – can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints? Let's check it out.
First up I'm going to create a couple of tables. Table t1 has a unique constraint backed by a nonclustered index. Table t1 has a primary key constraint backed by a clustered index.
CREATE DATABASE ConstraintTest;
GO
USE ConstraintTest;
GOCREATE TABLE UniqueConstraint (c1 INT UNIQUE NONCLUSTERED);
GO
INSERT INTO UniqueConstraint VALUES (1);
GOCREATE TABLE PrimaryKeyConstraint (c2 INT PRIMARY KEY CLUSTERED);
GO
INSERT INTO PrimaryKeyConstraint VALUES (1);
GO
(Oops – in the original post I c&p'd the wrong code and had the second table as a unique nonclustered constraint too – sorry for the mixup)
Now I'll add another filegroup that we'll try to move the indexes into.
ALTER DATABASE ConstraintTest ADD FILEGROUP ExtraFilegroup;
GOALTER DATABASE ConstraintTest ADD FILE (
NAME = ExtraFile1,
FILENAME = 'C:\SQLskills\ExtraFile1.ndf',
SIZE = 5MB)
TO FILEGROUP ExtraFilegroup;
GO
Now I'll try moving the nonclustered index enforcing the unique constraint.
SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('UniqueConstraint')
GOname index_id
—————————— ——–
NULL 0
UQ__UniqueConstraint__7C8480AE 2
The index we want is index ID=2, so we should be able to move it as follows:
CREATE UNIQUE NONCLUSTERED INDEX UQ__UniqueConstraint__7C8480AE
ON UniqueConstraint (c1) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO
No problem! Now let's try the same thing for the clustered primary key constraint.
SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GOname index_id
—————————— ——–
PK__PrimaryKeyConstr__7E6CC920 1
We only have one choice, so we should be able to rebuild it using:
CREATE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GOMsg 1907, Level 16, State 1, Line 1
Cannot recreate index 'PK__PrimaryKeyConstr__7E6CC920'. The new index definition does not match the constraint being enforced by the existing index.
Hmm – what am I missing? I'll check sys.indexes again and not filter the columns this time:
SELECT * FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GOobject_id name index_id type type_desc is_unique
———- —————————— ——– —- ——— ———
2105058535 PK__PrimaryKeyConstr__7E6CC920 1 1 CLUSTERED 1data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor
————- ————– ————– ——————– ———–
1 0 1 0 0is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks
——— ———– ————— ————— —————-
0 0 0 1 1
Ah – I was missing the unique attribute on the index. What I need to do is the following:
CREATE UNIQUE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO
And that works fine. So – the answer is yes, you can use CREATE INDEX … WITH DROP_EXISTING to move indexes that enforce constraints. This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX. That method is bad, as it creates a window of opportunity for someone to enter data that violates the constraint while the index is dropped.
One thought on “Conference Questions Pot-Pourri #8: How to move constraint indexes?”
<a href="http://www.pythian.com/blogs/1359/log-buffer-123-a-carnival-of-the-vanities-for-dbas">"…Paul S. Randal shows how to move constraint indexes, taking as his cue a question he heard at SQL Connections Fall in Las Vegas…"</a>