August 20, 2010 at 12:47 pm
I have a 1.5 +TB database that is a little unwieldy to deal with at times; and 55% of the data is contained in 3 tables.
I'm looking to create new filegroups and move the large tables to the new file groups using CREATE INDEX ... WITH DROP_EXISTING = ON to move the clustered indexes, and therefore the tables themselves.
I've seen statements similar to the following and wanted to throw it up to group and say: "huh?"
"Notice that the approach will be different if the clustered index is related to a primary key or unique constraint."
I'm curious to know why difference it would make if the clustered index covered the primary key or not? If I [re]create the clustered index with drop_existing, the table gets moved to the new location. What difference would it make if the primary key was part of the clustered index?
Also is there a rule-of-thumb or best practice as to how big a data file should be? I already have non clustered indexes in their on filegroup. I was considering four 250 gb (approximately) filegroups but I don't want to overkill.
August 20, 2010 at 12:57 pm
Well, you have to drop the primary key in order to drop the clustered index, if the primary key was created PRIMARY KEY CLUSTERED. And of course that means that any associated FOREIGN KEYs have to be dropped as well. You could re-add them later of course, but that means that your data integrity guards will be down for a period of time, which to me is risk. Others here disagree with me, but I never cluster the actual PRIMARY KEY constraint; if I want the PK field clustered I use a separate index. That way dropping a clustered index doesn't involve data integrity rules.
August 20, 2010 at 1:09 pm
jeff.mason (8/20/2010)
Well, you have to drop the primary key in order to drop the clustered index, if the primary key was created PRIMARY KEY CLUSTERED. And of course that means that any associated FOREIGN KEYs have to be dropped as well.
I used to think so too, until very recently. As long as the definition of the index isn't changing (and just changing the filegroup doesn't change the definition), neither the pk nor the foreign keys need dropping
CREATE TABLE Testing (
id INT IDENTITY,
SomeCol VARCHAR(20)
CONSTRAINT pk_Testing PRIMARY KEY CLUSTERED (ID) ON [PRIMARY]
)
GO
CREATE TABLE Testing2 (
id INT IDENTITY PRIMARY KEY,
fk int REFERENCES Testing(ID)
)
GO
CREATE UNIQUE CLUSTERED INDEX pk_Testing ON Testing (ID) WITH DROP_EXISTING
ON Secondary
Result: Command(s) completed successfully.
The CREATE INDEX must specify UNIQUE, or it fails, it also cannot change from clustered to nonclustered, and neither can the columns change. Just changing filegroup is however acceptable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 1:26 pm
Huh. Well what do you know....
Of course, it also must have the same name, so if you don't explicitly name the PK at creation time you have to locate the name SQL creates for you.
However, since all it does is move the clustered index, still gonna stick with my separation of key from index. There are times to change from clustered to nonclustered and vice versa.
August 20, 2010 at 1:37 pm
Fortunately in my case it doesn't matter as my primary key index is nonclustered.
Any comments to share on the second half of my post?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply