sql server怎么建立分区表

打开MsSQL2008,找到作业该项,如果打不开或者SQL Server代理是未启动状态,请先在windows服务中启动SQL Server代理(参考图片),

2

右击MsSQL2008对象资源管理器中的作业,选择新建作业,输入该作业你想用的名称,类别不用管,说明里面是输入一些该作业完成的功能,可不写,请务必勾选已启用复选框.

3

点击新建作业窗体左侧的步骤项,点击右侧区域下方的新建按钮,输入步骤名称,类型请选择Transact-SQL脚本(T-SQL),运行身份默认,数据库请选择要进行分区的数据库,请不要选择master默认的,命令文本框中输入如下代码:

/*--------------------创建数据库的文件组和物理文件------------------------*/

declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath

varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)

set @tableName='要分区的数据库名称'

set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间

set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)

set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')

set @fileGroupName=N'G'+@newNameStr

set @ndfName=N'F'+@newNameStr+''

set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'.ndf'

set @partFunName=N'pf_Time'

set @schemeName=N'ps_Time'

--创建文件组

if exists(select * from sys.filegroups where name=@fileGroupName)

begin

print '文件组存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')

print '新增文件组'

if exists(select * from sys.partition_schemes where name =@schemeName)

begin

exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')

print '修改分区方案'

end

if exists(select * from sys.partition_range_values where function_id=(select function_id from

sys.partition_functions where name =@partFunName) and value=@oldDay)

begin

exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')

print '修改分区函数'

end

end

--创建NDF文件

if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))

begin

print 'ndf文件存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',

FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')

print '新创建ndf文件'

end

/*--------------------以上创建数据库的文件组和物理文件------------------------*/

--分区函数

if exists(select * from sys.partition_functions where name =@partFunName)

begin

print '此处修改需要在修改分区函数之前执行'

end

else

begin

exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay

+''')')

print '新创建分区函数'

end

--分区方案

if exists(select * from sys.partition_schemes where name =@schemeName)

begin

print '此处修改需要在修改分区方案之前执行'

end

else

begin

exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO

(''PRIMARY'','''+@fileGroupName+''')')

print '新创建分区方案'

end

print '---------------以下是变量定义值显示---------------------'

print '当前数据库:'+@tableName

print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'

print '合法命名方式:'+@newNameStr

print '文件组名称:'+@fileGroupName

print 'ndf物理文件名称:'+@ndfName

print '物理文件完整路径:'+@fullPath

print '分区函数:'+@partFunName

print '分区方案:'+@schemeName

/*

--查看创建的分区函数

select * from sys.partition_functions

--查看分区函数的临界值

select * from sys.partition_range_values

--查询分区方案

select * from sys.partition_schemes

--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据

select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1

*/

GO

点击确定按钮

上述代码中的变量名称,路径等均可自行修改,上述是按天为单位,以G开头的日期作为文件组名称,以F开头的日期作为物理分区文件名即ndf文件名称

4

选择新建分区左侧的计划项,然后点击右侧区域下方的新建按钮,设定新建分区的时间间隔,图中设置的是每天创建一个新的分区,用户也可以自行修改,按月,按周,按自定义时间等

其他的条目,通知,警报,目标可自行设置,也可不设置,至此自动创建分区的计划任务已成功设置.

END

步骤二:对表应用分区方案和分区函数

右击要分区的表,选择存储菜单下的创建分区,上述步骤一中创建的分区函数是按datetime类型进行的分区,所以创建分区的时候需要选择相应类型的字段作为分区依据,用户也可以根据int型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行.

需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可.

有两种方法可以实现对一个表分区.一是创建一个新的标识为分区表的表(你可参照此步骤),然后把数据复制到这张新表,再对这两张表分别改名.或者,像我写在下面的,通过重建或创建一个聚集索引来达到分区一个表.

一个SQL Server表和数据进行分区示例

--Table/Index creation

CREATE TABLE [dbo].[TABLE1]

([pkcol] [int] NOT NULL,

[datacol1] [int] NULL,

[datacol2] [int] NULL,

[datacol3] [varchar](50) NULL,

[partitioncol] datetime)

GO

ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol)

GO

CREATE NONCLUSTERED INDEX IX_TABLE1_col2col3 ON dbo.TABLE1 (datacol1,datacol2)

WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

-- Populate table data

DECLARE @val INT

SELECT @val=1

WHILE @val <1000

BEGIN

INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol)

VALUES (@val,@val,@val,'TEST',getdate()-@val)

SELECT @val=@val+1

END

GO

通过查看sys.partitions系统视图,上面的代码创建了一个传统的单分区表.

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

FROM sys.partitions p

INNER JOIN sys.objects o ON o.object_id=p.object_id

INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

WHERE o.name LIKE '%TABLE1%'

objectname

indexname

partition_id

partition_number

rows

TABLE1PK_TABLE1720575940427120641999

TABLE1IX_TABLE1_col2col3720575940427776001999

创建已分区的SQL Server表

为了创建一个分区表,需要先创建一个分区函数和分区方案. 下面的示例中,将通过datatime字段对一个表分区. 这里是创建这些对象与在系统视图中查看这些元数据的代码.

CREATE PARTITION FUNCTION myDateRangePF (datetime)

AS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')

GO

CREATE PARTITION SCHEME myPartitionScheme

AS PARTITION myDateRangePF ALL TO ([PRIMARY])

GO

SELECT ps.name,pf.name,boundary_id,value

FROM sys.partition_schemes ps

INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id

INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

现在我们有一个分区方案,可对表进行分区 *** 作. 既然我们想通过聚集索引来对一个创建了聚集索引的表进行分区,我们需要先删除掉这个索引并通过一个非聚集索引重建这个约束. 若这张表没有聚集索引,我们可忽略这一步,直接执行创建聚集索引语句. 类似地,若有一个聚集索引创建在分区字段上,我们可执行带DROP_EXISTING的创建聚集索引语句. 最后,若关注于执行此任务时数据库停止服务时间并且你使用的是SQL Server企业版本,可通过创建索引的ONLINE=ON选项来使数据库的停止服务时间最小. 记住,在使用ONLINE选项重建索引时,你可能会看到一些性能降低的现象. 这里是一些可能会乃至的脚本.

ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1

GO

ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (pkcol)

WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.TABLE1 (partitioncol)

WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON myPartitionScheme(partitioncol)

GO

在这些语句结束后,我们可再次查看sys.partitions系统视图(看上面的代码)并确认我们的表有4个分区.

objectname

indexname

partition_id

partition_number

rows

TABLE1IX_TABLE1_partitioncol720575940430397441233

TABLE1IX_TABLE1_partitioncol720575940431052802365

TABLE1IX_TABLE1_partitioncol720575940431708163366

TABLE1IX_TABLE1_partitioncol72057594043236352435

TABLE1IX_TABLE1_col2col3720575940433018881999

TABLE1PK_TABLE1720575940433674241999

SQL Server表和数据示例清除

--cleanup

DROP TABLE TABLE1

DROP PARTITION SCHEME myPartitionScheme

DROP PARTITION FUNCTION myDateRangePF

---