sqlserver2012 changing filegroups

1. View Filegroup SQL statements

SELECTData_located_on_filegroup=Fg.groupname, table_name=Obj.name fromsysfilegroups FG, sysindexes idx, sysobjects objWHEREIdx.id=obj.id andObj.typeinch('S','U') andIdx.indid< 2 andIdx.groupid=Fg.groupid andObj.name like '%%'--table nameORDER  byFg.groupname

2. Changing filegroups

Description: When creating an index on a table, you can specify that the index be in a different filegroup so that the table is migrated to another filegroup

Step 1: Create an index for the table specify the filegroup

Step 2: Delete the index of the table (the table structure is restored)

3. Use Cases

CREATE procedure [OLAP].[usp_switchoutfact_partition](@date datetime,@tablename varchar( -)) asbeginBEGIN TRANSACTIONDeclare @RangeKey1 nvarchar( -) Declare @RangeKey2 nvarchar( -)   Set @RangeKey1 = Convert(nvarchar( -), Year(@date)*10000 + Month(@date)* - +  Day(@date)) Set @RangeKey2 =Convert(nvarchar( -), Year(DateAdd( Day,1,@date))*10000 + Month(DateAdd( Day,1,@date))* - +  Day(DateAdd( Day,1,@date))) /*--Logic Description: 1. First check that the staging table is not present, then delete the table created by using SELECT INTO new table 2.select into the PRIMARY filegroup, we want to change filegroup 3. When you create an index on a temporary table, you can change the text Group 4. Make the table structure consistent (including index) so delete the index that you just created, and then complete the changes to the filegroup of the Table 5. Table compression, specifying with (Data_compression = PAGE 6. SWITCH PARTITION*/ if exists(Select 1  fromSys.tableswhere object_id=object_id('[OLAP]. [Staging_'+@tablename+']'))    exec('drop table [OLAP]. [Staging_'+@tablename+']')exec('Select TOP (0) * into Olap.staging_'+@tablename+'From OLAP.'+@tablename)exec('CREATE CLUSTERED INDEX pk_staging_'+@tablename+'On [OLAP]. [Staging_'+@tablename+'] ([Partitionrangekey]) with (Online=on) on [FACTFG]')exec('DROP INDEX pk_staging_'+@tablename+'On [OLAP]. [Staging_'+@tablename+']')exec('ALTER TABLE olap.staging_' + @tablename + 'REBUILD with (data_compression = PAGE)')exec('ALTER TABLE [OLAP]. [Staging_'+@tablename+'] with CHECK ADD CONSTRAINT [Chk_staging_'+@tablename+'] CHECK ([partitionrangekey]>=' + @RangeKey1 + 'and [partitionrangekey]<' + @RangeKey2 + 'and [Partitionrangekey] is not NULL)')exec('ALTER TABLE [OLAP]. [Staging_'+@tablename+'] CHECK CONSTRAINT [Chk_staging_'+@tablename+']')exec('ALTER TABLE [OLAP]. ['+@tablename+'] SWITCH PARTITION $PARTITION. [Pf_'+@tablename+'] ('+@RangeKey1+') to [OLAP]. [Staging_'+@tablename+']')COMMIT TRANSACTIONEnd

Invoke statement

Declare @date datetimeSet @date = Convert(datetime,'2016-09-23')Print @dateDeclare @RangeKey1 nvarchar( -)= Convert(nvarchar( -), Year(@date)*10000 + Month(@date)* - +  Day(@date))Print @RangeKey1exec [OLAP].[usp_switchoutfact_partition] @date,'fact_xm_xmbjxx'

sqlserver2012 changing filegroups