Ⅰ sql 超多数据怎么分区好点
这样的项目少于百万级别，就别做了，这百万还只是硬件的上的投入。这几天经常看到你问类似的问题。说实话，这样的性能要求，若纯靠技术手段实现。俺只能呵呵呵了
Ⅱ 什么是SQL Server自动化管理分区设计方案
（图1：整体概念图） 数据流经过分区方案，被分配到不同的分区中，从图中可以看出，分区是可以重复利用的，后台有一个所谓的自动化切换分区的作业在跑，目的就是如果重复 利用这些分区。这里的PRIMARY目的就是说明它与其它文件组的一个平级关系，而且我们在做交换分区时候也会用到PRIMARY，需要事先分配足够的空间； （图2：自动化设计图） 自动化切换分区作业的逻辑处理，其中分区管理表的设计是比较重要的，它的灵活度关系到整个自动化的效果； 这个逻辑有以下几个特点： 1、 分区的索引进行存储位置对齐；其它索引在创建时就使用了分区方案，索引数据跟随分区数据一起存储在分区中； 2、分区管理表，包含了分区记录数预警设计，在Id达到这个值后就会进行交换分区； 3、分区管理表，FileGroup_String字段的数据可以通过SQL脚本自动化生成，条件就是分区文件组名称需要有规律； 4、 临时表是创建在PRIMARY主分区上，跟原表使用相同的分区方案；需要事先给PRIMARY分配大于或者等于一个分区文件大小的空间，这样在交换分区的时候就不用增量为主分区分配数据空间； 5、 交换旧数据到临时表，使用下面的语句可以把数据交换到相同的分区中编号，这样可以应对临时表就是一个历史表，而好处就是历史表也同样使用了分区； ALTER TABLE ［tb］ SWITCH PARTITION @PARTITION_num TO ［Temp_tb］ 6、这里需要先修改分区方案，才能修改分区函数，这个跟创建分区函数与分区方案的顺序是刚好相反的； 分区管理表（PartitionManage）是能应对比较多的情况： 1、 比如我们可以修改预警值（Change_Value），让数据提早进入交换分区； 2、比如我们可以修改分区值（Part_Value），达到调整分区间隔的目的； 3、比如我们可以修改分区文件组名称（FileGroup_String），达到跳级文件组的目的；通过修改分区管理表来设置分区值与分区文件组的对应关系； 4、再比如，我们一次性修改了分区方案和分区函数，已经去到很后面的分区值了，那么我们只要设置这些分区值的状态（IsDone）为1（True）就可以解决； 5、记录了进行交换分区的时间（UpdateTime），方便查询； 不知道朋友们看到这个标题是会陌生还是熟悉呢？今天带大家一起看看一起介绍下SQL Server自动化管理分区设计方案； 一、设计说明 设计这个自动化的目的是想要交替、重复地使用固定的几个分区（分区编号01~05）来保存数据，当最后一个分区就是快满的时候，我们会把最旧数据的分区的数据清空出分区，新数据就可以使用老分区空间了； 应用这个自动化管理分区的环境是有些限制的； 其一：分区的数据是呈现递增的，比如分区字段是自增Id值，或者是以日期作为分区； 其二：可以接受历史 数据被移除分区表带来的问题； 其三：一天进库的数量不应大于分区管理表PartitionManage中Part_Value与Change_Value 的差，因为我们作业执行的频率是1天，不过你可以调整Change_Value或者作业的执行频率；二、看图说话
Ⅲ 如何创建SQL Server 2005表分区
创建一个分区表首先要定义你用来映射表内分区的分区函数。在下面定义的分区函数中，我将使用三个分区，每个分区对应于SalesHistoryArchive表中的每一种产品类型，这个表将在稍后定义。基本上，这些分区会把SalesHistoryArchive档案表划分成三个不同的表，它们由SQL Server自动维护。CREATE PARTITION FUNCTION [pf_Proct_Partition](VARCHAR(10)) AS RANGE LEFTFOR VALUES (N'BigScreen', N'Computer', N'PoolTable')定义函数的范围决定分区值属于哪一个边界。RNAGE LEFT：指定分区值将小于或等于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于或等于‘BigScreen’的产品将映射到第一个分区。任何名称大于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。RANGE RIGHT：指定分区值将小于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于‘BigScreen’的产品将映射到第一个分区。任何名称大于或等于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。建好分区函数后，现在我需要建立分区方案。我将把例子中的所有分区都映射到主文件组中。如果我希望将分区映射到不同的文件组，我会按文件组列表的顺序加入文件组名称。下面是创建分区方案的代码：CREATE PARTITION SCHEME [ps_Proct_Scheme] AS PARTITION[pf_Proct_Partition]ALL TO ([PRIMARY])现在我建立需要分区的表，我可以创建SalesHistoryArchive表并在其中加载数据。在CREATE TABLE语句末尾，分区方案使用表中的一个字段名告诉SQL Server如何映射需要分区的表中的数据。查看列表A中的代码样本。我需要对数据进行一些查询，保证分区正常运行。下面的查询返回SalesHistoryArchive表的所有行，并使用$partition函数指出返回的行属于哪个分区：SELECT $partition.[pf_Proct_Partition](Proct), *FROM SalesHistoryArchive这个查询返回所有映射到SalesHistoryArchive表中的分区：SELECT * From sys.partitionsWHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'接下来…在后面的文章中，我将解释如何修改表中的这些分区，从而在其中增加新数据，并非常高效地删除旧数据。
Ⅳ 如何对一个已经存在的SQL Server表分区
有两种方法可以实现对一个表分区.一是创建一个新的标识为分区表的表(你可参照此步骤),然后把数据复制到这张新表,再对这两张表分别改名.或者,像我写在下面的,通过重建或创建一个聚集索引来达到分区一个表.
一个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
TABLE1 PK_TABLE1 72057594042712064 1 999
TABLE1 IX_TABLE1_col2col3 72057594042777600 1 999
创建已分区的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
TABLE1 IX_TABLE1_partitioncol 72057594043039744 1 233
TABLE1 IX_TABLE1_partitioncol 72057594043105280 2 365
TABLE1 IX_TABLE1_partitioncol 72057594043170816 3 366
TABLE1 IX_TABLE1_partitioncol 72057594043236352 4 35
TABLE1 IX_TABLE1_col2col3 72057594043301888 1 999
TABLE1 PK_TABLE1 72057594043367424 1 999
SQL Server表和数据示例清除
--cleanup
DROP TABLE TABLE1
DROP PARTITION SCHEME myPartitionScheme
DROP PARTITION FUNCTION myDateRangePF
Ⅳ sqlserver2008怎么实现自动分区表
/*第一步：创建分区函数*/
Create partition function Part_func_Bag(varchar(20)) as range right
/*正式区间
for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714')
/*第二步：创建文件组和文件*/
alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go
/*第三步：创建分区方案并关联到分区函数*/
Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go
/*第四步 重建索引（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组。数据在这一步开始转移。）*/
EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go
Ⅵ sql server2008怎么建分区
打开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型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行.
需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可.