SQL Clone

<a href="//g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="//g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

Log in :: Register :: Not logged in

Write for us

<a href="//g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="//g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Populate partition

By Federico Iori, 2010/03/25

This code is useful when to perform recalculation on very large partitioned tables. It allows to divide the calculation per partition , and works into staging table that are independent from each other

For instance if table has 30 partitions, is possible to run this code IN PARALLEL 30 times,

for example with jobs, or by sqlcmd in batch scripts for windows,

then to switch the results into the target table

It is fast : my test performed a 10mil rows table in about 5 minutes

Limitations: It will work with partitioned tables by range , having PK clustered or nonclustered sorted asc, and no check or foreign key constraints .

Supports data compression , so if the code needs to be compiled on sqlserver2005 is necessary to comment a piece.

Is possible to execute or only to script commands

- Input Parameters:
1. @output_type: S=script only, E=execute only, SE = both
2. @SchemaName - Partitioned Table Schema Name
3. @TabName - Partitioned Table Name
4. @PartitionNo - Partition number to be rebuilded

Example

exec dbo.populate_partition 'S','dbo','POSTPAID_RATED_EVENT',15,
' ( select *
from POSTPAID_RATED_EVENT_old a
with(tablock)
where 1=1
and $partition.Traffic_Data_Monthly_PF(Network_Start_Time) =15 ) qioren'

Warning: Clause with(tablock) could create problems during parallel execution

create function dbo.f_get_filegroup_name ( @schemaName varchar(50) = 'dbo',@TabName sysname, @index_name sysname, @PartitionNo int ) 
returns sysname as  
begin
 declare @filegroup sysname
 SELECT @filegroup=
 CASE
 WHEN fg.name IS NULL THEN ds.name
 ELSE fg.name
 END 
 FROM sys.dm_db_partition_stats p
 INNER JOIN sys.indexes i
 ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
 INNER JOIN sys.data_spaces ds
 ON ds.data_space_id = i.data_space_id
 LEFT OUTER JOIN sys.partition_schemes ps
 ON ps.data_space_id = i.data_space_id
 LEFT OUTER JOIN sys.destination_data_spaces dds
 ON dds.partition_scheme_id = ps.data_space_id
 AND dds.destination_id = p.partition_number
 LEFT OUTER JOIN sys.filegroups fg
 ON fg.data_space_id = dds.data_space_id
 LEFT OUTER JOIN sys.partition_range_values prv_right
 ON prv_right.function_id = ps.function_id
 AND prv_right.boundary_id = p.partition_number
 LEFT OUTER JOIN sys.partition_range_values prv_left
 ON prv_left.function_id = ps.function_id
 AND prv_left.boundary_id = p.partition_number - 1
 WHERE
 OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
 AND ( @index_name is null and p.index_id IN (0,1)
 AND OBJECT_NAME(p.OBJECT_ID)=@TabName or
  @index_name is not null and p.index_id > 1 and OBJECT_NAME(p.OBJECT_ID)=@TabName 
  and i.name = @index_name )
 AND p.partition_number=@PartitionNo;

return @filegroup;

/*
- Procedure Name: REBUILD_PARTITION
- Date of creation: 26-feb-2010
- Author- Federico Iori
- Email- fediori@yahoo.com
 
- Description: Rebuilds specified Partition from a partitioned table by range datetime.
- Limitations :
  It should not be working with compressed tables or indexes ,
  with table containing foreign keys or check constraints
  with clustered primary key defined as desc 
  Only partitions range datetime 

-Howitworks:  Uses 2 temporary tables 
 Copies all partition rows to a temporary table b, build indexes on this tables 
 then switch target partition into temp tab a 
 then switch data from temp tab b into target partition
 then drop temporaty tables a and b 

-- In this way is possible to rebuild partitions with indexes ONLINE 
-- SQL server 2008 does not allow to rebuild online a partitioned index
- Input Parameters: 
 1. @output_type: S=script only, E=execute only, SE = both
 2. @SchemaName - Partitioned Table Schema Name 
 3. @TabName - Partitioned Table Name
 4. @PartitionNo - Partition number to be rebuilded

- Command for execution- exec REBUILD_PARTITION 'SchemaName','TableName',PartitionNumber
- Successful Test results for- 
  1) Nonclustered PK + indexes 
  2) Clustered PK + indexes 
- Change History
 v1.0 Creation - 26-Feb-2010
  v2.0 Created 19 mar 2010 
   Replace "insert into b select from src_table" with 
   "select * into b from source table" and then move to, much faster
   Will work only with tables having PK nonclustered  or PK clustered not ordered desc
     identity or datum in general are always asc , so it's OK 
Support for compression  , but code will not compile on sqlserver 2005
  Sql Server 2008 introduced alter table rebuild partition=n , online 
  but still is not possible to rebuild single partition of an index online , 
  so this code can be sometime useful 
  Also can be used for scripting tables , useful for parallel partition-wise loading
  Consider for instance ETL load into partitioned table. 
  Sql Server 2008 should offer the possibility to parallel load all partitions together 
   using ALTER TABLE TMPFI_POSTPAID_RATED_EVENT SET (LOCK_ESCALATION = AUTO),
   wich should permit to put lock on a single partition
  but if it is not working is possible to run a TSQL script or SSIS package for each partition 
   into a separate staging table obtained with this script, then switching partitions
   all together
 v3.0 - populate partition 
   Populates partition with a specific select , useful for Very large ETL
exec dbo.populate_partition 'S','dbo','POSTPAID_RATED_EVENT',15,'TMPFI_POSTPAID_RATED_EVENT',
' ( select * 
from  DWHSK_TARGET_PROD.dbo.POSTPAID_RATED_EVENT a 
with(tablock)
where 1=1 
and $partition.Traffic_Data_Monthly_PF(Network_Start_Time) =15 ) qioren'
*/
USE [DWHSK_TARGET_PROD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[populate_PARTITION] ( @output_type varchar(10) = 'S', @SchemaName VARCHAR(20),@TabName VARCHAR(100), @PartitionNo INT ,
  @selectstatement varchar(5000) =null  )
AS
SET NOCOUNT ON
BEGIN 
 DECLARE @sqlstatement VARCHAR(max) , @repair_sqlstatement VARCHAR(8000)
 DECLARE @TAB_ID1 int
 declare @ProductVersion varchar(100), @IsCompressed int, @CompressionType varchar(10)
 
 select @ProductVersion= convert(varchar(200),SERVERPROPERTY('ProductVersion'))
 SELECT @TAB_ID1= OBJECT_ID(@SchemaName+'.'+@TabName) 

 /* Step-1 start create staging tables a */  
  set @sqlstatement= 
   'begin try DROP TABLE '+@SchemaName+'.[!del_a_'+@TabName+'_'+CONVERT(VARCHAR(5),@PartitionNo)+'] end try begin catch print '''' end catch ';
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  
set @sqlstatement= 
   'begin try DROP TABLE '+@SchemaName+'.[!del_b_'+@TabName+'_'+CONVERT(VARCHAR(5),@PartitionNo)+'] end try begin catch print '''' end catch ';
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

 SELECT @sqlstatement='select top 0 * into '+@SchemaName+'.[!del_a_'+@TabName+'_'+CONVERT(VARCHAR(5),@PartitionNo)+'] from '+@SchemaName+'.'+@TabName
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

 /* End create staging tables a */

/* B  Step 2 Copy partition data to stage table b */
/* It works only for range datetime partitions */ 
 declare @pafu varchar(300) , @cp_column_list varchar(max), 
 @lower_check varchar(300), @upper_check varchar(300)
 , @pacol varchar(100)
 select @pafu = 
 -- pf.name ,  c.name AS ColumnName 
  '$partition.'+pf.name+'('+c.name+')' 
   , @pacol = c.name
--, prng_up.value up_value
--, prng_down.value low_value
, @lower_check = case when prng_down.value is null then '' else case 
   when pf.type = 'R' then 
  c.name+' > '''+ convert( varchar(50),prng_down.value , 20)+''' ' 
 when pf.type = 'L' then 
  c.name+' >= '''+ convert( varchar(50),prng_down.value , 20)+''' '
 end end --as lower_check
,@upper_check=case when prng_up.value is null then '' else case 
   when pf.type = 'R' then 
  c.name+' <= '''+ convert( varchar(50),prng_up.value , 20)+''' ' 
 when pf.type = 'L' then 
  c.name+' < '''+ convert( varchar(50),prng_up.value , 20)+''' '
 end end --as upper_check
 from   sys.indexes AS i 
        join sys.index_columns ic 
         ON (ic.partition_ordinal > 0) 
            AND (ic.index_id = i.index_id 
                 AND ic.object_id = CAST(i.object_id AS INT)) 
          JOIN sys.columns c 
         ON c.object_id = ic.object_id 
            AND c.column_id = ic.column_id 
		left JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
		left join sys.partition_functions pf on ps.function_id = pf.function_id
        LEFT JOIN sys.partition_range_values prng_up ON prng_up.function_id=pf.function_id
        AND prng_up.boundary_id = @PartitionNo
        left join sys.partition_range_values prng_down ON prng_down.function_id=pf.function_id
        AND prng_down.boundary_id = @PartitionNo-1
      where 
   i.object_id = OBJECT_ID(@SchemaName+'.'+@TabName)  AND i.index_id in ( 0,1) ;
--print ('Iori'+@pafu+'--'+@lower_check+'--'+@upper_check)

set  @cp_column_list=(
select name + ','  from  sys.columns co 
where co.object_id = OBJECT_ID(@SchemaName+'.'+@TabName)
order by column_id FOR XML PATH ('') )
set @cp_column_list = substring(@cp_column_list,1,len(@cp_column_list)-1)

 set @repair_sqlstatement = ' begin try set identity_insert '+ 
   @SCHEMANAME+'.'+@TabName+' on  end try begin catch  end catch '+
' insert into '+ @SCHEMANAME+'.'+@TabName++ 
 ' ('+@cp_column_list+')  select '+@cp_column_list+
 ' from '+ @SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] with(tablock)'
print ('Iori repair_sqlstatement: '+@repair_sqlstatement)

-- Tablock from source table can lock it and block in case of parallel execution
if isnull(@selectstatement,'') ='' 
set @sqlstatement = 'select * into '+ @SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ 
 '] from '+@SchemaName+'.'+@TabName+' /*with(tablock)*/ where '+@pafu+'='+CONVERT(varchar(5),@PartitionNo)+' ';
 else
set @sqlstatement = 'select * into '+ @SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ 
 '] from '+@selectstatement;

  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

 
/* E Step 2 Copy partition data to stage table b */ 

 /*Step4-start add PK */
 DECLARE @Pk_available INT 
 DECLARE @PKCI_available INT 

 SELECT @Pk_available =(SELECT distinct 1 FROM sys.objects a inner join sys.indexes b ON a.object_id=b.object_id 
 WHERE a.object_id =OBJECT_ID(@SchemaName+'.'+@TabName) and (b.is_primary_key=1 or b.is_unique=1) AND b.index_id>=1 )
 SELECT @PKCI_available =(SELECT 1 FROM sys.objects a inner join sys.indexes b ON a.object_id=b.object_id 
 WHERE a.object_id =OBJECT_ID(@SchemaName+'.'+@TabName) and (b.is_primary_key=1 or b.is_unique=1) AND b.index_id=1)
--added on 11th Feb 2010
 if @Pk_available is null
 set @Pk_available=0

 if @PKCI_available is null
 set @PKCI_available=0
------

 IF (@Pk_available='1' or @PKCI_available='1')
 BEGIN
 DECLARE @pkInfo table 
 (SCHEMANAME VARCHAR(20),table_name varchar(100), pk_name varchar(100)
 ,columnName varchar(100), desckey char(1),IsUnique char(1)
 , index_id int ,is_primary_key int , column_id int)
 INSERT INTO @pkInfo 
 (SCHEMANAME, table_name,pk_name,columnName,desckey,IsUnique, index_id, is_primary_key, column_id)
 SELECT 
 SCHEMANAME=@SchemaName,
 B.NAME TABLE_NAME, 
 PK_NAME=
 (SELECT top 1 a.name PK_NAME FROM sys.indexes a 
 WHERE A.OBJECT_ID=B.OBJECT_ID AND A.index_id = c.index_id ),
 COLUMN_NAME=
 (SELECT name FROM sys.columns E WHERE E.OBJECT_ID=B.object_id AND E.column_id=D.column_id),
 D.is_descending_key,
 C.is_unique,
 c.index_id,
 is_primary_key, 
 d.column_id
 FROM SYS.OBJECTS B 
 INNER JOIN sys.INDEXES C ON 
 B.object_id=C.object_id
 INNER JOIN sys.index_columns D ON
 B.object_id=D.object_id and d.index_id = c.index_id
 WHERE B.TYPE='U'
 --AND (C.index_id IN (0,1,2))  
 and c.name is not null  
  and ( is_primary_key=1 or is_unique=1)
 AND B.object_id=@TAB_ID1
 order by is_primary_key desc, is_unique desc, c.index_id asc

 DECLARE @Pkname VARCHAR(100),@columns VARCHAR(4000)

 SELECT top 1 @Pkname=pk_name FROM @pkInfo where pk_name is not null  
 order by is_primary_key desc, isunique desc , index_id asc
 
 DECLARE @ALLcolumns TABLE (idcol1 INT IDENTITY,colname VARCHAR(100))
 INSERT INTO @ALLcolumns (colname)
  SELECT columnName
  +case desckey when 1 then ' desc ' else ' ' end  
  FROM @pkInfo where pk_name=@Pkname order by column_id;
 DECLARE @cnt INT
 DECLARE @clncnt INT
 SELECT @cnt=1
 SELECT @clncnt=COUNT(*) FROM @ALLcolumns
 DECLARE @cols VARCHAR(400)
 SELECT @cols=''
 while @clncnt>=@cnt
 begin
 select @cols=@cols+','+ colname FROM @ALLcolumns WHERE idcol1=@cnt
 select @cnt=@cnt+1
 end 
 select @columns=SUBSTRING(@cols,2,len(@cols))
 
 end 
 
 
print('Iori pk ci='+cast(@Pk_available as varchar(10))+cast(@pkCI_available as varchar(10)) )--+@pkname);
 if @Pk_available='1'
 set @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT  A'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY CLUSTERED ('+@columns+')';
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 -- repeat for b 
 if @Pk_available='1'
 select @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT B'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY CLUSTERED ('+@columns+')'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 
 /*end step 4 add PK */

 /* Step5- Start identify file group of partition to be truncated*/

 Declare @filegroup varchar(50)
 set @filegroup= dbo.f_get_filegroup_name('dbo',@TabName,null,@PartitionNo)


 /* end identify file group of partition to be truncated*/


 /*Step6- Start Move table to File group of Partition Table */
 
 
 if (@Pk_available='1' )
 BEGIN
 select @sqlstatement ='ALTER TABLE '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] DROP CONSTRAINT A'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' WITH (MOVE TO ['+@filegroup+'])'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  if @PKCI_available='0'
     select @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT A'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY nonclustered ( '+@columns+') on ['+@filegroup+']'
  else   
     select @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT A'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY clustered ( '+@columns+') on ['+@filegroup+']'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 END

 -- Repeat for b 
 if (@Pk_available='1' )
 BEGIN
 select @sqlstatement ='ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] DROP CONSTRAINT B'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' WITH (MOVE TO ['+@filegroup+'])'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

  if @PKCI_available='0'
     select @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT B'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY nonclustered ( '+@columns+') on ['+@filegroup+']'
  else   
     select @sqlstatement='ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT B'+@Pkname+CONVERT(varchar(5),@PartitionNo)+' PRIMARY KEY clustered ( '+@columns+') on ['+@filegroup+']'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

 END
  
  /* Begin reproduce NONCLUSTERED nonPK indexes from src table on stage table b */
 DECLARE @IndInfo table (table_name varchar(100), index_name varchar(100),columnName varchar(100), 
  index_column_id int , desckey char(1),IsUnique char(1), fg_name varchar(300), is_pk varchar(1),
  last_column_id int , PA_COL_NAME varchar(100) )
 INSERT INTO @IndInfo 
 (table_name,index_name,columnName,index_column_id,desckey,IsUnique, fg_name,is_pk,last_column_id , PA_COL_NAME )
 SELECT 
 B.NAME TABLE_NAME, 
 PK_NAME=
 (SELECT a.name PK_NAME FROM sys.indexes a 
 WHERE A.OBJECT_ID=B.OBJECT_ID AND A.index_id=c.index_id),
 COLUMN_NAME=
 (SELECT name FROM sys.columns E WHERE E.OBJECT_ID=B.object_id AND E.column_id=D.column_id),
 d.index_column_id,
 D.is_descending_key,
 C.is_unique 
, dbo.f_get_filegroup_name ( @SchemaName,@TabName, c.name, @PartitionNo  ) as fg_name
 , c.is_PRIMARY_KEY
 , qumaco.maco 
 ,  PA_COL.NAME AS PA_COL_NAME
 FROM SYS.OBJECTS B 
 INNER JOIN sys.INDEXES C ON 
 B.object_id=C.object_id
 INNER JOIN sys.index_columns D ON
 B.object_id=D.object_id and d.index_id = c.index_id
 inner join 
 ( select  
     object_id, index_id, max(index_column_id) as maco 
   from sys.index_columns  
  group by object_id, index_id ) qumaco 
 on qumaco.object_id = B.object_id
 and qumaco.index_id= c.index_id 
inner join 
 ( select distinct ab.name from sys.index_columns aa 
   join sys.columns ab on aa.column_id = ab.column_id
    and aa.object_id= ab.object_id
 where aa.object_id=OBJECT_ID(@SchemaName+'.'+@TabName)
 and aa.partition_ordinal > 0 ) AS pa_col
on 1=1
 WHERE B.TYPE='U'
 AND (C.index_id>1) -- nonclustered 
 and c.is_PRIMARY_KEY <> 1 -- non pk 
 AND B.object_id=OBJECT_ID(@SchemaName+'.'+@TabName)
order by 1,2,4;

DECLARE @c1_index_name varchar(100), @c1IsUnique int, @c1FILEGROUP varchar(300), @IS_PK VARCHAR(1)
 , @last_column_id int , @PA_COL_NAME varchar(100)
declare c1 cursor for 
 select distinct index_name , IsUnique, FG_NAME, IS_PK , last_column_id, PA_COL_NAME from @IndInfo ;

OPEN c1;
FETCH NEXT FROM c1 into @c1_index_name ,@c1IsUnique, @c1FILEGROUP, @IS_PK, @last_column_id, @PA_COL_NAME;
  WHILE @@FETCH_STATUS = 0 
 BEGIN
IF @IS_PK = 1  -- it will never be 
	 begin
	  SET @COLUMNS = 
	 (  SELECT columnName+
	   case desckey when 1 then ' desc ' else ' asc ' end+','  
		FROM  @IndInfo where index_name = @c1_index_name 
	  order by index_column_id FOR XML PATH ('') ) ;
	  set @columns=SUBSTRING(@columns,1,len(@columns)-1);
	 set @sqlstatement=
	 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] ADD CONSTRAINT B'
	 +@c1_index_name+CONVERT(varchar(5),@PartitionNo)
	 +' PRIMARY KEY NONCLUSTERED '
	 + ' ( '+@columns+') on ['+@c1filegroup+']'
    end
ELSE 
	 begin 
	  SET @COLUMNS = 
	 (  SELECT columnName+
	   case desckey when 1 then ' desc ' else ' asc ' end+','  
		FROM  @IndInfo where index_name = @c1_index_name 
		and ( index_column_id < @last_column_id or @last_column_id = 1 )
	  order by index_column_id FOR XML PATH ('') ) ;
	  set @columns=SUBSTRING(@columns,1,len(@columns)-1);
	 set @sqlstatement='CREATE '
	  +case when @c1IsUnique = 1 then ' UNIQUE ' else ' ' end
	 +' INDEX B'+@c1_index_name
	 +CONVERT(varchar(5),@PartitionNo)
	 +' ON '+ @SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)
	 + '] ( '+@columns+') '
     + case when @columns not like '%'+@PA_COL_NAME+'%' 
        then ' include ('+@PA_COL_NAME+') ' else ' ' end
     + ' on ['+@c1filegroup+']' 
	 end
 --print ('Index columns:'+@columns )
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
FETCH NEXT FROM c1 into @c1_index_name ,@c1IsUnique, @c1FILEGROUP, @IS_PK, @last_column_id, @PA_COL_NAME;  
   END;
CLOSE C1;
DEALLOCATE C1;
  
  /* End reproduce indexes from src table on stage table b */

/* B constraint reproducing partition boundaries , necessary for switching partitions */
  if @upper_check is not null
  begin
  set @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] with check ADD CONSTRAINT BCHECK_UP_'
	 +@TabName+CONVERT(varchar(5),@PartitionNo)
     + ' CHECK ('+@upper_check+')' ;
 --print ('Iori'+@sqlstatement) ;
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  set @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] check CONSTRAINT BCHECK_UP_'
	 +@TabName+CONVERT(varchar(5),@PartitionNo) ;
 --print ('Iori'+@sqlstatement) ;
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  end;

  if @lower_check is not null
  begin
  set @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] with check ADD CONSTRAINT BCHECK_LOW_'
	 +@TabName+CONVERT(varchar(5),@PartitionNo)
     + ' CHECK ('+@lower_check+')' ;
 --print ('Iori'+@sqlstatement) ;
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  set @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+ '] check CONSTRAINT BCHECK_LOW_'
	 +@TabName+CONVERT(varchar(5),@PartitionNo) ;
 --print ('Iori'+@sqlstatement) ;
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
  end;

/* E constraint reproducing partition boundaries */


 --STEP 6 
 --  Added functionality to the script of source partitioned table/ partition is compressed.
-- Compression only on sql server 2008 

if  @ProductVersion  like '10%'
begin
 declare @index_id int
 declare c_compr cursor for 
   select distinct 
    b.name  --data_compression , 
    ,b.index_id
    , data_compression_desc  
     from sys.partitions a
     join sys.indexes b 
     on A.OBJECT_ID=B.OBJECT_ID AND A.index_id=b.index_id
      where a.partition_number=@PartitionNo 
      and a.object_id=OBJECT_ID(@SchemaName+'.'+@TabName)
     and data_compression <> 0 ;
  open c_compr 
FETCH NEXT FROM c_compr into @c1_index_name , @index_id, @CompressionType ;
  WHILE @@FETCH_STATUS = 0 
   begin 
     if @index_id in( 0 ,1) -- heap or pk clustered
       begin 
		  select @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@CompressionType+')'
		  if @output_type like '%S%' print(@sqlstatement);
		  if @output_type like '%E%' exec(@sqlstatement);
		  select @sqlstatement = 'ALTER TABLE '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@CompressionType+')'
		  if @output_type like '%S%' print(@sqlstatement);
		  if @output_type like '%E%' exec(@sqlstatement);
       end
     if @index_id = 1111111111111 -- pk clustered
       begin 
		  select @sqlstatement = 'ALTER index A'+@c1_index_name+CONVERT(varchar(5),@PartitionNo)+' on '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@CompressionType+')'
		  if @output_type like '%S%' print(@sqlstatement);
		  if @output_type like '%E%' exec(@sqlstatement);
		  select @sqlstatement = 'ALTER index B'+@c1_index_name+CONVERT(varchar(5),@PartitionNo)+' on '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@CompressionType+')'
		  if @output_type like '%S%' print(@sqlstatement);
		  if @output_type like '%E%' exec(@sqlstatement);
       end
     if @index_id > 1 -- normal index
       begin 
		  select @sqlstatement = 'ALTER index B'+@c1_index_name+CONVERT(varchar(5),@PartitionNo)+' on '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@CompressionType+')'
		  if @output_type like '%S%' print(@sqlstatement);
		  if @output_type like '%E%' exec(@sqlstatement);
       end
	FETCH NEXT FROM c_compr into @c1_index_name , @index_id, @CompressionType ;
	END;
	CLOSE c_compr;
	DEALLOCATE c_compr;  
end  

-- E compression adjustments 

 --Step10 - switch partitions , empty targTabName, then filling it
 select @sqlstatement='alter table '+@SCHEMANAME+'.'+@TabName+' switch partition '+CONVERT(varchar(5),@PartitionNo)+' to '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+']'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);

 select @sqlstatement='alter table '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+'] switch to '+@SCHEMANAME+'.'+@TabName+' partition '+CONVERT(varchar(5),@PartitionNo)
 begin try
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 end try 
 begin catch
 set @sqlstatement = 
 'Failed switch, try to run: ' +@repair_sqlstatement ;
  raiserror(@sqlstatement
 ,16,1)
  return;
 end catch
 --Step11 drop staging tables 
 select @sqlstatement='drop table '+@SCHEMANAME+'.[!del_a_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+']'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 select @sqlstatement='drop table '+@SCHEMANAME+'.[!del_b_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo)+']'
  if @output_type like '%S%' print(@sqlstatement);
  if @output_type like '%E%' exec(@sqlstatement);
 
END

Total article views: 748 | Views in the last 30 days: 1

Related Articles

FORUM

Partition

2010/07/19

Partition

FORUM

URGENT: Error executing scripts......

2008/01/24

URGENT: Error executing scripts......

FORUM

Connditional executing of script

2009/09/22

Connditional executing of script

FORUM

how to execute procedures one after the another in an script

2008/09/25

script execution

SCRIPT

Automated Script to Partition Tables

2009/05/22

This script is used to print the alter statement and also partition the table with the respective pa...

Contribute