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;

Leave a Reply