TSQL: Partitioned table exercise for 70-462

Here’s some TSQL for the WingTipToys2012 table partitioning exercise in the 70-462 training materials.

CREATE DATABASE WingTipToys2012

USE WingTipToys2012;
GO

CREATE PARTITION FUNCTION WTPartFunction(INTEGER)
AS RANGE LEFT FOR VALUES (30, 60)

CREATE PARTITION SCHEME WTPartScheme
AS PARTITION WTPartFunction
TO (fgOne, fgTwo, fgThree);

CREATE TABLE toys
(
	column1 INTEGER NOT NULL PRIMARY KEY CLUSTERED,
	column2 CHAR(30) NOT NULL
) ON WTPartScheme(column1);

INSERT INTO dbo.toys
VALUES (1, 'Car'), (2, 'Truck'), (3, 'Bike'),
	(4, 'Doll'), (5, 'Football'), (6, 'Transformer'),
	(7, 'Action Man'), (8, 'Barbie'), (9, 'Pokemon'),
	(10, 'Nintendo'), (25, 'Hula-Hoop'), (31, 'Playing Cards');

-- In execution plan partition count should = 1 for each
SELECT * FROM dbo.toys WHERE column1 = 1;
SELECT * FROM dbo.toys WHERE column1 = 25;
SELECT * FROM dbo.toys WHERE column1 = 31;
-- Partition count should now be = 3
SELECT * FROM dbo.toys WHERE column1 >= 1;