DanBrother的部落格

Partitioned Table and Index Creation Example (SQL Server 2005 and up)

USE [testdb]
GO

ALTER DATABASE testdb ADD FILEGROUP [YEAR_2011]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2012]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2013]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2014]

ALTER DATABASE testdb
ADD FILE
(NAME = N'YEAR_2011',
FILENAME = N'C:\testdb\data\YEAR_2011.ndf',
SIZE = 1GB,
FILEGROWTH = 100MB
)
TO FILEGROUP [YEAR_2011]

ALTER DATABASE testdb
ADD FILE
(NAME = N'YEAR_2012',
FILENAME = N'C:\testdb\data\YEAR_2012.ndf',
SIZE = 1GB,
FILEGROWTH = 100MB
)
TO FILEGROUP [YEAR_2012]

ALTER DATABASE testdb
ADD FILE
(NAME = N'YEAR_2013',
FILENAME = N'C:\testdb\data\YEAR_2013.ndf',
SIZE = 1GB,
FILEGROWTH = 100MB
)
TO FILEGROUP [YEAR_2013]

ALTER DATABASE testdb
ADD FILE
(NAME = N'YEAR_2014',
FILENAME = N'C:\testdb\data\YEAR_2014.ndf',
SIZE = 1GB,
FILEGROWTH = 100MB
)
TO FILEGROUP [YEAR_2014]

CREATE PARTITION FUNCTION Yearlogtime_RangePFN(VARCHAR(14))
AS
RANGE LEFT FOR VALUES (
'20111231235959', -- Year 2011
'20121231235959', -- Year 2012
'20131231235959', -- Year 2013
'20141231235959' -- Year 2014
)
GO

CREATE PARTITION SCHEME [Yearlogtime_RangePScheme]
AS
PARTITION Yearlogtime_RangePFN TO
([YEAR_2011],
[YEAR_2012],
[YEAR_2013],
[YEAR_2014],
[PRIMARY]
)
GO

CREATE TABLE [dbo].[archive_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[year_stamp] [varchar](12),
[year] [int],
[logtime] [varchar](14)
) ON Yearlogtime_RangePScheme(logtime)

GO

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2011-01',2011,'20110101103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2011-02',2011,'20111231103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2012-01',2012,'20120101103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2012-02',2012,'20121231103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2013-01',2013,'20130101103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2013-02',2013,'20131231103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2014-01',2014,'20140101103000');

insert into [dbo].[archive_log](year_stamp,year,logtime)
values('Year-2014-02',2014,'20141231103000');

create nonclustered index IX_ARCHIVE_LOG ON archive_log(logtime)
on Yearlogtime_RangePScheme(logtime);

 [REFERENCE]
http://technet.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx#sql2k5parti_topic10