October 30, 2013 at 3:08 am
Sorry but I changed roles and never finished a version with computed columns supported.
June 26, 2014 at 7:20 am
Hi
Another Issue is if you use SPARSE columns. I don't have a script which fixes this yet but the idea is to change the table which is generated within the first lines to mirror the SPARSE columns of the original table.
September 24, 2014 at 12:45 pm
First thing first, awesome script, save me a lot of time!
I tried running the script on a table that's set up as page compression, but the script didn't work, complaining the source / target table have mismatched compression options..
I alter the script to this, and that solved the problem...
If @IsCompressed>=1
what happens is that the data_compression column in sys.partitions for my table is 2, not 1 as expected in the script, so I changed it to >=1, and it's been working fine now.
Just sharing. thanks
May 21, 2015 at 1:29 pm
Just ran across your script. Nice. Thanks.
July 23, 2015 at 12:47 pm
Hi Paul.
Actually, it doesn't removes the partition. On SWITCH command, only the data on the master partition is Switched to the staging one.
To remove the partition (thus emulating the "DROP PARTITION" instruction), you have to issue a MERGE PARTITION after running this procedure. 😎
Regards.
- Thiago (SP)
July 23, 2015 at 1:17 pm
Hi Guys.
I did some Identation to the code, to ease its readability and understanding. 😎
Thanks, Vidyadhar!
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRUNCATE_PARTITION]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TRUNCATE_PARTITION]
GO
CREATE PROCEDURE [dbo].[TRUNCATE_PARTITION]
/*
- Procedure Name: dbo.TRUNCATE_PARTITION
- Date of creation: 06-Jan-2010
- Author- Mr. Vidhaydhar Vijay Pandekar
- Email-
- Description: Truncates specified Partition from partitioned table.
- Application:
1. To truncate the partition automatically and to avoid sequence of manual steps required for truncating partitions
2. As a replacement to the ALTER TABLE TRUNCATE PARITION statement of oracle. This becomes useful when oracle code
requires replacement for this statement while migrating to SQL Server.
- Input Parameters:
1. @SchemaName - Partitioned Table Schema Name
2. @TabName - Partitioned Table Name
3. @PartitionNo - Partition number to be truncated
- Command for execution- exec TRUNCATE_PARTITION 'SchemaName','TableName',PartitionNumber
i.e. exe TRUNCATE_PARTITION 'dbo','Table1',3
- Successful Test results for-
1 No Clustered Primary key and No Clustered Index
2 Clustered Primary key
3 No Primary key and Clustered Index
4. Non Clustered Primary key
- Change History
v1.0 Creation - 06-Jan-2010
V2.0 Modied - 9th Feb-2010- Table Schema name issue resolved
V3.0 Modified- 10th Feb 2010 - step1.5 Added functionality to consider if source table/ partition is compressed????
v4.0 Modified-11th Feb 2010 - Step 2- modified Pk related issue
*/
(
@SchemaNameVARCHAR(20)
,@TabNameVARCHAR(100)
,@PartitionNoINT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
/* Step-1 start create staging table*/
DECLARE @PkIndexVARCHAR(200)
,@NewTabVARCHAR(500)
,@CreateTab VARCHAR(8000);
SET@NewTab = @SchemaName+'.'+@TabName+'_'+CONVERT(VARCHAR(5),@PartitionNo);
SET @CreateTab ='SELECT TOP 0 *
INTO '+@NewTab+'
FROM '+@SchemaName+'.'+@TabName;
-- STEP 1.1
-- ADDED ON July 22, 2015. Added functionality to eventually drop existing temporary table.
IF OBJECT_ID(@NewTab) IS NOT NULL
EXEC ('DROP TABLE '+ @NewTab);
EXEC (@CreateTab);
--STEP 1.5
-- ADDED ON 10th Feb 2010. Added functionality to the script of source partitioned table/ partition is compressed.
DECLARE @IsCompressedINT
,@CompressionTypeVARCHAR(10)
,@altStatementVARCHAR(1000);
SELECT @IsCompressed= DATA_COMPRESSION
,@CompressionType= data_compression_desc
FROMsys.partitions
WHEREobject_id= OBJECT_ID(@SchemaName+'.'+@TabName)AND
partition_number= @PartitionNoAND
index_id= 0;
IF @IsCompressed = 1
BEGIN
SET @altStatement = 'ALTER TABLE '+@NewTab+'
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = '+@CompressionType+')';
EXEC (@altStatement);
END;
/*Step2-start add PK */
DECLARE @Pk_available INT =0
,@CI_available INT =0;
SELECT @Pk_available = (
SELECT1
FROMsys.objects a
INNER JOINsys.indexes b ON a.object_id = b.object_id
WHEREa.object_id= OBJECT_ID(@SchemaName+'.'+@TabName)AND
b.is_primary_key= 1AND
b.index_id= 1
);
SELECT @CI_available = (
SELECT1
FROMsys.objects a
INNER JOINsys.indexes b ON a.object_id = b.object_id
WHEREa.object_id = OBJECT_ID(@SchemaName+'.'+@TabName)AND
b.is_primary_key= 0AND
b.index_id= 1
);
--added on 11th Feb 2010
IF @Pk_available IS NULL SET @Pk_available=0;
IF @CI_available IS NULL SET @CI_available=0;
------
IF (@Pk_available= '1' OR
@CI_available= '1')
BEGIN
DECLARE @TAB_ID1 int;
SELECT @TAB_ID1 = OBJECT_ID(@SchemaName+'.'+@TabName);
DECLARE @pkInfo TABLE(
schemaname VARCHAR(20)
,table_name VARCHAR(100)
,pk_nameVARCHAR(100)
,columnName VARCHAR(100)
,asckeyCHAR(1)
,IsUniqueCHAR(1)
);
INSERT INTO @pkInfo
(schemaname
,table_name
,pk_name
,columnName
,asckey
,IsUnique)
SELECT SCHEMANAME = @SchemaName
,B.NAMETABLE_NAME
,PK_NAME= ( SELECTa.name PK_NAME
FROMsys.indexes a
WHEREa.object_id= b.object_id AND
A.index_id= 1)
,COLUMN_NAME= ( SELECTname
FROMsys.columns E
WHEREE.OBJECT_ID = B.object_id AND
E.column_id= D.column_id)
,D.is_descending_key
,C.is_unique
FROMsys.objects B
INNER JOINsys.indexes C
ONB.object_id = C.object_id
INNER JOINsys.index_columns D
ON B.object_id = D.object_id
WHEREb.type= 'U'AND
C.index_id= 1AND
B.object_id= @TAB_ID1;
DECLARE @alterstatementVARCHAR(8000)
,@PknameVARCHAR(100)
,@columnsVARCHAR(4000);
SELECT @Pkname = pk_name FROM @pkInfo;
DECLARE @ALLcolumns TABLE(
idcol1INT IDENTITY
,colnameVARCHAR(100)
);
INSERT INTO @ALLcolumns (colname) SELECT columnName FROM @pkInfo;
DECLARE @cntINT
,@clncntINT;
SELECT @cnt=1;
SELECT @clncnt = COUNT(*) FROM @ALLcolumns;
DECLARE @cols VARCHAR(400);
SELECT @cols='';
WHILE @clncnt >= @cnt
BEGIN
SELECT@cols = @cols+','+ colname
FROM@ALLcolumns
WHEREidcol1 = @cnt;
SET @cnt=@cnt+1;
END;
SET @columns = SUBSTRING(@cols,2,len(@cols));
END;
IF @Pk_available = '1'
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
ADD CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
PRIMARY KEY CLUSTERED ('+@columns+')';
IF @Pk_available <> '1'
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+'
ADD IDCOL INT
CONSTRAINT PK_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo) +' PRIMARY KEY';
EXEC (@alterstatement);
/* Step3- Start identify file group of partition to be truncated*/
DECLARE @filegroup VARCHAR(50);
SELECT@filegroup = CASE
WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END
FROMsys.dm_db_partition_statsp
INNER JOINsys.indexesi
ONi.object_id= p.object_idAND
i.index_id= p.index_id
INNER JOINsys.data_spacesds
ON ds.data_space_id= i.data_space_id
LEFT OUTER
JOINsys.partition_schemesps
ONps.data_space_id= i.data_space_id
LEFT OUTER
JOINsys.destination_data_spaces dds
ONdds.partition_scheme_id = ps.data_space_idAND
dds.destination_id= p.partition_number
LEFT OUTER
JOINsys.filegroupsfg
ONfg.data_space_id= dds.data_space_id
LEFT OUTER
JOINsys.partition_range_valuesprv_right
ONprv_right.function_id= ps.function_idAND
prv_right.boundary_id= p.partition_number
LEFT OUTER
JOINsys.partition_range_valuesprv_left
ONprv_left.function_id= ps.function_idAND
prv_left.boundary_id= p.partition_number - 1
WHERE
OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped')=0AND
p.index_idIN (0,1)AND
OBJECT_NAME(p.OBJECT_ID)=@TabNameAND
p.partition_number=@PartitionNo;
/*Step4- Start Move table to File group of Partition Table */
IF (@Pk_available='1' )
BEGIN
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
DROP CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
WITH (MOVE TO ['+@filegroup+'])';
EXEC (@alterstatement);
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
ADD CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
PRIMARY KEY ( '+@columns+')';
EXEC (@alterstatement);
END;
IF (@Pk_available<>'1' )
BEGIN
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
DROP CONSTRAINT PK_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo) +'
WITH (MOVE TO ['+@filegroup+'])';
EXEC (@alterstatement);
SELECT @alterstatement='ALTER TABLE '+@NewTab+ '
DROP COLUMN IDCOL';
EXEC (@alterstatement);
END;
/*Step5- Create clustered index of staging table if it is there on source partitioned table to make the schema equal */
IF (@CI_available='1' )
BEGIN
DECLARE @IsUnique char(1);
SELECT@IsUnique = IsUnique
FROM@pkInfo;
IF(@CI_available= '1' AND
@IsUnique= '1')
SELECT @alterstatement = 'CREATE UNIQUE CLUSTERED INDEX '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
ON '+ @NewTab+ ' ( '+@columns+')';
IF (@CI_available= '1' AND
@IsUnique= '0')
SELECT @alterstatement = 'CREATE CLUSTERED INDEX '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
ON '+ @NewTab+ ' ( '+@columns+')'
EXEC (@alterstatement);
END;
--Step6 - switch partition
SELECT @alterstatement = 'ALTER TABLE '+@SCHEMANAME+'.'+@TabName+'
SWITCH PARTITION '+CONVERT(varchar(5),@PartitionNo)+'
TO '+@NewTab;
EXEC (@alterstatement);
--Step7 drop staging table
SELECT @alterstatement = 'DROP TABLE '+@NewTab;
EXEC (@alterstatement);
PRINT 'trunc com sucesso!';
END TRY
--Error Handling
BEGIN CATCH
PRINT 'Truncate Partition Failed due to error.';
SELECT
ERROR_NUMBER()AS ErrorNumber
,ERROR_SEVERITY()AS ErrorSeverity
,ERROR_STATE()AS ErrorState
,ERROR_PROCEDURE()AS ErrorProcedure
,ERROR_LINE()AS ErrorLine
,ERROR_MESSAGE()AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END;
GO
Just to say, I successfully use it in my automated ETL routines everyday. 😎
- Thiago (SP)
March 1, 2016 at 10:24 am
Hi,
For some tables it works fine but for some i'm getting the following error:
"Cannot use duplicate column names in index. Column name '[the partition column]' listed more than once."
It tries to build cluster index with all the columns on the 2 indexes i have on the table...
Any idea?
Thanks!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply