Correct example: Updated existing code

1)

USE master

GO

CREATE DATABASE MyPartitionPracticeDB

ON PRIMARY

( NAME = db_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDB.mdf',

SIZE = 50MB),

FILEGROUP FG1

( NAME = FG1_1_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB),

( NAME = FG1_2_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB),

( NAME = FG1_3_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB),

( NAME = FG1_4_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB),

( NAME = FG1_5_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB),

( NAME = FG1_6_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',

SIZE = 2MB)

LOG ON

( NAME = db_log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBlog.ndf',

SIZE = 2MB,

FILEGROWTH = 10% );

GO

USE MyPartitionPracticeDB

GO

2)

--It will create 6 partition... 5 for mentioned range and one for left out values

CREATE PARTITION FUNCTION partFunc (int) AS

RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);

SELECT * FROM sys.partition_functions

3)

CREATE PARTITION SCHEME MyPartitionScheme AS

PARTITION Partfunc TO

([FG1], [FG1], [FG1], [FG1], [FG1], [FG1])

GO

SELECT * FROM SYS.PARTITION_RANGE_VALUES

4)

CREATE TABLE MyPartionedTable

(

ID int PRIMARY KEY,

Name VARCHAR(50)

)

ON MyPartitionScheme(ID)