Partitioning Sample

-- SQL Server table partitioning
-- SQL Server partitioning
USE Copy1OfAdventureWorks
GO
 
ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryAlpha
ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryBeta
ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryDelta
GO
 
ALTER DATABASE Copy1OfAdventureWorks
ADD FILE
( NAME = DataFile1,
FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile1.ndf',
SIZE = 10MB,
MAXSIZE = 1000MB,
FILEGROWTH = 10MB)
TO FILEGROUP SecondaryAlpha
 
ALTER DATABASE Copy1OfAdventureWorks
ADD FILE
( NAME = DataFile2,
FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile2.ndf',
SIZE = 10MB,
MAXSIZE = 1000MB,
FILEGROWTH = 10MB)
TO FILEGROUP SecondaryBeta
 
ALTER DATABASE Copy1OfAdventureWorks
ADD FILE
( NAME = DataFile3,
FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile3.ndf',
SIZE = 10MB,
MAXSIZE = 1000MB,
FILEGROWTH = 10MB)
TO FILEGROUP SecondaryDelta
GO
 
CREATE PARTITION FUNCTION fnStorePartition (int)
AS RANGE RIGHT FOR VALUES (100,300)
GO
 
CREATE PARTITION SCHEME schStorePartition AS
PARTITION fnStorePartition TO ( 'SecondaryAlpha','SecondaryBeta', 'SecondaryDelta')
GO
 
CREATE TABLE Sales.StorePartitioned (
  StoreID      INT    NOT NULL    PRIMARY KEY,
  [Name]       NVARCHAR(50),
  ModifiedDate DATETIME)
GO
 
INSERT INTO Sales.StorePartitioned
SELECT   CustomerID,
         [Name],
         ModifiedDate
FROM     Sales.Store
ORDER BY CustomerID
GO
 
SELECT StoreID,
       [Name],
       ModifiedDate,
       PARTITION =$PARTITION.fnStorePartition(StoreID)
FROM   Sales.StorePartitioned

GO

This is the partial result set:

StoreID	Name	ModifiedDate	PARTITION
1	A Bike Store	10/13/04 11:15	1
2	Progressive Sports	10/13/04 11:15	1
3	Advanced Bike Components	10/13/04 11:15	1
4	Modular Cycle Systems	10/13/04 11:15	1
5	Metropolitan Sports Supply	10/13/04 11:15	1
6	Aerobic Exercise Company	10/13/04 11:15	1