在 SQL Server 中，表分区有两种类型：水平分区和垂直分区。本文将向您展示什么是水平表分区以及如何通过示例创建它。
SQL Server 水平表分区：将一个表分成多个表称为水平分区。它有助于组织数据以实现快速访问。在此示例中，将销售额分成每月或每季度分区将帮助最终用户快速选择记录。
请记住，所有分区表都包含相同数量的列。在我们开始创建表分区之前，让我向您展示本地文件系统中可用的数据库列表。
查看我们服务器上可用的数据库。对于这个 SQL 演示，我们创建了一个名为 PartSample 的新数据库。
现在您可以在我们的文件系统中看到我们新创建的数据库 PartSample。
在 SQL Server 中创建表分区
在此示例中，我们将创建一个表分区以按月存储数据。我的意思是，每个月的订单或销售额将存储在单独的文件组中（带有 .ndf 扩展名的文件）。
为了实现相同目的，我们必须为从 1 月到 12 月的 12 个月创建 12 个单独的文件组。要创建文件组，我们必须使用 Alter Database 命令。
ALTER DATABASE PartSample ADD FILEGROUP January GO ALTER DATABASE PartSample ADD FILEGROUP February GO ALTER DATABASE PartSample ADD FILEGROUP March GO ALTER DATABASE PartSample ADD FILEGROUP April GO ALTER DATABASE PartSample ADD FILEGROUP May GO ALTER DATABASE PartSample ADD FILEGROUP June GO ALTER DATABASE PartSample ADD FILEGROUP July GO ALTER DATABASE PartSample ADD FILEGROUP August GO ALTER DATABASE PartSample ADD FILEGROUP September GO ALTER DATABASE PartSample ADD FILEGROUP October GO ALTER DATABASE PartSample ADD FILEGROUP November GO ALTER DATABASE PartSample ADD FILEGROUP December GO
Messages
--------
Command(s) completed successfully.获取系统文件组
使用以下查询检查或查看此 SQL Server 数据库中表分区中可用的文件组列表。
USE PartSample GO SELECT * FROM Sys.filegroups
以下表分区查询返回文件组名称
USE PartSample GO SELECT name AS [File Group Name] FROM Sys.filegroups WHERE type = 'FG'
将 .ndf 文件添加到 SQL Server 表分区的文件组
创建文件组后，您必须添加或分配或创建 .ndf 文件。以下查询将 .ndf 文件添加到 January 文件组。请记住，我们没有给出实际路径，因此请将“...”替换为实际的数据库路径。
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartJan],
FILENAME = 'C:\Program Files\..........\DATA\PartJan.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [January]
执行表分区查询
Messages
--------
Command(s) completed successfully.使用以下查询为剩余的 11 个月添加 .ndf 文件。请记住，这是每月数据将存储的物理位置。
-- Adding ndf for February File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartFeb],
FILENAME = 'C:\Program Files\...............\DATA\PartFeb.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [February]
-- Adding ndf for March File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartMarch],
FILENAME = 'C:\Program Files\...............\DATA\PartMarch.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [March]
-- Adding ndf for April File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartApril],
FILENAME = 'C:\Program Files\...............\DATA\PartApril.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [April]
/-- Adding ndf for May File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartMay],
FILENAME = 'C:\Program Files\...............\DATA\PartMay.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [May]
-- Adding ndf for June File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartJune],
FILENAME = 'C:\Program Files\...............\DATA\PartJune.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [June]
-- Adding ndf for July File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartJuly],
FILENAME = 'C:\Program Files\...............\DATA\PartJuly.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [July]
-- Adding ndf for August File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartAug],
FILENAME = 'C:\Program Files\...............\DATA\PartAug.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [August]
-- Adding ndf for September File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartSept],
FILENAME = 'C:\Program Files\...............\DATA\PartSept.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [September]
-- Adding ndf for October File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartOct],
FILENAME = 'C:\Program Files\...............\DATA\PartOct.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [October]
-- Adding ndf for November File Group
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartNov],
FILENAME = 'C:\Program Files\...............\DATA\PartNov.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [November]
-- File Group for December
ALTER DATABASE [PartSample]
ADD FILE
(
NAME = [PartDec],
FILENAME = 'C:\Program Files\...............\DATA\PartDec.ndf',
SIZE = 5080 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2040 KB
) TO FILEGROUP [December]
执行查询后，消息将显示为
Messages
--------
Command(s) completed successfully.从下面的屏幕截图中您可以看到，已为每个文件组创建了 .ndf 文件。
查看 SQL 表分区的文件组和相应的 .ndf 文件
使用 sys.database_files 获取有关文件组及其物理位置的信息。
USE PartSample GO SELECT * FROM sys.database_files
您还可以在 select 语句中使用更具体的列
USE PartSample GO SELECT name AS [Partition Name], physical_name AS [File Path] FROM sys.database_files -- WHERE type_desc = 'ROWS'
为 SQL Server 中的表创建分区函数
此函数将把原始表的行映射到分区表。为此，SQL 分区函数将使用一个列来识别文件组。
以下函数将根据每个月将原始表映射到文件组。此函数比较插入日期值并根据我们在括号 () 中指定的值映射到其中一个分区。
USE PartSample
GO
CREATE PARTITION FUNCTION [MonthlyPartition] (datetime)
AS RANGE RIGHT FOR VALUES ('20180201', '20180301', '20180401',
'20180501', '20180601', '20180701', '20180801',
'20180901', '20181001', '20181101', '20181201');
执行上述查询
Messages
--------
Command(s) completed successfully.创建分区方案
这将把分区表与文件组映射。以下方案将 20180201 映射到 February 等。
USE PartSample GO CREATE PARTITION SCHEME MonthWisePartition AS PARTITION MonthlyPartition TO (January, February, March, April, May, June, July, August, September, October, November, December );
执行上述查询。
Messages
--------
Command(s) completed successfully.创建分区表
让我使用新创建的表分区架构创建一个表。我建议您参考创建表和标识列以理解以下代码。
USE [PartSample] GO CREATE TABLE [dbo].[Insert]( [EmpID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](255) NULL, [LastName] [nvarchar](255) NULL, [Occupation] [nvarchar](255) NULL, [YearlyIncome] [float] NULL, [Sales] [float] NULL, [InsertDate] [datetime] NULL ) ON MonthWisePartition (InsertDate);
Messages
--------
Command(s) completed successfully.让我向新创建的表中插入几条记录。请参阅插入语句文章以了解插入操作。
USE [PartSample]
GO
INSERT INTO [dbo].[Insert]
VALUES ('Imran', 'Khan', 'Skilled Professional', 15900, 100, GETDATE())
,('Doe', 'Lara', 'Management', 15000, 60, GETDATE())
,('Ramesh', 'Kumar', 'Professional', 65000, 630, DATEADD(month, 1, GETDATE()))
从下面的屏幕截图中，您可以看到这些记录。
接下来，我们插入了一些具有不同日期的记录。请注意，我们使用了 DATEADD 函数来添加或删除当前日期中的月份。
USE [PartSample]
GO
INSERT INTO [dbo].[Insert]
VALUES ('Tutorial', 'Gateway', 'Masters', 14500, 200, DATEADD(month, 4, GETDATE()))
,('Joe', 'Root', 'Management', 10000, 160, DATEADD(month, 3, GETDATE()))
,('SQL', 'Tutorial', 'Management', 15000, 120, DATEADD(month, 2, GETDATE()))
,('Jhon', 'Wick', 'Software Sales', 21000, 1160, DATEADD(month, -7, GETDATE()))
,('Steve', 'Smith', 'App Sale', 13000, 2160, DATEADD(month, -6, GETDATE()))
,('Kishore', 'Kumar', 'Admin', 120500, 310, DATEADD(month, -5, GETDATE()))
,('Demi', 'Lovato', 'Professional', 193000, 1260, DATEADD(month, -4, GETDATE()))
,('Madison', 'De', 'Management', 90000, 1090, DATEADD(month, -3, GETDATE()))
,('Wang', 'Chung', 'Software Sale', 15000, 1560, DATEADD(month, -2, GETDATE()))
,('Dave', 'Jhones', 'Professional', 55000, 630, DATEADD(month, -1, GETDATE()))
从下面的屏幕截图中，您可以看到所有记录。如果您观察插入日期，每个月有一条记录，而 8 月有 2 条记录。
查看 SQL 表分区中的记录
使用以下查询查看每个文件组的总行数。
SELECT partition_id AS ID,
partition_number AS [Partition Number],
rows AS [Number of Rows]
FROM sys.partitions AS part
WHERE OBJECT_NAME(OBJECT_ID) = 'Insert'
您可以使用以下查询查看文件组名称以及表分区中的总行数。
USE PartSample SELECT part.partition_number AS [Partition Number], fle.name AS [Partition Name], part.rows AS [Number of Rows] FROM sys.partitions AS part JOIN SYS.destination_data_spaces AS dest ON part.partition_number = dest.destination_id JOIN sys.filegroups AS fle ON dest.data_space_id = fle.data_space_id WHERE OBJECT_NAME(OBJECT_ID) = 'Insert'
