Hi Friends,
This post is important if at all if you worked on Partitioning especially while performing Switch in.
I have a scenario where I need to partition an existing table which has data from 2007.
Below are the high level steps what I did
1) Adding filegroups
2) Adding files to each of the filegroups
3) create temporary staging tables on the respective filegroups
4)Load the data from main table in to staging tables
5) truncate main table
6) create partition function and partition scheme
7) create check constraints on temporary staging tables
8) Disable NON Clustered index and Foreign keys if there are any on the source table
9) Switch in Data from Staging tables to main table
while performing the final step I missed to create check constraint on one of the temporary staging tables and I didn't expect that will cause havoc
If you want you can play with the below demo (Thanks to Kendra Little)
CREATE DATABASE [PartitionThis]
GO
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );
ALTER DATABASE [PartitionThis]
MODIFY FILE ( NAME = N'PartitionThis_log', SIZE = 128MB , FILEGROWTH = 128MB );
GO
USE PartitionThis;
GO
;WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1),
Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B),
Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B),
Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B),
Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B),
Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B),
tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 )
SELECT N
INTO ph.tally
FROM tally
WHERE N <= 100000;
GO
--Create the partition function: dailyPF
DECLARE @StartDay DATE=DATEADD(dd,-3,CAST(SYSDATETIME() AS DATE));
CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))
AS RANGE RIGHT FOR VALUES
(@StartDay, DATEADD(dd,1,@StartDay), DATEADD(dd,2,@StartDay),
DATEADD(dd,3,@StartDay), DATEADD(dd,4,@StartDay) );
GO
--Add filegroups.
--Number of filegroups = 1 + Number of boundary points
--defined in partition function
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG4
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG5
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG6
GO
--Add files to the filegroups
DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);
SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)
FROM sys.database_files WHERE name='PartitionThis';
WHILE @i <= 6
BEGIN
SET @sql=N'ALTER DATABASE PartitionThis ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',
filename=''' + @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',
size=128MB, filegrowth=256MB) TO FILEGROUP DailyFG'+CAST(@i AS NCHAR(1))
--show the command we're running
RAISERROR (@sql,0,0)
--run it
EXEC sp_executesql @sql;
SET @i+=1;
END
GO
--Create the partition scheme: dailyPS
CREATE PARTITION SCHEME DailyPS
AS PARTITION DailyPF
TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6);
This is how the Data looks after we create partitioning with above Scripts.
Now I created temporary tables like below so that I can dump the data from main table to below staging tables
CREATE TABLE OrdersDaily_2 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL)
on [DailyFG2]
CREATE TABLE OrdersDaily_3 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL)
on [DailyFG3]
CREATE TABLE OrdersDaily_4 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL)
on [DailyFG4]
CREATE TABLE OrdersDaily_5 (
OrderDate DATETIME2(0) NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderName nvarchar(256) NOT NULL)
on [DailyFG5]
Comments