sql2005分区表示例
SQL2005分区表可以在一定程度上解决海量数据的性能问题，比如可以规避高访问量数据区段的io竞争，可以缩小你查询数据范围的索引大小。 msdn参考http://msdn.microsoft.com/zh-cn/library/ms345146.aspx 一个完整的脚本示例
--drop database dbPartitionTest--测试数据库create database dbPartitionTestgouse dbPartitionTestgo--增加分组alter database dbPartitionTest ADD FILEGROUP P200801alter database dbPartitionTest ADD FILEGROUP P200802alter database dbPartitionTest ADD FILEGROUP P200803go--分区函数CREATE PARTITION FUNCTION part_Year(datetime) AS RANGE LEFT FOR VALUES ( '20080131 23:59:59.997','20080229 23:59:59.997','20080331 23:59:59.997') go--增加文件组ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200801',FILENAME = N'c:\tb_P200801.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200801ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200802',FILENAME = N'c:\tb_P200802.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200802ALTER DATABASE dbPartitionTest ADD FILE (NAME = N'P200803',FILENAME = N'c:\tb_P200803.ndf',SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200803go--分区架构CREATE PARTITION SCHEME part_YearSchemeAS PARTITION part_Year TO (P200801,P200802,P200803,)goCREATE TABLE .t_part(name varchar(100) default newid(),date datetime NOT NULL)ON part_YearScheme (date)go--添加测试数据,每天1条declare @date datetimeset @date='2007-12-31'while @date<='2008-04-01'begin insert into t_part(date)values(@date) set @date=@date+1endgo--查询数据分布在哪些分区select $partition.part_Year(date) as 分区编号,* from t_partorder by date--查询数据库文件gosp_helpfile
页:
[1]