sql server 分割字段 sql文件分割

转载

cnolnic 2023-12-07 13:21:27

文章标签 sql server 分割字段数据库分区表 Desktop 字符串 文章分类 SQL Server 数据库

背景：

一般情况下，我们建立数据库表时，表数据都存放在一个文件里。

但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。

1.创建文件组
alter database <数据库名> add filegroup <文件组名>
alter database HARVEY add filegroup ByIdGroup1
alter database HARVEY add filegroup ByIdGroup2
alter database HARVEY add filegroup ByIdGroup3
2.创建数据文件到文件组里面
alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>
alter database HARVEY add file 
(name=N'ById1',filename=N'C:\Users\Administrator\Desktop\DATA\file1.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1
alter database HARVEY add file 
(name=N'ById2',filename=N'C:\Users\Administrator\Desktop\DATA\file2.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup2
alter database HARVEY add file 
(name=N'ById3',filename=N'C:\Users\Administrator\Desktop\DATA\file3.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup3
若以后想增加文件到文件组，只需
alter database HARVEY add file 
(name=N'ById4',filename=N'C:\Users\Administrator\Desktop\DATA\file4.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1
还有另外的文件方式如下等，不再扩展
file(
  NAME = N'File1',   
  FILENAME = N'G:\data\FG1\File1.ndf',   
  SIZE = 1MB,   
  MAXSIZE = 100MB,   
  FILEGROWTH = 10% 
)
注意：加上
N'string' 表示string是个Unicode字符串
3.创建分区函数
--创建分区函数语法
create partition function分区函数名(<分区列类型>) as range [left/right] for values (每个分区的边界值,....) 
--删除分区语法
drop partition function <分区函数名>
--创建分区函数
CREATE PARTITION FUNCTION[bgPartitionFun](int) AS RANGE LEFT FOR VALUES (
N'2',--sql server自动将字符串转为int类型
N'4',
N'6')  --4个分区
--按照时间分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE LEFT|RIGHT
FOR VALUES(
 @dt, --string类型，sql server会自动将字符串转化为datetime
DATEADD(Year, 1, @dt))
注意：LEFT相当于左区间，RIGHT相当于右区间
4.创建分区方案
--创建分区方案语法
create partition scheme <分区方案名称> as partition <分区函数名称> [all]to (文件组名称,....) 
--删除分区方案语法
drop partition scheme<分区方案名称>
--删除分区方案 bgPartitionSchema
drop partition scheme bgPartitionSchema1
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup2], [ByIdGroup3],[ByIdGroup1])
注意：分区函数必须关联分区方案才能有效，然而分区方案指定的文件组数量必须与分区数量一致，哪怕多个分区存放在一个文件组中。
5.分区索引
--创建分区索引语法
create <索引分类> index <索引名称> on <表名>(列名)on <分区方案名>(分区依据列名)
--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
注意：使用分区索引查询，可以避免多个cpu操作多个磁盘时产生的冲突。
6.重建索引（对已存在的表进行分区）
（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组）
EXEC sp_helpindex N'BigOrder' --查看orders中使用的索引,删除clustered索引
drop index idx_cl_od on BigOrder;
go 
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema] ON [dbo].[BigOrder] 
{
　　 [OrderId]
}
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
go
7.建立分区表
--创建分区表语法
create table <表名> (
  <列定义>
)on<分区方案名>(分区列名)
 
--创建分区表create table BigOrder (
   OrderId              int                  identity,
   orderNum             varchar(30)          not null,
   CreateDate           datetime             null default getdate()
)on bgPartitionSchema(OrderId)
8.判断数据在哪个分区上
select $partition.<分区函数>(分区列名)
select $partition.bgPartitionFun(3)
select $partition.bgPartitionFun(b.orderid) as partitionNum,b.* from BigOrder b
其他
查看分区信息sql
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions     AS  p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber
查看分区的相关情况
--查看分区及分区范围的情况
select * from sys.partitions where object_id = object_id('orders');
select * from sys.partition_range_values;
 
--查看分区架构情况
select * from sys.partition_schemes;
 
--查看某一特定分区列值属于哪个分区
select Performance.$partition.Part_func_orders('20050325') as partition_num;
 
--查看某一特定分区的记录 
select * from orders where Performance.$partition.Part_func_orders(orderdate) = 2

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。