Table and Index Partitioning - Microsoft SQL Server 2012 Performance Tuning - page 302

CREATE PARTITION SCHEME ps_OneMillion_LeftRange
AS PARTITION pf_OneMillion_LeftRange
TO ([PRIMARY],[FG_1],[FG_2],[FG_3],[FG_N])

--Verify that the Partition Scheme
--has been created
SELECT
name
,data_space_id
,type
,type_desc
,function_id
FROM sys.partition_schemes
GO

7. The preceding query should give you output similar to that shown in the

following screenshot:

8. Now, let us create our table tbl_SampleRecords on the partition scheme

ps_OneMillion_LeftRange , that we defined, and insert 5 million sample rows:
USE Sample_DB
GO

--Create Sample Table
IF OBJECT_ID('tbl_SampleRecords') IS NOT NULL
DROP TABLE tbl_SampleRecords

CREATE TABLE tbl_SampleRecords
(
ID INT
,SomeData sysname
,CONSTRAINT pk_tbl_SampleRecords_id PRIMARY KEY CLUSTERED(ID)
) ON ps_OneMillion_LeftRange (ID)
GO

--Inserting Sample Records
INSERT INTO tbl_SampleRecords
SELECT TOP 5000000
ID = ROW_NUMBER() OVER(ORDER BY C1.name)