建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
SQLServer表分區(qū)的NULL值問題
SQL Server表分區(qū)只支持range分區(qū)這一種類型，往往會被大家吐槽
成都創(chuàng)新互聯(lián)公司-云計算及IDC服務(wù)提供商，涵蓋公有云、IDC機房租用、雅安電信機房、等保安全、私有云建設(shè)等企業(yè)級互聯(lián)網(wǎng)基礎(chǔ)服務(wù)，溝通電話：13518219792
人家MySQL支持四種類型：RANGE分區(qū)、LIST分區(qū)、HASH分區(qū)、KEY分區(qū)
共同點是MySQL跟SQL Server也有分區(qū)對齊的問題，都是水平切分，大家都允許分區(qū)列存在NULL值
這次我們測試SQL Server表分區(qū)的分區(qū)列的NULL值，究竟NULL值是被存放在哪個區(qū)間，以前一直沒有注意
測試腳本
- --1.創(chuàng)建文件組
- 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.創(chuàng)建文件
- 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.創(chuàng)建分區(qū)函數(shù)
- CREATE PARTITION FUNCTION
- Fun_TinyBlog_Id(INT) AS
- RANGE LEFT
- FOR VALUES(-10,0,1,6)
- --4.創(chuàng)建分區(qū)方案
- CREATE PARTITION SCHEME
- [Sch_TinyBlog_Id] AS
- PARTITION [Fun_TinyBlog_Id]
- TO([FG_TinyBlog_Id_01],[FG_TinyBlog_Id_02],[FG_TinyBlog_Id_03],[FG_TinyBlog_Id_04],[FG_TinyBlog_Id_05])
插入測試數(shù)據(jù)
- 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)
- )
表數(shù)據(jù)如下
- SELECT * FROM TinyBlog ORDER BY id
分區(qū)分布
- --查看分區(qū)架構(gòu)文件組分布
- 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 rows
- FROM 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_right
- WHERE i.object_id = OBJECT_ID('TinyBlog')
- AND i.index_id IN ( 0, 1 )
- ORDER BY p.partition_number
- --分區(qū)區(qū)間
- --SELECT * FROM sys.partition_range_values
分區(qū)情況
- --分區(qū)情況
- SELECT c.* ,
- b.[groupname] AS '分區(qū)方案對應(yīng)的文件組名稱' ,
- d.name '當(dāng)前分區(qū)函數(shù)對應(yīng)的分區(qū)方案'
- 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 分區(qū)編號 ,
- MIN(id) AS Min_value ,
- MAX(id) AS Max_value ,
- COUNT(id) AS 記錄數(shù)
- FROM [dbo].[TinyBlog]
- GROUP BY $PARTITION.Fun_TinyBlog_Id(id)
- ) AS c ON c.[分區(qū)編號] = a.[destination_id]
- INNER JOIN sys.partition_schemes AS d ON a.[partition_scheme_id] = d.data_space_id
- ORDER BY c.[分區(qū)編號]
第二個視圖直接把NULL值忽略了
根據(jù)***個視圖，我們畫出一個總結(jié)圖
分析一下
總結(jié)
結(jié)論其實很清晰，分區(qū)列不要允許NULL，如果允許列，那么我們交換分區(qū)歸檔數(shù)據(jù)的時候就會有麻煩了，因為無法判斷分區(qū)列是NULL值的那些數(shù)據(jù)行是老數(shù)據(jù)還是新數(shù)據(jù)。
分享名稱：SQLServer表分區(qū)的NULL值問題
文章起源：http://www.5511xx.com/article/cdcsdig.html
