Home     Documentation     Console Utility Usage Scenarios     Sample 3

Sample 3

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 deleted.

Given batch file:

  • creates staging tables for loading new data;
  • loads new data to staging tables;
  • disables a foreign key between tables while switching partitions, and than enables it;
  • deletes the earliest month data;
  • updates CHECK-constraints defined for the dbo.Orders and dbo.OrderDetails tables according to the OrderDate range changes;
  • switches data from staging tables to partitioned tables;
  • creates new partitions in the dbo.Orders and dbo.OrderDetails tables.

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
        

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 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 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 Deleting the first partitions with old data
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders:1 /SwitchOff
@if errorlevel 1 goto error
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.OrderDetails:1 /SwitchOff
@if errorlevel 1 goto error

@rem Updating CHECK-constraints on partitioned 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 partitions 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 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