Partager via

TechNet Roadshow Example code for Microsoft SQL Server 2005 Table partitioning

  • Article

For those who want to give this a try, this is the code I've been using on the 2K5 H1 roadshow for my SQL 2k5 Table Partitioning demo.

USE AdventureWorks
GO

ALTER DATABASE AdventureWorks ADD FILEGROUP FG1
ALTER DATABASE AdventureWorks ADD FILEGROUP FG2
GO

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = dat1,
  FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dat1.ndf',
  SIZE = 1MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 1MB)
TO FILEGROUP FG1

ALTER DATABASE AdventureWorks
ADD FILE
( NAME = dat2,
  FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dat2.ndf',
  SIZE = 1MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 1MB)
TO FILEGROUP FG2
GO

--Step 1
USE AdventureWorks
GO
--Create the partition function
CREATE PARTITION FUNCTION StorePF (int)
AS RANGE RIGHT FOR VALUES (350)

--Step 2
--Create the partition scheme
CREATE PARTITION SCHEME StorePS AS
PARTITION StorePF TO (FG1, FG2)
GO

--Step 3
--Create the partitioned table
CREATE TABLE Sales.PartitionedStore(
 StoreID int NOT NULL PRIMARY KEY,
 Name nvarchar(50))
ON StorePS (StoreID)

--Add data into the partitioned table
--from the existing Store table
INSERT INTO Sales.PartitionedStore SELECT CustomerID, Name FROM Sales.store
GO

--Step 4
--Retrieve your partition information
--Displays each row in the table and the partition it is stored on.
SELECT StoreID, Name, $partition.StorePF(StoreID)
FROM Sales.PartitionedStore

--Step 5
--You can also display partition information for a row.
--In this case for the row with storeID of 349.
SELECT $partition.StorePF (349)

--In this case for the row with storeID of 350.
SELECT $partition.StorePF (350)
GO