Home     Documentation     Console Utility Usage Scenarios     Sample 2

Sample 2

In this example, we use the dbo.Orders table partitioned by the OrderDate column. Check-boundaries are defined for a table to limit table data to a given range. New monthly data must be loaded into the new partition and the earliest month data must be deleted.

Given batch file:

  • creates a staging table for loading new data;
  • loads new data to a staging table;
  • deletes the earliest month data;
  • updates CHECK-constraints defined for the dbo.Orders table according to the OrderDate range changes;
  • switches data from a staging table to a partitioned table;
  • creates a new partition in the dbo.Orders table.

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
        

Run the batch file below for performing needed actions.

Command file SalesDb_02.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 table 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

@rem Loading new data into future partition
bcp %DB%.dbo.Orders_new in .\Data\Orders_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"
@if errorlevel 1 GOTO error

@rem Deleting the first partition with old data
spmcmd.exe /server:%SRV% /db:%DB% /winauth /table:dbo.Orders:1 /SwitchOff
@if errorlevel 1 GOTO error

@rem Updating CHECK-constraint on the dbo.Orders table
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

@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

@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

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

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

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