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
*/
(
@SchemaName VARCHAR(20)
,@TabName VARCHAR(100)
,@PartitionNo INT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
/* Step-1 start create staging table*/
DECLARE @PkIndex VARCHAR(200)
,@NewTab VARCHAR(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 @IsCompressed INT
,@CompressionType VARCHAR(10)
,@altStatement VARCHAR(1000);
SELECT @IsCompressed = DATA_COMPRESSION
,@CompressionType = data_compression_desc
FROM sys.partitions
WHERE object_id = OBJECT_ID(@SchemaName+'.'+@TabName) AND
partition_number = @PartitionNo AND
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 = (
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 AND
b.index_id = 1
);
SELECT @CI_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 = 0 AND
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_name VARCHAR(100)
,columnName VARCHAR(100)
,asckey CHAR(1)
,IsUnique CHAR(1)
);
INSERT INTO @pkInfo
(schemaname
,table_name
,pk_name
,columnName
,asckey
,IsUnique)
SELECT SCHEMANAME = @SchemaName
,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 = 1)
,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
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
WHERE b.type = 'U' AND
C.index_id = 1 AND
B.object_id = @TAB_ID1;
DECLARE @alterstatement VARCHAR(8000)
,@Pkname VARCHAR(100)
,@columns VARCHAR(4000);
SELECT @Pkname = pk_name FROM @pkInfo;
DECLARE @ALLcolumns TABLE(
idcol1 INT IDENTITY
,colname VARCHAR(100)
);
INSERT INTO @ALLcolumns (colname) SELECT columnName FROM @pkInfo;
DECLARE @cnt INT
,@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;
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
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
p.index_id IN (0,1) AND
OBJECT_NAME(p.OBJECT_ID) = @TabName AND
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