[MS SQL Server] SWITCH statement: required indexes in target table? |
| SWITCH statement: required indexes in target table? Posted: 29 Sep 2013 05:43 PM PDT hello,is there some information about which indexes have to be created on a table to which I want to switch a partition? Books online says:[i]The corresponding indexes, or index partitions, must also reside in the same filegroup.[/i]But it does not tell me wether the target table [b]has[/b] to have any indexes at all.After some testing I got the following:Test 1: A partitioned source table with a clustered PK. The target table, not partitioned, needs the same PK definition.Test 2: A partioned source table with a nonclusterd PK and an additional clustered index. In this case the target table, not partitioned, does not need any index or PK to perform the switch.Now I'd like to have the official rules what is neccessary for the target table. Did not find any useful on google.For people who like to evaluate my testing results, here is my testing skript:[code="sql"]-- create a test databasecreate database Test01;goUSE Test01GO-- create the parition function and schemeCREATE PARTITION FUNCTION [PFN_EventPartitionKey](smallint) AS RANGE RIGHT FOR VALUES (1309, 1310)GOCREATE PARTITION SCHEME [PS_EventPartitionKey] AS PARTITION [PFN_EventPartitionKey] TO ([PRIMARY], [PRIMARY], [PRIMARY] )GO-- create 2 data tables with different kind of PK and clustered indexcreate table dbo.PartitionTest1( Id bigint not null, PartitionKey smallint not null, Col1 varchar(50) null ) on PS_EventPartitionKey( PartitionKey )goalter table dbo.PartitionTest1 add constraint PartitionTest1_PK primary key clustered( Id, PartitionKey ) on PS_EventPartitionKey( PartitionKey )gocreate table dbo.PartitionTest2( Id bigint not null, PartitionKey smallint not null, Col1 varchar(50) null ) on PS_EventPartitionKey( PartitionKey )goalter table dbo.PartitionTest2 add constraint PartitionTest2_PK primary key nonclustered( Id, PartitionKey ) on PS_EventPartitionKey( PartitionKey )gocreate nonclustered index cix_PartitionTest2 on dbo.PartitionTest2 ( Col1 ) on PS_EventPartitionKey( PartitionKey )go-- now create the target tables, in which we want so switchcreate table dbo.PartitionTest1Archive( Id bigint not null, PartitionKey smallint not null, Col1 varchar(50) null ) on [PRIMARY]go--alter table dbo.PartitionTest1Archive-- add constraint PartitionTest1Archive_PK-- primary key clustered( Id, PartitionKey )-- on [PRIMARY]--go-- this raises an error. we have to create the PK first (the comment here above)alter table dbo.PartitionTest1 switch partition 2 to dbo.PartitionTest1Archive;gocreate table dbo.PartitionTest2Archive( Id bigint not null, PartitionKey smallint not null, Col1 varchar(50) null ) on [PRIMARY]go-- this worksalter table dbo.PartitionTest2 switch partition 2 to dbo.PartitionTest2Archive;go[/code] |
| Management Data Warehouse Purge Posted: 29 Sep 2013 09:51 PM PDT Does anyone know where the table core.source_info_internal gets the days_until_expiration value from.I have an MDW database which has grown to epic proportions.It looks like the MDW purge SQL agent job which executes the core.sp_purge_data is getting the valid_through date via the core.snapshots view which the purge is based on from the core.source_info_internal - days_until_expiration column. The days_until_expiration value is set to 730 for a number of collections. I cannot seem to find this value in the Data Collection retention days setting in the SQL agent job which are set to a more manageable no. of days. Could these values have been changed via the agent jobs but not yet persisted to the table until a restart of the agent service is executed? |
| Posted: 29 Sep 2013 07:51 AM PDT Dear Experts How to drop all auto sys statistics in the whole databaseThanks lot |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment