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;
GO
CREATE TABLE UniqueConstraint (c1 INT UNIQUE NONCLUSTERED);
GO
INSERT INTO UniqueConstraint VALUES (1);
GO
CREATE 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;
GO
ALTER 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')
GO
name 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')
GO
name 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;
GO
Msg 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')
GO
object_id name index_id type type_desc is_unique
---------- ------------------------------ -------- ---- --------- ---------
2105058535 PK__PrimaryKeyConstr__7E6CC920 1 1 CLUSTERED 1
data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor
------------- -------------- -------------- -------------------- -----------
1 0 1 0 0
is_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.