Sql-Server

如何將數據與特定分區對齊？加上與同一個表分區相關的其他2個問題

  • April 19, 2017

我正在創建一個用於測試目的的表：

begin try
drop table countries
end try
begin catch
end catch
go


create table countries(
name nvarchar(100) not null,
visit date not null,
constraint pk__countries primary key clustered (visit,name))


set nocount on

insert into countries VALUES ('Portugal-Lisbon','20111101')
insert into countries VALUES ('Spain-Madrid','20111101')
insert into countries VALUES ('Italia-Milano','20120101')
insert into countries VALUES ('Norway-Thromso','20121201')
insert into countries VALUES ('USA-California','20160110')
insert into countries VALUES ('Brasil-Porto Alegre','20131101')
insert into countries VALUES ('Lithuania-Vilnius','20131101')
insert into countries VALUES ('France-Paris','20131101')
insert into countries VALUES ('Russia-Moscow','20141101')
insert into countries VALUES ('Germany-Liepzig','20140901')
insert into countries VALUES ('Germany-Hamburg','20100901')
insert into countries VALUES ('Estonia-Viru','20151101')
insert into countries VALUES ('Sweden-Gotemborg','20151101')
insert into countries VALUES ('Latvia-Riga','20141101')
insert into countries VALUES ('India-Vrindavana','20151101')
insert into countries VALUES ('Switzerland-Lugano','20161101')
insert into countries VALUES ('USA-New York','20160110')
insert into countries VALUES ('Russia-Cheliabinsky','20170110')
insert into countries VALUES ('Italia-Bergamo','20170101')
insert into countries VALUES ('UK-Telford','20170405')
insert into countries VALUES ('Spain-Barcelona','20101101')
insert into countries VALUES ('Spain-Fuerteventura','20101001')

然後我創建我的分區函式和分區模式：

---------------------------------------------------------------------------------------
--- create a partition function
-- https://technet.microsoft.com/en-us/library/ms186307(v=sql.110).aspx
---------------------------------------------------------------------------------------

use radhe
go


IF EXISTS (SELECT * FROM sys.partition_functions
   WHERE name = 'PF_year')
DROP PARTITION FUNCTION PF_year ;
GO



CREATE PARTITION FUNCTION PF_year (date)
AS RANGE RIGHT 
              FOR VALUES ( '20100101',
                           '20110101',
                           '20120101',
                           '20130101',
                           '20140101',
                           '20150101',
                           '20160101');




---------------------------------------------------------------------------------------
--- Create a Partition Scheme
---------------------------------------------------------------------------------------
use radhe
go
DROP PARTITION SCHEME PSC_YEAR 
GO


CREATE PARTITION SCHEME PSC_YEAR AS 
PARTITION PF_year   TO 
([PRIMARY],[F0],[F1], [F2], [F3], [F4], [F5], [F6]) 
GO

之後，我讓我的表使用分區模式：

--================================================================================
--================================================================================
use radhe
go

ALTER TABLE countries
DROP CONSTRAINT [pk__countries] 
go

CREATE UNIQUE CLUSTERED INDEX pk__countries   
ON dbo.countries (visit desc ,name asc)
WITH (DROP_EXISTING=off,ONLINE=Off,FILLFACTOR=100,DATA_COMPRESSION=PAGE) ON PSC_YEAR (visit)
go

所有這些都完成了 - 為了驗證一切都完成了，我有以下腳本來檢查每個分區中有多少行：

SELECT [table_name] = SCHEMA_NAME([schema_id]) + '.' + t.[name]
     ,i.[name] AS [index_name]
     ,i.[type_desc] AS [index_type]
     ,ps.[name] AS [partition_scheme]
     ,pf.[name] AS [partition_function]
     ,p.[partition_number]
     ,r.[value] AS [current_partition_range_boundary_value]
     ,p.[rows] AS [partition_rows]
     ,p.[data_compression_desc]
FROM sys.tables t
INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id]
                          AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]
INNER JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r ON pf.[function_id] = r.[function_id]
   AND r.[boundary_id] = p.[partition_number]
where t.object_id = object_id('dbo.countries')
GROUP BY SCHEMA_NAME([schema_id])
       ,t.[name]
       ,i.[name]
       ,i.[type_desc]
       ,ps.[name]
       ,pf.[name]
       ,p.[partition_number]
       ,r.[value]
       ,p.[rows]
       ,p.[data_compression_desc]
ORDER BY SCHEMA_NAME([schema_id])
       ,t.[name]
       ,i.[name]
       ,p.[partition_number];

上面的腳本給了我以下結果： 在此處輸入圖像描述

但是當我執行以下腳本時，要查看我每年有多少行（因為我每年進行分區），我得到以下結果：

select c.name,
      [year of visit] = year(visit),
      [number of visits] = count(*) over (partition by year(visit))
from countries c
order by visit desc

在此處輸入圖像描述

為什麼我 2010 年的記錄不在分區 1 上？

我可以準確找出記錄在哪個分區上嗎？

如何添加另一個分區，假設明年（2018 年）我將添加 2017 年的分區，而無需重建聚集索引？

最後但同樣重要的是，假設幾年後，我將不再對 2010 年的行感興趣。我怎樣才能擺脫所有的分區？

下面是這個數據庫的創建方式，基本上我的目標是將每個分區放在不同的文件上。

CREATE DATABASE [radhe]
CONTAINMENT = NONE
ON  PRIMARY 
( NAME = N'radhe', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\radhe.mdf' , SIZE = 70254592KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F0] 
( NAME = N'F0', FILENAME = N'C:\sql2014_data\RADHE0.ndf' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F1] 
( NAME = N'F1', FILENAME = N'C:\sql2014_data\RADHE1.ndf' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F2] 
( NAME = N'F2', FILENAME = N'C:\sql2014_data\RADHE2.ndf' , SIZE = 3145728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F3] 
( NAME = N'F3', FILENAME = N'C:\sql2014_data\RADHE3.ndf' , SIZE = 3145728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F4] 
( NAME = N'F4', FILENAME = N'C:\sql2014_data\RADHE4.ndf' , SIZE = 3145728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F5] 
( NAME = N'F5', FILENAME = N'C:\sql2014_data\RADHE5.ndf' , SIZE = 3145728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F6] 
( NAME = N'F6', FILENAME = N'C:\sql2014_data\RADHE6.ndf' , SIZE = 3145728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [F7] 
( NAME = N'F7', FILENAME = N'C:\sql2014_data\RADHE7.ndf' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [Facts ] 
( NAME = N'Facts', FILENAME = N'C:\sql2014_data\facts.ndf' , SIZE = 45613056KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
FILEGROUP [Facts2] 
( NAME = N'Facts2', FILENAME = N'C:\sql2014_data\facts2.ndf' , SIZE = 59244544KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
LOG ON 
( NAME = N'radhe_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\radhe_log.ldf' , SIZE = 2097152KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
END

下面的這些連結是有關表分區的有趣參考：

如何對現有的非分區表進行分區？

除了目前年份之外，同時對錶進行分區的最佳方法是什麼？

SQL Server 2008 中的分區關鍵問題

為什麼我 2010 年的記錄不在分區 1 上？

使用RANGE RIGHT函式，大於或等於第一個邊界 (‘20100101’) 且小於第二個邊界 (‘20110101’) 的值儲存在分區 2 中。只有小於第一個 ‘20100101’ 邊界的行儲存在分區 1 中。您的測試數據中沒有任何行符合此條件。

我可以準確找出記錄在哪個分區上嗎？

$PARTITION使用與分區列值一起呼叫分區函式：

SELECT 
     visit
   , $PARTITION.PF_Year(visit) AS PartitionNumber
FROM dbo.countries;

如何添加另一個分區，假設明年（2018 年）我將添加 2017 年的分區，而無需重建聚集索引？

將分區方案NEXT USED文件組設置為所需的文件組和SPLIT具有新邊界的函式。這應該在 2018 年數據存在之前完成，理想情況下，現有分區 (2017) 為空，以避免代價高昂的數據移動​​。

ALTER PARTITION SCHEME PSC_YEAR
   NEXT USED [F8];
ALTER PARTITION FUNCTION PF_Year()
   SPLIT RANGE('20180101');

最後但同樣重要的是，假設幾年後，我將不再對 2010 年的行感興趣。我怎樣才能擺脫所有的分區？

SWITCH將分區放入相同模式的對齊臨時表中，並使用 . 刪除分區邊界MERGE。我建議使用與源相同的方案對臨時表進行分區，以確保對齊並在SWITCH.

CREATE TABLE dbo.countries_staging(
     name nvarchar(100) not null
   , visit date not null
);

CREATE UNIQUE CLUSTERED INDEX pk__countries_staging   
ON dbo.countries_staging (visit desc ,name asc)
WITH(DATA_COMPRESSION=PAGE)
ON PSC_YEAR (visit);
GO

ALTER TABLE dbo.countries
   SWITCH PARTITION $PARTITION.PF_Year('20100101')
   TO dbo.countries_staging PARTITION $PARTITION.PF_Year('20100101');
TRUNCATE TABLE dbo.countries_staging;
ALTER PARTITION FUNCTION PF_Year()
   MERGE RANGE('20100101');
GO

引用自：https://dba.stackexchange.com/questions/171343

相關問答

Sql-Server

如何生成分區函式和分區模式的創建腳本？

  • October 26, 2019
檢視問答
Sql-Server

從分區表的分區中刪除所有數據的最快方法是什麼？

  • April 21, 2017
檢視問答
Sql-Server

分區太多，刪除舊年

  • March 28, 2021
檢視問答
Sql-Server

在 SQL Server 上建構執行計劃花費的時間太長

  • November 10, 2020
檢視問答
Sql-Server

sql數據庫中的數據大小可以小於索引大小嗎？

  • November 3, 2020
檢視問答
Sql-Server

將數據庫兼容性從舊版 CE 更改為 120

  • June 8, 2020
檢視問答