September 10, 2010 at 11:55 am
Primary key constraints can be moved along with the table using the
alter table.... drop constraint...with move command. Then you add the constraint back in to the table after it's moved. Non-unique nonclustered indexes need to be recreated on the new filegroup. Unique nonclustered indexes/constraints need to be dropped, then recreated with an alter table command on the new filegroup.
But foreign keys??? They don't seem to have their own storage area as viewed from sql 2008 management studio (we're still using sql 2005), so these are just references kept within the table itself??
alter table [dbo].[EVENT_HISTORY] drop constraint EVENT_HISTORY_PK with (move to CM_Log)
alter table [dbo].[EVENT_HISTORY] WITH NOCHECK ADD CONSTRAINT [EVENT_HISTORY_PK] PRIMARY KEY CLUSTERED (EVENT_HISTORY_ID) WITH (FILLFACTOR = 100, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-- CAN'T USE DROP_EXISTING WITH ALTER TABLE, ONLY WITH CREATE NONCLUSTERED (not unique) INDEX
ALTER TABLE [dbo].[EVENT_HISTORY] ADD CONSTRAINT [EVENT_HISTORY_UC1] UNIQUE NONCLUSTERED
(
[EVENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Test]
CREATE NONCLUSTERED INDEX [index_BillingEntry_OwnershipDocId] ON [dbo].[BILLING_ENTRY]
(
[OWNERSHIP_DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
September 10, 2010 at 1:27 pm
Foreign keys are not created on one filegroup or another unlike primary keys.
You can designate that a primary key be created on a different file group due to the index created with it (default behavior). It is actually the index that is being stored (clustered or nonclustered) on the destination filegroup.
Thus, you need not worry about trying to move the FK. However, if you created an index on the FK - then you can move the index to a different filegroup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 10, 2010 at 1:56 pm
Indianrock (9/10/2010)
But foreign keys??? They don't seem to have their own storage area as viewed from sql 2008 management studio (we're still using sql 2005), so these are just references kept within the table itself??
Foreign keys are not indexes, so they don't have a physical storage. If you want to get technical, they're stored in the system tables.
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
September 10, 2010 at 2:16 pm
Great, that makes our project to improve IO easier. Of course, foreign keys are one method of enforcing referential integrity, but are not indexes. ( I knew that 🙂 )
We found that since the last round of this "splitting" ( moving tables out of what was the single, primary filegroup into new filegroups on new luns ), some indexes were created on the default primary filegroup, instead of where they should have gone, with the migrated tables ( or at least not in the primary filegroup ).
September 10, 2010 at 2:26 pm
That's an easy fix as well. Make sure you designate the new filegroup as your default filegroup and new indexes will be created there by default.
Otherwise make sure to specify the filegroup during creation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply