declare @parameter1 int, @parameter2 date
select @parameter1=123, @parameter2='2013-08-28'
IF (object_id('tempdb..#TempTable')) is not null
BEGIN
drop table #TempTable
END
CREATE TABLE #TempTable(col1 int, col2 varchar(50), primary key clustered(col1))
INSERT INTO #TempTable
EXECUTE [LinkedServerName].[DatabaseName].[SchemaName].[usp_StoredProcedure] @parameter1=@parameter1, @parameter2=@parameter2
SELECT * FROM #TempTable
Wednesday, 28 August 2013
Thursday, 8 August 2013
T-SQL funciton to calculate CUSIP check digit
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCusipCheckDigit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetCusipCheckDigit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------
-- Author Date
-- Steven Rao 2013-08-08
---------------------------------------------------------------------------------------------------------------
-- Return the check digit for a CUSIP number
---------------------------------------------------------------------------------------------------------------
-- Reference:
-- http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55474df-6908-4d05-bf23-7b341bd7994f/user-defined-function
-----------------------------------------------------------------------------------------------------------------
-- Test script:
--DECLARE @Sample TABLE ( Cusip CHAR(9) );
--INSERT INTO @Sample
--VALUES ('14149YAR9')
-- ,('000020347')
-- ,('126650BG4')
-- ,('254709AC2')
-- ,('437076AQ5') ;
--SELECT *, dbo.[fn_GetCusipCheckDigit](LEFT(Cusip, 8)) as CheckDigit FROM @Sample ;
-----------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_GetCusipCheckDigit]
(
@Cusip CHAR(8)
)
RETURNS CHAR(1)
AS
BEGIN
RETURN
(
SELECT (10 - SUM(s/10 + S%10) % 10) % 10
FROM (
SELECT s=(NULLIF(CHARINDEX(substring(@Cusip, V.number, 1), R.Ref),0) -1) * (2 - V.number % 2)
FROM master..spt_values V CROSS JOIN
(SELECT Ref='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ*@#') R
WHERE V.type='P' and V.number BETWEEN 1 AND 8
) SQ
)
END
GO
DROP FUNCTION [dbo].[fn_GetCusipCheckDigit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------
-- Author Date
-- Steven Rao 2013-08-08
---------------------------------------------------------------------------------------------------------------
-- Return the check digit for a CUSIP number
---------------------------------------------------------------------------------------------------------------
-- Reference:
-- http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55474df-6908-4d05-bf23-7b341bd7994f/user-defined-function
-----------------------------------------------------------------------------------------------------------------
-- Test script:
--DECLARE @Sample TABLE ( Cusip CHAR(9) );
--INSERT INTO @Sample
--VALUES ('14149YAR9')
-- ,('000020347')
-- ,('126650BG4')
-- ,('254709AC2')
-- ,('437076AQ5') ;
--SELECT *, dbo.[fn_GetCusipCheckDigit](LEFT(Cusip, 8)) as CheckDigit FROM @Sample ;
-----------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_GetCusipCheckDigit]
(
@Cusip CHAR(8)
)
RETURNS CHAR(1)
AS
BEGIN
RETURN
(
SELECT (10 - SUM(s/10 + S%10) % 10) % 10
FROM (
SELECT s=(NULLIF(CHARINDEX(substring(@Cusip, V.number, 1), R.Ref),0) -1) * (2 - V.number % 2)
FROM master..spt_values V CROSS JOIN
(SELECT Ref='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ*@#') R
WHERE V.type='P' and V.number BETWEEN 1 AND 8
) SQ
)
END
GO
Saturday, 13 July 2013
Enable/Disable a table to be read only on the fly (usp_Util_SetTableReadOnly)
published at:
http://www.sqlservercentral.com/articles/read+only/100618/
USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Util_SetTableReadOnly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
GO
-- =================================================================================================
--
-- Author Date Description
-- ================================================================================================
-- Steven Rao (StevenZRAO@gmail.com) 2013-07-13 original draft
-- ================================================================================================
-- PURPOSE: Enable/Disable read only property to any give table
-- =================================================================================================
-- Reference:
-- http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/
-- http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/26/how-to-make-a-table-read-only-in-sql-server.aspx
-- http://www.sqlservercentral.com/articles/Advanced+Querying/readonlytables/2517/
-- =================================================================================================
CREATE PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
@tableName varchar(50)
,@readOnly tinyint=1
,@schemaName varchar(50)='dbo'
,@debug tinyint=0
AS
BEGIN
if (@readOnly NOT IN (0, 1)) return 10
declare @error int, @rowsCount int, @now datetime2
--
-- verify schema and table name, also trim them if needed
-- warning: don't swtich the order of assignment here!
select
@tableName=ltrim(rtrim(@tableName))
,@schemaName=ltrim(rtrim(@schemaName))
,@tableName=t.name
from sys.schemas s inner join sys.tables t
on s.schema_id=t.schema_id
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @now=SYSDATETIME()
if (@error<>0) RETURN 20
if (@rowsCount<>1) RETURN 30
declare @createCheckConstraint varchar(8000), @dropCheckConstraint varchar(8000), @createDeleteTrigger varchar(8000), @dropDeleteTrigger varchar(8000)
if (@readOnly=1)
begin
select
@createCheckConstraint='IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)+
+'ALTER TABLE ['+@schemaName+'].['+@tableName+'] WITH NOCHECK ADD CONSTRAINT [CK_'+@tableName+'_ReadOnly] CHECK (1=0)'
,@createDeleteTrigger='IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'EXEC dbo.sp_executesql @statement =N'+
+''''+'CREATE TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete] ON '+'['+@schemaName+'].['+@tableName+']'+char(13)
+'INSTEAD OF DELETE'+char(13)
+'AS'+char(13)
+'BEGIN'+char(13)
+'RAISERROR( '+''''+''''+'Deletion of table '+@tableName+' not allowed.'+''''+''''+', 16, 1 )'+char(13)
+'ROLLBACK TRANSACTION'+char(13)
+'END'+''''
if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @createCheckConstraint='+@createCheckConstraint+' @createDeleteTrigger='+@createDeleteTrigger
end
exec (@createCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create check constraint. will return.'
return 110
end
exec (@createDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create trigger for delete. will return.'
return 120
end
end
else
begin
select
@dropCheckConstraint='IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)
+'ALTER TABLE ['+@schemaName+'].['+@tableName+'] DROP CONSTRAINT [CK_'+@tableName+'_ReadOnly]'
,@dropDeleteTrigger='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'DROP TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'
if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @dropCheckConstraint='+@dropCheckConstraint+' @dropDeleteTrigger='+@dropDeleteTrigger
end
exec (@dropCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop check constraint. will return.'
return 130
end
exec (@dropDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop trigger for delete. will return.'
return 140
end
end
return 0
END
http://www.sqlservercentral.com/articles/read+only/100618/
USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Util_SetTableReadOnly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
GO
-- =================================================================================================
--
-- Author Date Description
-- ================================================================================================
-- Steven Rao (StevenZRAO@gmail.com) 2013-07-13 original draft
-- ================================================================================================
-- PURPOSE: Enable/Disable read only property to any give table
-- =================================================================================================
-- Reference:
-- http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/
-- http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/26/how-to-make-a-table-read-only-in-sql-server.aspx
-- http://www.sqlservercentral.com/articles/Advanced+Querying/readonlytables/2517/
-- =================================================================================================
CREATE PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
@tableName varchar(50)
,@readOnly tinyint=1
,@schemaName varchar(50)='dbo'
,@debug tinyint=0
AS
BEGIN
if (@readOnly NOT IN (0, 1)) return 10
declare @error int, @rowsCount int, @now datetime2
--
-- verify schema and table name, also trim them if needed
-- warning: don't swtich the order of assignment here!
select
@tableName=ltrim(rtrim(@tableName))
,@schemaName=ltrim(rtrim(@schemaName))
,@tableName=t.name
from sys.schemas s inner join sys.tables t
on s.schema_id=t.schema_id
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @now=SYSDATETIME()
if (@error<>0) RETURN 20
if (@rowsCount<>1) RETURN 30
declare @createCheckConstraint varchar(8000), @dropCheckConstraint varchar(8000), @createDeleteTrigger varchar(8000), @dropDeleteTrigger varchar(8000)
if (@readOnly=1)
begin
select
@createCheckConstraint='IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)+
+'ALTER TABLE ['+@schemaName+'].['+@tableName+'] WITH NOCHECK ADD CONSTRAINT [CK_'+@tableName+'_ReadOnly] CHECK (1=0)'
,@createDeleteTrigger='IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'EXEC dbo.sp_executesql @statement =N'+
+''''+'CREATE TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete] ON '+'['+@schemaName+'].['+@tableName+']'+char(13)
+'INSTEAD OF DELETE'+char(13)
+'AS'+char(13)
+'BEGIN'+char(13)
+'RAISERROR( '+''''+''''+'Deletion of table '+@tableName+' not allowed.'+''''+''''+', 16, 1 )'+char(13)
+'ROLLBACK TRANSACTION'+char(13)
+'END'+''''
if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @createCheckConstraint='+@createCheckConstraint+' @createDeleteTrigger='+@createDeleteTrigger
end
exec (@createCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create check constraint. will return.'
return 110
end
exec (@createDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create trigger for delete. will return.'
return 120
end
end
else
begin
select
@dropCheckConstraint='IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)
+'ALTER TABLE ['+@schemaName+'].['+@tableName+'] DROP CONSTRAINT [CK_'+@tableName+'_ReadOnly]'
,@dropDeleteTrigger='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'DROP TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'
if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @dropCheckConstraint='+@dropCheckConstraint+' @dropDeleteTrigger='+@dropDeleteTrigger
end
exec (@dropCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop check constraint. will return.'
return 130
end
exec (@dropDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop trigger for delete. will return.'
return 140
end
end
return 0
END
Tuesday, 18 June 2013
fn_Util_IsTablePartitionedAtValue: checking if a specific value is at the boundary point of table partition
---------------------------------------------------------------------------------------------------------------
-- Author Date
-- Steven Rao 2013-06-18
---------------------------------------------------------------------------------------------------------------
-- Assumption:
-- 1) if table is partitioned, it will be using LEFT range type
-- 2) partition function used has parameter of integer data type
-- 3) return value:
-- 1: table @tableName is partitioned and @partitionValue is one of the partition boundry point
-- 0: table @tableName is partitioned but @partitionValue is not a partition boundary point
-- -1: table @tableName is not partioned at all
---------------------------------------------------------------------------------------------------------------
-- Usage example
-- SELECT [fn_Util_IsTablePartitionedAtValue] ('MyTable', 1000000, 'dbo')
---------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [fn_Util_IsTablePartitionedAtValue]
(
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
)
RETURNS INT
AS
BEGIN
DECLARE @boudaryID int
SELECT
TOP 1
-- boundary_id start with 1
@boudaryID=isnull(rv.boundary_id, 0)
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
-- need exact match, assuming left range type
AND cast(rv.value as int)=@partitionValue
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
ORDER BY isnull(rv.boundary_id, 0) DESC
-- if @boudaryID is null then table is NOT partitioned
-- if @boudaryID>0 then we fall into a partition boundary point
-- if @boudaryID=0 table is partitioned but value is not a boundary point
RETURN ISNULL(case when @boudaryID>0 then 1 else @boudaryID end, -1)
END
Tuesday, 11 June 2013
usp_Util_TruncatePartition: trancate a specified partition from a partitioned table
-- published at
-- http://www.sqlservercentral.com/articles/partition+switch/100013/
USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp_Util_TruncatePartition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [usp_Util_TruncatePartition]
GO
--
-- By Steven Rao
-- Date: June 13, 2013
-- StevenZRAO@gmail.com
-- declare @code int
-- A generic procedure to truncate any given partition from a partitioned table
-- Limitation: cannot deal with some data types, such as xml
-- Basic assumption and current limitation: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition parameter has integer type
-- Change history
-- Date Description
-- 2013-07-26 Fix schema name and computed column issues
-- It is now also working both left and right partition type
CREATE PROCEDURE [usp_Util_TruncatePartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
AS
BEGIN
--
-- Is table partitioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
--
-- find the partition number for the partion value and other information for table partition
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
-- partition FOR RIGHT and boundary_id begins with id 1
,@partitionNumber=case when pf.boundary_value_on_right=1 then rv.boundary_id+1 else rv.boundary_id end
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
-- need exact match
AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.'
return 100
end else
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16))
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
end
--
-- create clone table on same file group, drop it if already exists
-- we don't need to take care of FK and no clustered for our case
-- No clustered index is not needed
-- taking care of columns and primary key
-- Ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000),
@createCloneTable varchar(8000),
@primaryKeyName varchar(255),
@dropPrimaryKey varchar(8000),
@primayKeyNamePlaceHolder varchar(255),
@hasPrimaryKey tinyint,
@switchPartition varchar(1024)
select
-- A place holder for primary key which will be replace when the value is known
@primayKeyNamePlaceHolder='$$PK_NAME$$'
,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
+'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13) +'END'
,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
+'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
select
@hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
,@createCloneTable= 'CREATE TABLE ' +@schemaName+'.'+@CloneTableName
+ '(' + o.list + ')'
+ ' ON ['+@partitionFileGroupName+']'
+ CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
+'ALTER TABLE ' +@schemaName+'.'+ @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
+ CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
+ ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sys.schemas s
inner join sys.objects so
on s.schema_id=so.schema_id
cross apply
(SELECT
' ['+column_name+'] '
-- deal with computed columns
+ case when cc.is_computed=1 then ' AS ('+cc.definition+')'+case when cc.is_persisted=1 then ' PERSISTED' else '' end + ', ' else
-- non computed columns case
data_type+
case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
select id
from syscolumns
where object_name(id)='TestTruncatePartition'
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
)
then 'IDENTITY(' + cast(ident_seed('TestTruncatePartition') as varchar) + ',' + cast(ident_incr('TestTruncatePartition') as varchar) + ')'
else '' end
+ ' '
+ (case when c.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
+ case when c.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ c.COLUMN_DEFAULT ELSE '' END + ', '
end
from information_schema.columns c left join sys.computed_columns cc on
OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)=cc.object_id and c.COLUMN_NAME=cc.name
where c.table_name = so.name AND c.TABLE_SCHEMA=@schemaName
order by ordinal_position
FOR XML PATH('')
) o (list)
left join information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
AND tc.TABLE_SCHEMA=@schemaName
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name and kcu.TABLE_SCHEMA=@schemaName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)
-- find out if the primary key is clustered
left join sys.indexes i
on so.object_id=i.object_id
-- index_id 1 means clustered
-- constraint name is primary key name which is same as the index name
AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name and tc.TABLE_SCHEMA=@schemaName
where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end
-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end
else if @compression=2
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- taking care of clustered index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256),
@indexName varchar(256),
@indexUnique varchar(8),
@hasNonPrimaryKeyClusterdIndex tinyint,
@createClusteredIndex varchar(8000),
@dropClusteredIndex varchar(8000)
select @indexColums='(',
@indexUnique='',
@indexName='',
@hasNonPrimaryKeyClusterdIndex=0,
@dropClusteredIndex='',
@createClusteredIndex=''
select
-- whenever we have result from here, we do have clusterd index
@hasNonPrimaryKeyClusterdIndex=1
,@indexColums=@indexColums+ic.columnList
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
from sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
CROSS APPLY
(
select
c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
from sys.index_columns ic
INNER JOIN sys.columns c
ON c.column_id=ic.column_id
and c.object_id=ic.object_id
where ic.index_id = i.index_id
and ic.object_id = t.object_id
order by ic.key_ordinal
FOR XML PATH('')
) ic(columnList)
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126),
@hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
+char(13)+'BEGIN'+char(13)
+'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
+char(13)+'END'
,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
EXEC(@dropCloneTable)
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
EXEC(@createCloneTable)
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@createClusteredIndex)
select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
if (@error<>0) goto ERROR
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
RETURN 0
ERROR:
print @errMsg
return @returnCode
END
-- http://www.sqlservercentral.com/articles/partition+switch/100013/
USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp_Util_TruncatePartition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [usp_Util_TruncatePartition]
GO
--
-- By Steven Rao
-- Date: June 13, 2013
-- StevenZRAO@gmail.com
-- declare @code int
-- A generic procedure to truncate any given partition from a partitioned table
-- Limitation: cannot deal with some data types, such as xml
-- Basic assumption and current limitation: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition parameter has integer type
-- Change history
-- Date Description
-- 2013-07-26 Fix schema name and computed column issues
-- It is now also working both left and right partition type
CREATE PROCEDURE [usp_Util_TruncatePartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
AS
BEGIN
--
-- Is table partitioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
--
-- find the partition number for the partion value and other information for table partition
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
-- partition FOR RIGHT and boundary_id begins with id 1
,@partitionNumber=case when pf.boundary_value_on_right=1 then rv.boundary_id+1 else rv.boundary_id end
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
-- need exact match
AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.'
return 100
end else
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16))
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
end
--
-- create clone table on same file group, drop it if already exists
-- we don't need to take care of FK and no clustered for our case
-- No clustered index is not needed
-- taking care of columns and primary key
-- Ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000),
@createCloneTable varchar(8000),
@primaryKeyName varchar(255),
@dropPrimaryKey varchar(8000),
@primayKeyNamePlaceHolder varchar(255),
@hasPrimaryKey tinyint,
@switchPartition varchar(1024)
select
-- A place holder for primary key which will be replace when the value is known
@primayKeyNamePlaceHolder='$$PK_NAME$$'
,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
+'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13) +'END'
,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
+'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
select
@hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
,@createCloneTable= 'CREATE TABLE ' +@schemaName+'.'+@CloneTableName
+ '(' + o.list + ')'
+ ' ON ['+@partitionFileGroupName+']'
+ CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
+'ALTER TABLE ' +@schemaName+'.'+ @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
+ CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
+ ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sys.schemas s
inner join sys.objects so
on s.schema_id=so.schema_id
cross apply
(SELECT
' ['+column_name+'] '
-- deal with computed columns
+ case when cc.is_computed=1 then ' AS ('+cc.definition+')'+case when cc.is_persisted=1 then ' PERSISTED' else '' end + ', ' else
-- non computed columns case
data_type+
case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
select id
from syscolumns
where object_name(id)='TestTruncatePartition'
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
)
then 'IDENTITY(' + cast(ident_seed('TestTruncatePartition') as varchar) + ',' + cast(ident_incr('TestTruncatePartition') as varchar) + ')'
else '' end
+ ' '
+ (case when c.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
+ case when c.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ c.COLUMN_DEFAULT ELSE '' END + ', '
end
from information_schema.columns c left join sys.computed_columns cc on
OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)=cc.object_id and c.COLUMN_NAME=cc.name
where c.table_name = so.name AND c.TABLE_SCHEMA=@schemaName
order by ordinal_position
FOR XML PATH('')
) o (list)
left join information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
AND tc.TABLE_SCHEMA=@schemaName
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name and kcu.TABLE_SCHEMA=@schemaName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)
-- find out if the primary key is clustered
left join sys.indexes i
on so.object_id=i.object_id
-- index_id 1 means clustered
-- constraint name is primary key name which is same as the index name
AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name and tc.TABLE_SCHEMA=@schemaName
where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end
-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end
else if @compression=2
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- taking care of clustered index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256),
@indexName varchar(256),
@indexUnique varchar(8),
@hasNonPrimaryKeyClusterdIndex tinyint,
@createClusteredIndex varchar(8000),
@dropClusteredIndex varchar(8000)
select @indexColums='(',
@indexUnique='',
@indexName='',
@hasNonPrimaryKeyClusterdIndex=0,
@dropClusteredIndex='',
@createClusteredIndex=''
select
-- whenever we have result from here, we do have clusterd index
@hasNonPrimaryKeyClusterdIndex=1
,@indexColums=@indexColums+ic.columnList
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
from sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
CROSS APPLY
(
select
c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
from sys.index_columns ic
INNER JOIN sys.columns c
ON c.column_id=ic.column_id
and c.object_id=ic.object_id
where ic.index_id = i.index_id
and ic.object_id = t.object_id
order by ic.key_ordinal
FOR XML PATH('')
) ic(columnList)
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR,
@rowsCount=@@ROWCOUNT,
@Now=getdate(),
@LogHeader=convert(char(23), @Now, 126),
@hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
+char(13)+'BEGIN'+char(13)
+'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
+char(13)+'END'
,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
EXEC(@dropCloneTable)
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
EXEC(@createCloneTable)
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@createClusteredIndex)
select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
if (@error<>0) goto ERROR
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
RETURN 0
ERROR:
print @errMsg
return @returnCode
END
Monday, 10 June 2013
usp_Util_InsertPartition: Dynamically add a new partition to a partioned table
--
-- basic assumption:
-- partition range is FOR LEFT
-- partiitn column is of data type in
-- exec [usp_Util_InsertPartition] 'PartitionTestNo', 100
-- exec [usp_Util_InsertPartition] 'PartitionTest', 2000
-- exec [usp_Util_InsertPartition] 'PartitionTest', 0
-- Basic assumption: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition range is left
-- 2) partion parameter has integer type
-- when @usePreviousFileGroup=1, will use the file gruop on the left most partion value and @fileSize and @fileGrowth will be ignored
-- otherwise will append partion number as the file group name and add a new file to the new file group
CREATE PROCEDURE [usp_Util_InsertPartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
,@usePreviousFileGroup tinyint=1
,@fileSize varchar(255)='50MB'
,@fileGrowth varchar(255)='10'
AS
AS
BEGIN
--
-- Is table partioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
--
-- find the partition number for the partion value and other information for table partition
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
,@partitionNumber=rv.boundary_id
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
-- need exact match, assuming left range type
AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.'
return 100
end else
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16))
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
end
--
-- create clone table on same file group, drop it if already exists
-- sample checking and drop table and PK statement
--IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND type in (N'U'))
--DROP TABLE [dbo].[corDistrictPrd]
--IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND name = N'PK_corDistrictPrd')
--ALTER TABLE [dbo].[corDistrictPrd] DROP CONSTRAINT [PK_corDistrictPrd]
-- No clustered index is not needed
-- taking care of columns and primary key
-- ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000), @createCloneTable varchar(8000), @primaryKeyName varchar(255), @dropPrimaryKey varchar(8000), @primayKeyNamePlaceHolder varchar(255), @hasPrimaryKey tinyint, @switchPartition varchar(1024)
select
-- A place holder for primary key which will be replace when the value is known
@primayKeyNamePlaceHolder='$$PK_NAME$$'
,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
+'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13)+'END'
,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
+'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
select
@hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
,@createCloneTable=
'CREATE TABLE ' +@CloneTableName
+ '(' + o.list + ')'
+ ' ON '+@partitionFileGroupName
+ CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
+'ALTER TABLE ' + @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
+ CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
+ ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sys.schemas s inner join
sys.objects so on
s.schema_id=so.schema_id
cross apply
(SELECT
' ['+column_name+'] '
+ data_type
+ case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
)
then 'IDENTITY(' + cast(ident_seed(so.name) as varchar) + ',' + cast(ident_incr(so.name) as varchar) + ')'
else '' end
+ ' '
+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns
where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
left join information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')) j (list)
-- find out if the primary key is clustered
left join sys.indexes i
on so.object_id=i.object_id
-- index_id 1 means clustered
-- constraint name is primary key name which is same as the index name
AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name
where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end
-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end else if @compression=2
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- taking care of clusterd index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fctBranch]') AND name = N'IX_fctBranch')
--DROP INDEX [IX_fctBranch] ON [dbo].[fctBranch] WITH ( ONLINE = OFF )
-- CREATE [ UNIQUE | CLUSTERED | NONCLUSTERED ] INDEX [IX_fctBranch] ON [dbo].[fctBranch]
--(
-- [dimPMFirmid] ASC,
-- [dimPeriodid] ASC
--)
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256), @indexName varchar(256), @indexUnique varchar(8), @hasNonPrimaryKeyClusterdIndex tinyint, @createClusteredIndex varchar(8000), @dropClusteredIndex varchar(8000)
select @indexColums='(', @indexUnique='', @indexName='', @hasNonPrimaryKeyClusterdIndex=0, @dropClusteredIndex='', @createClusteredIndex=''
/*
select
-- whenever we have result from here, we do hve clusterd index
@hasNonPrimaryKeyClusterdIndex=1
-- this will accumulate all columns
,@indexColums=@indexColums+c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
-- they will be overwritten since they have identical values for all columns
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
INNER JOIN sys.index_columns ic
ON (ic.index_id = i.index_id and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.column_id=ic.column_id and c.object_id=ic.object_id
where s.name=@schemaName and t.name=@tableName
order by ic.key_ordinal
*/
-- this is a better way to do the same thing:
select
-- whenever we have result from here, we do hve clusterd index
@hasNonPrimaryKeyClusterdIndex=1
,@indexColums=@indexColums+ic.columnList
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
from sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
CROSS APPLY
(
select
c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
from sys.index_columns ic
INNER JOIN sys.columns c
ON c.column_id=ic.column_id and c.object_id=ic.object_id
where ic.index_id = i.index_id and ic.object_id = t.object_id
order by ic.key_ordinal
FOR XML PATH('')
) ic(columnList)
where s.name=@schemaName and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
+char(13)+'BEGIN'+char(13)
+'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
+char(13)+'END'
,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
-- Note: we can not have transaction here, unfortunately.
-- Msg 226, Level 16, State 6, Line 1
-- ALTER DATABASE statement not allowed within multi-statement transaction.
--begin transaction Txc_usp_Util_TruncatePartition
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
EXEC(@dropCloneTable)
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
EXEC(@createCloneTable)
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@createClusteredIndex)
select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
if (@error<>0) goto ERROR
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
--commit transaction Txc_usp_Util_TruncatePartition
RETURN 0
ERROR:
--rollback transaction
print @errMsg
return @returnCode
END
-- basic assumption:
-- partition range is FOR LEFT
-- partiitn column is of data type in
-- exec [usp_Util_InsertPartition] 'PartitionTestNo', 100
-- exec [usp_Util_InsertPartition] 'PartitionTest', 2000
-- exec [usp_Util_InsertPartition] 'PartitionTest', 0
-- Basic assumption: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition range is left
-- 2) partion parameter has integer type
-- when @usePreviousFileGroup=1, will use the file gruop on the left most partion value and @fileSize and @fileGrowth will be ignored
-- otherwise will append partion number as the file group name and add a new file to the new file group
CREATE PROCEDURE [usp_Util_InsertPartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
,@usePreviousFileGroup tinyint=1
,@fileSize varchar(255)='50MB'
,@fileGrowth varchar(255)='10'
AS
AS
BEGIN
--
-- Is table partioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
--
-- find the partition number for the partion value and other information for table partition
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
,@partitionNumber=rv.boundary_id
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
-- need exact match, assuming left range type
AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.'
return 100
end else
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16))
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
end
--
-- create clone table on same file group, drop it if already exists
-- sample checking and drop table and PK statement
--IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND type in (N'U'))
--DROP TABLE [dbo].[corDistrictPrd]
--IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND name = N'PK_corDistrictPrd')
--ALTER TABLE [dbo].[corDistrictPrd] DROP CONSTRAINT [PK_corDistrictPrd]
-- No clustered index is not needed
-- taking care of columns and primary key
-- ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000), @createCloneTable varchar(8000), @primaryKeyName varchar(255), @dropPrimaryKey varchar(8000), @primayKeyNamePlaceHolder varchar(255), @hasPrimaryKey tinyint, @switchPartition varchar(1024)
select
-- A place holder for primary key which will be replace when the value is known
@primayKeyNamePlaceHolder='$$PK_NAME$$'
,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
+'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13)+'END'
,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
+'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
select
@hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
,@createCloneTable=
'CREATE TABLE ' +@CloneTableName
+ '(' + o.list + ')'
+ ' ON '+@partitionFileGroupName
+ CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
+'ALTER TABLE ' + @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
+ CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
+ ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sys.schemas s inner join
sys.objects so on
s.schema_id=so.schema_id
cross apply
(SELECT
' ['+column_name+'] '
+ data_type
+ case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
)
then 'IDENTITY(' + cast(ident_seed(so.name) as varchar) + ',' + cast(ident_incr(so.name) as varchar) + ')'
else '' end
+ ' '
+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns
where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
left join information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')) j (list)
-- find out if the primary key is clustered
left join sys.indexes i
on so.object_id=i.object_id
-- index_id 1 means clustered
-- constraint name is primary key name which is same as the index name
AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name
where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end
-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end else if @compression=2
begin
set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- taking care of clusterd index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fctBranch]') AND name = N'IX_fctBranch')
--DROP INDEX [IX_fctBranch] ON [dbo].[fctBranch] WITH ( ONLINE = OFF )
-- CREATE [ UNIQUE | CLUSTERED | NONCLUSTERED ] INDEX [IX_fctBranch] ON [dbo].[fctBranch]
--(
-- [dimPMFirmid] ASC,
-- [dimPeriodid] ASC
--)
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256), @indexName varchar(256), @indexUnique varchar(8), @hasNonPrimaryKeyClusterdIndex tinyint, @createClusteredIndex varchar(8000), @dropClusteredIndex varchar(8000)
select @indexColums='(', @indexUnique='', @indexName='', @hasNonPrimaryKeyClusterdIndex=0, @dropClusteredIndex='', @createClusteredIndex=''
/*
select
-- whenever we have result from here, we do hve clusterd index
@hasNonPrimaryKeyClusterdIndex=1
-- this will accumulate all columns
,@indexColums=@indexColums+c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
-- they will be overwritten since they have identical values for all columns
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
INNER JOIN sys.index_columns ic
ON (ic.index_id = i.index_id and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.column_id=ic.column_id and c.object_id=ic.object_id
where s.name=@schemaName and t.name=@tableName
order by ic.key_ordinal
*/
-- this is a better way to do the same thing:
select
-- whenever we have result from here, we do hve clusterd index
@hasNonPrimaryKeyClusterdIndex=1
,@indexColums=@indexColums+ic.columnList
,@indexName=@cloneTableName+'_'+i.name
,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
from sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- looking for clustered index ONLY
and i.index_id=1
-- ignore primary key, which is taken care of above already
and i.is_primary_key=0)
CROSS APPLY
(
select
c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
from sys.index_columns ic
INNER JOIN sys.columns c
ON c.column_id=ic.column_id and c.object_id=ic.object_id
where ic.index_id = i.index_id and ic.object_id = t.object_id
order by ic.key_ordinal
FOR XML PATH('')
) ic(columnList)
where s.name=@schemaName and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
+char(13)+'BEGIN'+char(13)
+'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
+char(13)+'END'
,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
-- Note: we can not have transaction here, unfortunately.
-- Msg 226, Level 16, State 6, Line 1
-- ALTER DATABASE statement not allowed within multi-statement transaction.
--begin transaction Txc_usp_Util_TruncatePartition
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
EXEC(@dropCloneTable)
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
EXEC(@createCloneTable)
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@createClusteredIndex)
select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
if (@error<>0) goto ERROR
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
--commit transaction Txc_usp_Util_TruncatePartition
RETURN 0
ERROR:
--rollback transaction
print @errMsg
return @returnCode
END
Looking inside Partition (how many rows are stroed in which file groups?)
-- reference:
http://davidpeterhansen.com/view-partitions-in-sql-server/
SELECT
s.name as SchemaName
,t.name as TableName
,c.name as PartitionColumn
,CASE boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END as PartionRangeType
,ps.name as PartitionName
,pf.name as PartitionFunction
,p.partition_number AS PartitionNumber
,CASE boundary_value_on_right WHEN 1 THEN '<' ELSE '<=' END as PartionComparisionType
,rv.value as PartitionValue
,fg.name AS FileGroupName
,p.rows AS RowsInPartition
,au.total_pages AS PagesInPartition
,au.total_pages*8/1024.0 AS MBsInPartition
,CONVERT(VARCHAR(6), CONVERT(INT, SUBSTRING (au.first_page, 6, 1) +SUBSTRING (au.first_page, 5, 1)))
+':'
+ CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +SUBSTRING (au.first_page, 3, 1) +SUBSTRING (au.first_page, 2, 1) +SUBSTRING (au.first_page, 1, 1)))
AS PartitionFirstPage
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.index_columns ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
ORDER BY 1,2,7
Subscribe to:
Posts (Atom)