Thursday, October 12, 2017

Playing with Partitioned Tables

I wanted to test drive my installed SQL 2012 Developer instance so I decided to experiment with a basic table partition test.  Below is the code I have used, you can find more of an explaination in the following Microsoft article: http://technet.microsoft.com/en-us/library/ms188730.aspx

Here is the code I used on my development instance:

--Make File Groups


USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [test00]
GO
ALTER DATABASE [test] ADD FILEGROUP [test01]
GO
ALTER DATABASE [test] ADD FILEGROUP [test02]
GO
ALTER DATABASE [test] ADD FILEGROUP [test03]
GO
ALTER DATABASE [test] ADD FILEGROUP [test04]
GO


--Add files for the file groups


USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test00', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test00.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test00]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test01', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test01.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test01]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test02', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test02.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test02]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test03', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test03.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test03]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test04', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test04.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [test04]
GO


--Partition function
USE [test]
GO
CREATE PARTITION FUNCTION TestRange(int)
AS RANGE LEFT
FOR VALUES (1,500000,1000000,1500000)


--Create Partition Scheme


CREATE PARTITION SCHEME TestScheme00
AS
PARTITION TestRange
TO ([test00],[test01],[test02],[test03],[test04])


-- Make test table

USE [test]
GO


CREATE TABLE dbo.TestTable
    (
    TestId int NOT NULL IDENTITY (1, 1),
    FirstName varchar(50) NULL,
    LastName varchar(50) NULL,
    InsertDate datetime NULL
    )  ON TestScheme00 (TestId)
GO


--Insert some test data
USE [test]
GO

DECLARE @count INT;
SET @count = 1;


WHILE @count < 100000

BEGIN

 INSERT INTO dbo.TestTable VALUES ('test','test',GetDate());
 SET @count = @count + 1;

END

--Get row counts per partition number

SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
    ON  p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL