SQL Server 2008 创建分区表

最新推荐文章于 2024-11-13 16:30:47 发布

转载最新推荐文章于 2024-11-13 16:30:47 发布 · 833 阅读

数据库专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了如何使用SQL数据库进行文件组、次文件、分区函数和分区方案的创建，以及通过插入十万条数据来优化数据库性能。通过创建不同时间范围的分区，实现了数据的有效管理和快速查询。

--- 创建文件组

alter database test add filegroup Before2013
alter database test add filegroup T2013
alter database test add filegroup T2014
alter database test add filegroup After2014

--- 创建次文件

alter database test add file (Name='Before2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\Before2013.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup Before2013
alter database test add file (Name='T2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\T2013.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup T2013
alter database test add file (Name='T2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\T2014.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup T2014
alter database test add file (Name='After2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\After2014.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup After2014

--- 创建分区函数

create partition function RangeTime(datetime)
as range left for values('2012-12-31','2013-12-31','2014-12-31')

--- 创建分区方案

create partition scheme RangeScheme_CreateTime
as partition RangeTime
to (Before2013,T2013,T2014,After2014)

--- 创建表
create table Shop
(
ID varchar(50),
ShopName varchar(50),
CreateTime datetime
) on RangeScheme_CreateTime(CreateTime)

--- 插入十万条数据

declare @i int
set @i =1
while @i<90000
begin
insert into Shop values(NEWID(),'love热卖'+CAST(RAND()*1000 as varchar(20)),DATEADD(DAY,CAST(RAND()*2000 as int),'2012-1-1'))
set @i=@i+1
end

--- 统计每个分区的记录数
select $partition.RangeTime(CreateTime) as number ,COUNT(*) as rcount
from Shop group by $partition.RangeTime(CreateTime)