SQL Server表分區的NULL值問題

2024-08-31 00:54:14

字體：大中小

來源：轉載

供稿：網友

SQL Server表分區的NULL值問題SQL Server表分區的NULL值問題

SQL Server表分區只支持range分區這一種類型，但是本人覺得已經夠用了

雖然MySQL支持四種分區類型：RANGE分區、LIST分區、HASH分區、KEY分區，還支持子分區，但是功能有些欠缺

共同點是MySQL跟SQL Server也有分區對齊的問題，都是水平切分，大家都允許分區列存在NULL值

這次我們測試SQL Server表分區的分區列的NULL值，究竟NULL值是被存放在哪個區間，以前一直沒有注意

測試腳本

--1.創建文件組ALTER DATABASE [sss]ADD FILEGROUP [FG_TinyBlog_Id_01]ALTER DATABASE [sss]ADD FILEGROUP [FG_TinyBlog_Id_02]ALTER DATABASE [sss]ADD FILEGROUP [FG_TinyBlog_Id_03]ALTER DATABASE [sss]ADD FILEGROUP [FG_TinyBlog_Id_04]ALTER DATABASE [sss]ADD FILEGROUP [FG_TinyBlog_Id_05]--2.創建文件ALTER DATABASE [sss]ADD FILE(NAME = N'FG_TinyBlog_Id_01_data',FILENAME = N'E:/DataBase/sss/FG_TinyBlog_Id_01_data.ndf',SIZE = 96MB, FILEGROWTH = 24MB )TO FILEGROUP [FG_TinyBlog_Id_01];ALTER DATABASE [sss]ADD FILE(NAME = N'FG_TinyBlog_Id_02_data',FILENAME = N'E:/DataBase/sss/FG_TinyBlog_Id_02_data.ndf',SIZE = 96MB, FILEGROWTH = 24MB )TO FILEGROUP [FG_TinyBlog_Id_02];ALTER DATABASE [sss]ADD FILE(NAME = N'FG_TinyBlog_Id_03_data',FILENAME = N'E:/DataBase/sss/FG_TinyBlog_Id_03_data.ndf',SIZE = 96MB, FILEGROWTH = 24MB )TO FILEGROUP [FG_TinyBlog_Id_03];ALTER DATABASE [sss]ADD FILE(NAME = N'FG_TinyBlog_Id_04_data',FILENAME = N'E:/DataBase/sss/FG_TinyBlog_Id_04_data.ndf',SIZE = 96MB, FILEGROWTH = 24MB )TO FILEGROUP [FG_TinyBlog_Id_04];ALTER DATABASE [sss]ADD FILE(NAME = N'FG_TinyBlog_Id_05_data',FILENAME = N'E:/DataBase/sss/FG_TinyBlog_Id_05_data.ndf',SIZE = 96MB, FILEGROWTH = 24MB )TO FILEGROUP [FG_TinyBlog_Id_05];--3.創建分區函數CREATE PARTITION FUNCTIONFun_TinyBlog_Id(INT) ASRANGE LEFTFOR VALUES(-10,0,1,6)--4.創建分區方案CREATE PARTITION SCHEME[Sch_TinyBlog_Id] aspARTITION [Fun_TinyBlog_Id]TO([FG_TinyBlog_Id_01],[FG_TinyBlog_Id_02],[FG_TinyBlog_Id_03],[FG_TinyBlog_Id_04],[FG_TinyBlog_Id_05])

View Code

插入測試數據

USE [sss]CREATE TABLE TinyBlog(id INT  NULL,NAME VARCHAR(100))  ON [Sch_TinyBlog_Id](id)SELECT * FROM TinyBlog ORDER BY id INSERT INTO [dbo].[TinyBlog]        ( [id], [NAME] )VALUES  ( NULL, -- id - int          '3232'  -- NAME - varchar(100)          )INSERT INTO [dbo].[TinyBlog]        ( [id], [NAME] )VALUES  ( -2, -- id - int          '-2'  -- NAME - varchar(100)          )INSERT INTO [dbo].[TinyBlog]        ( [id], [NAME] )VALUES  ( 66, -- id - int          '66'  -- NAME - varchar(100)          )INSERT INTO [dbo].[TinyBlog]        ( [id], [NAME] )VALUES  ( 0, -- id - int          '0'  -- NAME - varchar(100)          )INSERT INTO [dbo].[TinyBlog]        ( [id], [NAME] )VALUES  ( -30, -- id - int          '-30'  -- NAME - varchar(100)          )

表數據如下

SELECT * FROM TinyBlog ORDER BY id

分區分布

--查看分區架構文件組分布SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,        p.partition_number ,        CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,        CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,        STR(p.rows, 9) AS rowsFROM    sys.indexes i        JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id        JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id        JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id        JOIN sys.partitions p ON dds.destination_id = p.partition_number                                 AND p.object_id = i.object_id                                 AND p.index_id = i.index_id        JOIN sys.partition_functions pf ON ps.function_id = pf.function_id        LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id                                                  AND v.boundary_id = p.partition_number                                                  - pf.boundary_value_on_rightWHERE   i.object_id = OBJECT_ID('TinyBlog')        AND i.index_id IN ( 0, 1 )ORDER BY p.partition_number--分區區間--SELECT  *  FROM    sys.partition_range_values

View Code

分區情況

--分區情況SELECT  c.* ,        b.[groupname] AS '分區方案對應的文件組名稱' ,        d.name '當前分區函數對應的分區方案'FROM    sys.destination_data_spaces AS a        INNER JOIN sysfilegroups AS b ON a.[data_space_id] = b.[groupid]        INNER JOIN ( SELECT $PARTITION.Fun_TinyBlog_Id(id) AS 分區編號 ,                            MIN(id) AS Min_value ,                            MAX(id) AS Max_value ,                            COUNT(id) AS 記錄數                     FROM   [dbo].[TinyBlog]                     GROUP BY $PARTITION.Fun_TinyBlog_Id(id)                   ) AS c ON c.[分區編號] = a.[destination_id]        INNER JOIN sys.partition_schemes AS d ON a.[partition_scheme_id] = d.data_space_idORDER BY c.[分區編號]

View Code