sqlserver 分区表

 

CREATE PARTITION FUNCTION OneYearDateRangePFN(datetime)
 AS RANGE LEFT FOR VALUES
(
'20070131 23:59:59.997',
'20070228 23:59:59.997',
'20070331 23:59:59.997',
'20070430 23:59:59.997',
'20070531 23:59:59.997',
'20070630 23:59:59.997',
'20070731 23:59:59.997',
'20070831 23:59:59.997',
'20070930 23:59:59.997',
'20071031 23:59:59.997',
'20071130 23:59:59.997',
'20071231 23:59:59.997'
) ;

 

ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200701];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200702];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200703];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200704];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200705];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200706];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200707];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200708];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200709];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200710];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200711];
ALTER DATABASE newcsdnblog ADD FILEGROUP [ViewCount200712];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200701',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200701.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200701];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200702',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200702.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200702];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200703',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200703.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200703];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200704',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200704.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200704];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200705',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200705.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200705];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200706',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200706.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200706];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200707',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200707.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200707];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200708',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200708.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200708];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200709',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200709.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200709];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200710',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200710.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200710];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200711',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200711.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200711];

ALTER DATABASE newcsdnblog
ADD FILE
(NAME = N'ViewCount200712',FILENAME = N'd:\sqlserverdata\SmallTableViewCount200712.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [ViewCount200712];
;

CREATE PARTITION SCHEME OneYearDateRangePScheme
AS
PARTITION OneYearDateRangePFN
ALL TO ([PRIMARY])
;

CREATE TABLE [dbo].[Blog_ViewCount2] 
(
[EntryID] [int] NOT NULL,
[BlogID] [int] NOT NULL,
[ViewDate] [datetime] NOT NULL
)
ON OneYearDateRangePScheme (ViewDate)
GO
;

select $partition.partition_fn_town_year_new(partition_field), COUNT(*) from student_score1 a
group by $partition.partition_fn_town_year_new(partition_field)