Home     Documentation     Console Utility Usage Scenarios     Sample 4

Sample 4

In this example, we use the dbo.Orders and dbo.OrderDetails partitioned tables, dbo.OrderDetails has foreign key that refers to dbo.Orders. New monthly data must be loaded into a new partition for both tables, and the earliest month data must be transferred to the dbo.Orders_Archive and dbo.OrderDetails_Archive archive tables.

Given batch file:

  • creates staging tables for loading new data;
  • loads new data to staging tables;
  • switches data from staging tables to main tables;
  • switches partitions from main tables to archive tables;
  • creates new partitions in the dbo.Orders and dbo.OrderDetails tables;
  • enables and disables foreign keys on main and archive tables, updates CHECK-constraints according to range changes.

You can download the archive that includes the code of the scenario, and script for recreating the situation from here.

To create the SalesDB test database, invoke the script presented below. The AdventureWorks sample database is used for loading test data.

SalesDb Creation Script:

USE master
CREATE DATABASE [SalesDb] ON PRIMARY (NAME = N'SalesDb', FILENAME = N'C:\SqlData\Sql2008\SalesDb.mdf',
	 SIZE = 69824KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB),
FILEGROUP [fg01] (NAME = N'file01', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file01.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB),
FILEGROUP [fg02] (NAME = N'file02', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file02.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
FILEGROUP [fg03] (NAME = N'file03', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file03.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
FILEGROUP [fg04] (NAME = N'file04', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file04.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
FILEGROUP [fg05] (NAME = N'file05', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file05.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
FILEGROUP [fg06] (NAME = N'file06', FILENAME = N'C:\SqlData\Sql2008\SalesDb_file06.ndf',
	SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (NAME = N'SalesDb_log', FILENAME = N'C:\SqlData\Sql2008\SalesDb_log.ldf',
	SIZE = 526336KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
GO
USE [SalesDb]
CREATE PARTITION FUNCTION ActivePartitionFunc(datetime)
AS 
RANGE LEFT FOR VALUES ('20091031 23:59:59.997',      -- Oct 2009
                       '20091130 23:59:59.997',      -- Nov 2009
                       '20091231 23:59:59.997',      -- Dec 2009
                       '20100131 23:59:59.997')      -- Jan 2010
GO

CREATE PARTITION SCHEME ActivePartitionScheme
AS
PARTITION ActivePartitionFunc TO
([FG01], [FG02], [FG03], [FG04], [PRIMARY])
GO

CREATE TABLE [dbo].[Orders]  
(
      [OrderID] [int]            NOT NULL,
      [EmployeeID] [int]         NULL,
      [VendorID] [int]           NULL,
      [TaxAmt] [money]           NULL,
      [Freight] [money]          NULL,
      [SubTotal] [money]         NULL,
      [Status] [tinyint]         NOT NULL,
      [RevisionNumber] [tinyint] NULL,
      [ModifiedDate] [datetime]  NULL,
      [ShipMethodID]  [tinyint]  NULL,
      [ShipDate] [datetime]      NOT NULL, 
      [OrderDate] [datetime]     NOT NULL, 
      [TotalDue] [money]         NULL
) ON ActivePartitionScheme(OrderDate)
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT PK_Orders
      PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO

--Loading test data to the [dbo].[Orders] table
DECLARE @min datetime, @max datetime, @factor float
SELECT @min = MIN(OrderDate), @max = MAX(OrderDate) FROM AdventureWorks.Purchasing.PurchaseOrderHeader
SELECT @factor = CONVERT(float, DATEDIFF(dd, @min, @max)) / 120

INSERT dbo.[Orders]
	SELECT o.[PurchaseOrderID]
                  , o.[EmployeeID]
                  , o.[VendorID]
                  , o.[TaxAmt]
                  , o.[Freight]
                  , o.[SubTotal]
                  , o.[Status]
                  , o.[RevisionNumber]
                  , DATEADD(dd, CONVERT(float, DATEDIFF(dd, @min, o.[ModifiedDate]))/@factor, '20091001')
                  , o.[ShipMethodID]
                  , DATEADD(dd, CONVERT(float, DATEDIFF(dd, @min, o.[ModifiedDate]))/@factor, '20091001')
                  , DATEADD(dd, CONVERT(float, DATEDIFF(dd, @min, o.[OrderDate]))/@factor, '20091001')
                  , o.[TotalDue] 
      FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o

--------
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT CK_Orders_OrderDate
	CHECK ([OrderDate] >= '20091001'
	AND    [OrderDate] <  '20100201')
GO

--------
CREATE TABLE [dbo].[OrderDetails](
      [OrderID] [int]           NOT NULL,
      [LineNumber] [smallint]   NOT NULL,
      [ProductID] [int]         NULL,
      [UnitPrice] [money]       NULL,
      [OrderQty] [smallint]     NULL,
      [ReceivedQty] [float]     NULL,
      [RejectedQty] [float]     NULL,
      [OrderDate] [datetime]    NOT NULL,
      [DueDate] [datetime]      NULL,
      [ModifiedDate] [datetime] NOT NULL
            CONSTRAINT [DF_OrderDetails_ModifiedDate]
               DEFAULT (getdate()),
      [LineTotal]   AS (([UnitPrice]*[OrderQty])),
      [StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON ActivePartitionScheme(OrderDate)
GO
ALTER TABLE [dbo].[OrderDetails] ADD CONSTRAINT PK_OrderDetails
      PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO
ALTER TABLE [dbo].[OrderDetails] ADD CONSTRAINT CK_OrderDetails_OrderDate
	CHECK ([OrderDate] >= '20091001'
	AND    [OrderDate] <  '20100201')
GO

ALTER TABLE [dbo].[OrderDetails] ADD CONSTRAINT FK_OrderDetails
	FOREIGN KEY (OrderDate, OrderID) REFERENCES [dbo].[Orders] (OrderDate, OrderID)
GO

--Loading test data to the [dbo].[OrderDetails] table
INSERT dbo.[OrderDetails]
      SELECT      od.PurchaseOrderID
                  , od.PurchaseOrderDetailID
                  , od.ProductID
                  , od.UnitPrice
                  , od.OrderQty
                  , od.ReceivedQty
                  , od.RejectedQty
                  , o.OrderDate
                  , o.ShipDate
                  , o.ModifiedDate
      FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
            JOIN SalesDb.dbo.Orders AS o
                        ON o.OrderID = od.PurchaseOrderID
GO

--------
CREATE PARTITION FUNCTION ArchivePartitionFunc(datetime)
AS
RANGE LEFT FOR VALUES ('20090930 23:59:59.997')      -- Before Oct 2009
GO

CREATE PARTITION SCHEME ArchivePartitionScheme
AS
PARTITION ArchivePartitionFunc TO
( [PRIMARY], [PRIMARY] )
GO
CREATE TABLE [dbo].[Orders_Archive]
(
      [OrderID] [int]			 NOT NULL,
      [EmployeeID] [int]         NULL,
      [VendorID] [int]           NULL,
      [TaxAmt] [money]           NULL,
      [Freight] [money]          NULL,
      [SubTotal] [money]         NULL,
      [Status] [tinyint]         NOT NULL,
      [RevisionNumber] [tinyint] NULL,
      [ModifiedDate] [datetime]  NULL,
      [ShipMethodID]  [tinyint]  NULL,
      [ShipDate] [datetime]      NOT NULL,
      [OrderDate] [datetime]     NOT NULL,
      [TotalDue] [money]         NULL
) ON ArchivePartitionScheme(OrderDate)
GO
ALTER TABLE [dbo].[Orders_Archive] ADD CONSTRAINT PK_Orders_Archive
      PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE [dbo].[Orders_Archive] ADD CONSTRAINT CK_Orders_Archive_OrderDate
	CHECK ([OrderDate] < '20091001')
GO

CREATE TABLE [dbo].[OrderDetails_Archive](
      [OrderID] [int]           NOT NULL,
      [LineNumber] [smallint]   NOT NULL,
      [ProductID] [int]         NULL,
      [UnitPrice] [money]       NULL,
      [OrderQty] [smallint]     NULL,
      [ReceivedQty] [float]     NULL,
      [RejectedQty] [float]     NULL,
      [OrderDate] [datetime]    NOT NULL,
      [DueDate] [datetime]      NULL,
      [ModifiedDate] [datetime] NOT NULL,
      [LineTotal]   AS (([UnitPrice]*[OrderQty])),
      [StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON ArchivePartitionScheme(OrderDate)
GO
ALTER TABLE [dbo].[OrderDetails_Archive] ADD CONSTRAINT PK_OrderDetails_Archive
      PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO
ALTER TABLE [dbo].[OrderDetails_Archive] ADD CONSTRAINT CK_OrderDetails_Archive_OrderDate
	CHECK ([OrderDate] < '20091001')
GO

ALTER TABLE [dbo].[OrderDetails_Archive] ADD CONSTRAINT FK_OrderDetails_Archive
	FOREIGN KEY (OrderDate, OrderID) REFERENCES [dbo].[Orders_Archive] (OrderDate, OrderID)
GO
        

Run the batch file below for performing needed actions.

Command file SalesDb_03.cmd:

@PROMPT $G

@rem Connection parameters
@SET SRV=dev0002\sql2008
@SET DB=SalesDb
@rem Operation parameters
@SET NEW_FG=fg05
@SET NEW_BOUND=20100228 23:59:59.997
@SET LOWER_CHECK_BOUND=20091101
@SET UPPER_CHECK_BOUND=20100301
@SET NEW_ARCHIVE_BOUND=20091031 23:59:59.997

@SET PATH=C:\Program Files\DonWellSoft\SQL Partition Manager\;%PATH%
@SET PATH=C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;%PATH%

@rem Creating staging tables for loading new data
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders:5 /createStagingTable:dbo.Orders_new /Overwrite
@if errorlevel 1 goto error
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.OrderDetails:5 /createStagingTable:dbo.OrderDetails_new /Overwrite
@if errorlevel 1 goto error

@rem Loading new data into future partitions
bcp %DB%.dbo.Orders_new in .\Data\Orders_new.dat -S %SRV% -T -c
@if errorlevel 1 goto error
bcp %DB%.dbo.OrderDetails_new in .\Data\OrderDetails_new.dat -S %SRV% -T -c
@if errorlevel 1 goto error

@rem Adding check-constraints to staging tables. Otherwise, it will be impossible to switch data
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.Orders_new WITH CHECK CHECK CONSTRAINT ALL
	ALTER TABLE dbo.OrderDetails_new WITH CHECK CHECK CONSTRAINT ALL" 
@if errorlevel 1 goto error

@rem Disabling foreign keys on main tables that impede switching partitions
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails NOCHECK CONSTRAINT FK_OrderDetails" 
@if errorlevel 1 goto error

@rem Updating CHECK-constraints on archive tables
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.Orders_Archive DROP CONSTRAINT CK_Orders_Archive_OrderDate
	ALTER TABLE dbo.Orders_Archive ADD CONSTRAINT CK_Orders_Archive_OrderDate
	CHECK ([OrderDate] < '%LOWER_CHECK_BOUND%')"
@if errorlevel 1 goto error
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails_Archive DROP CONSTRAINT CK_OrderDetails_Archive_OrderDate
	ALTER TABLE dbo.OrderDetails_Archive ADD CONSTRAINT CK_OrderDetails_Archive_OrderDate
	CHECK ([OrderDate] < '%LOWER_CHECK_BOUND%')"
@if errorlevel 1 goto error

@rem Disabling foreign keys on archive tables that impede switching partitions
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails_Archive NOCHECK CONSTRAINT FK_OrderDetails_Archive" 
@if errorlevel 1 goto error

@rem Split the last partition of archive tables for switching data from main tables to it
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders_Archive /Split:"%NEW_ARCHIVE_BOUND%" /NextFg:FG01
@if errorlevel 1 goto error

@rem Switching the first partitions containing old data to archive tables
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders:1 /SwitchOff:dbo.Orders_Archive:2
@if errorlevel 1 goto error
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.OrderDetails:1 /SwitchOff:dbo.OrderDetails_Archive:2
@if errorlevel 1 goto error

@rem Delete boundary for archive tables to merge switched data with previous data
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders_Archive /Merge:1
@if errorlevel 1 goto error

@rem Enable foreign keys on archive tables that have been previously disabled
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails_Archive CHECK CONSTRAINT FK_OrderDetails_Archive"
@if errorlevel 1 goto error

@rem Updating CHECK-constraints on main tables
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.Orders DROP CONSTRAINT CK_Orders_OrderDate
	ALTER TABLE dbo.Orders ADD CONSTRAINT CK_Orders_OrderDate CHECK ([OrderDate] >= '%LOWER_CHECK_BOUND%' AND
	[OrderDate] < '%UPPER_CHECK_BOUND%')"
@if errorlevel 1 goto error
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails DROP CONSTRAINT CK_OrderDetails_OrderDate
	ALTER TABLE dbo.OrderDetails ADD CONSTRAINT CK_OrderDetails_OrderDate
	CHECK ([OrderDate] >= '%LOWER_CHECK_BOUND%' AND [OrderDate] < '%UPPER_CHECK_BOUND%')"
@if errorlevel 1 goto error

@rem Switch new partition in
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders:5 /SwitchIn:dbo.Orders_new
@if errorlevel 1 goto error
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.OrderDetails:5 /SwitchIn:dbo.OrderDetails_new
@if errorlevel 1 goto error

@rem Delete the first boundary point
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders /Merge:1
@if errorlevel 1 goto error

@rem Split the last partition
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders /Split:"%NEW_BOUND%" /NextFg:%NEW_FG%
@if errorlevel 1 goto error

@rem Enable foreign keys on main tables that have been previously disabled
sqlcmd -b -S %SRV% -E -d %DB% -Q "ALTER TABLE dbo.OrderDetails CHECK CONSTRAINT FK_OrderDetails"
@if errorlevel 1 goto error

@echo ############################################
@echo Scenario was successfully completed!
@goto exit

:error
@echo ############################################
@echo Scenario failed!

:exit
@echo ############################################
@pause