Difference between moving index and recreating primary key constraint

Question

Difference between moving index and recreating primary key constraint

dkschill

Hall of Fame

Points: 3375
More actions
July 12, 2014 at 1:05 pm

#287895

I am in the process of generating scripts to move indexes to a new file group. I can't seem to figure out if I am hurting anything by recreating the primary key's index without recreating the constraint.
CREATE TABLE a (a INT NOT NULL);
ALTER TABLE a ADD CONSTRAINT pk_a PRIMARY KEY(a);
INSERT INTO a SELECT 1;
SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = 'a';
CREATE UNIQUE CLUSTERED INDEX pk_a ON a (a)
WITH (DROP_EXISTING = ON) ON New_FileGroup;
SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = 'a';
DROP TABLE a;
The results appear correct, so the index is moved to the new file group and the index is still marked as the primary key.
Am I missing anything?
I know a lot of scripts exist for this task, but I am wanting something very light weight and specific to our environment.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Question I have is this, are you just trying to move only indexes, meaning nonclustered indexes or are you actually move the tables to a new file group?

dkschill Hall of Fame Points: 3375 More actions · Answer 2

Moving the entire table, the database was setup before I started working on it and I would like to get our tables off of primary.

Thanks for the help!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Then dropping and recreating the clustered indexes on the new filegroup should do just what you are looking for here. A quick check of BOL seems to concur with what you are doing.

dkschill Hall of Fame Points: 3375 More actions · Answer 4

Sorry about that, I should of known to read there a bit more carefully:

If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

http://msdn.microsoft.com/en-us/library/ms188783.aspx