SqlServer關于分區表的相關知識點有哪些

138次閱讀

沒有評論

共計 7577 個字符，預計需要花費 19 分鐘才能閱讀完成。

這篇文章主要講解了“SqlServer 關于分區表的相關知識點有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“SqlServer 關于分區表的相關知識點有哪些”吧！

創建分區表的大致步驟

1、建立文件組(類似 oracle 的表空間)，當然不建立也行，把所有分區都放一個文件組內也可以

2、建立分區函數，數據按什么范圍分配

3、建立分區方案，關聯分區函數，也會關聯文件組，分區函數把數據分了幾個范圍，就需要關聯幾個文件組，當然也可以把這幾個分區范圍都放入到同一個文件組

4、建立表，關聯分區方案

遇到的一個 Bug

直接右鍵表導出表結構時導不出分區信息，只能右鍵數據庫 – 任務 – 生成腳本才能導出表的分區信息

分區表的一些結論：

1、分區字段不一定需要建立索引

2、分區字段可以創建為 clustered 索引或 noclustered 索引

3、分區字段不管是 clustered 索引還是 noclustered 索引，重建為 clustered 索引且沒有關聯分區方案時，分區表就變成了非分區表

4、普通表轉換為分區表，只要在該表創建一個 clustered 索引，并在 clustered 索引上使用分區方案即可。比如非分區表的字段 1 創建 clustered 索引且關聯分區方案時，該表轉換為分區表，且分區字段為字段 1。

5、分區表轉換為普通表，如果有分區字段有索引則重建分區字段為 clustered 索引且不關聯分區方案即可，分區字段沒有索引的話則分區字段新建 clustered 索引且不關聯分區方案即可

6、普通表改成分區表或把分區表改成普通表，只能使用 clustered 索引來實現，因為有了 clustered 索引就是索引組織表，通過 clustered 索引的重建來實現表的重新分布。普通表變成分區表，把分區字段重建為 clustered 索引并關聯分區方案即可，分區表變成普通表，把分區字段重建為 clustered 索引不要關聯分區方案即可。

7、分區表創建唯一性約束，必須包含分區列

8、創建分區方案時，必須保證文件組數量匹配分區函數的分區范圍段，文件組名稱重復沒有關系，當然也可以使用 ALL，指定一個文件組名稱，這樣所有的分區函數的分區范圍段數據都落到這一個文件組。

9、分區函數和分區方案是在一個個數據庫里面的，而不是面對整個實例的

10、分區表太大占用很多磁盤空間，delete 了一些字段后大小還是沒變，這個時候進行分區合并或把分區表轉換為普通表，則大小會降下來

創建分區表的步驟

1.1、建立文件組的示例

alter database test1 add filegroup part1;

alter database test1 add filegroup part1000;

alter database test1 add filegroup part2000;

alter database test1 add filegroup part3000;

alter database test1 add filegroup part4000;

1.2、建立文件的示例，關聯文件組

ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = G:\test1part1.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = G:\test1part1000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = G:\test1part2000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part2000;

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = G:\test1part3000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part3000;

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = G:\test1part4000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part4000;

2、建立分區函數的示例，分區函數名為 partfun1

CREATE PARTITION FUNCTION partfun1 (int)

AS RANGE LEFT FOR VALUES (1000 , 2000 , 3000 , 4000)

–VALUES (1000 , 2000 , 3000 , 4000)表明，將把表分為 5 個區了，是從根據表字段的值的大小來分區，五個區分別是最小 –1000,1000-2000,2000-3000,3000-4000,4000- 最大

3、建立方案的例子, 關聯分區函數 partfun1，關聯文件組

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (part1,part1000,part2000,part3000,part4000);

– 建立在 part1,part1000,part2000,part3000,part4000 幾個文件組上

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

TO (part1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

– 建立在 part1、[PRIMARY]文件組上，把 part1 換成 [PRIMARY] 也沒問題，這樣就類似都建立在 [PRIMARY] 文件組上

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (part1);

– 都建立在 part1 文件組上

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARY]);

– 都建立在 [PRIMARY] 文件組上

4、建立分區表的示例

CREATE TABLE parttable1(

[ID] [int] NOT NULL,

[IDText] [nvarchar](max) NULL,

[Date] [datetime] NULL)

ON [partschema1](ID);

insert into parttable1 values (1, 1 ,getdate()-4);

insert into parttable1 values (1001, 1001 ,getdate()-3);

insert into parttable1 values (2001, 2001 ,getdate()-2);

insert into parttable1 values (3001, 3001 ,getdate()-1);

insert into parttable1 values (4001, 4001 ,getdate());

5、驗證分區表的數據

SELECT * FROM parttable1;

– 返回分區表所有行

SELECT distinct $PARTITION.[partfun1](4) FROM parttable1;

– 返回 ID 字段值為 4 的行屬于哪個分區

SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2

– 返回第 2 個分區的所有行,ID 就是分區字段 ID

注意：不能因為 SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)= 2 有結果就說明它是分區表，本文最后試驗 7 該表是非分區表了，但是執行 SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)= 2 還是有結果的

新增分區

1、為分區方案指定一個可以使用的文件組(新增分區方案的文件組)。

2、修改分區函數(新增分區函數的數據范圍)

ALTER PARTITION SCHEME partschema1 NEXT USED [PRIMARY]

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ( 4500)

select p.partition_number,p.rows from sys.indexes i inner join sys.partitions p on p.object_id = i.object_id and i.object_id = object_id(parttable2) order by 1

– 第一條語句，如果分區方案使用的 ALL TO ([PRIMARY])，則這條語句不用執行

– 第二條語句新增一個分區，范圍是 4000-4500

– 第三條語句驗證新增分區是否存在，是否存在行數

刪除 \ 合并分區

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ( 2000)

就把 1000-2000 這個分區，刪除了，合并成了 1000-3000

– 無法像 oracle 一樣執行 ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME;

刪除分區表及對應的文件組

刪除順序為：刪除分區表、刪除分區方案、刪除分區函數，最后刪除文件組，刪除完文件組后對應的文件也就刪除了

分區表轉換為普通表，普通表轉換為分區表的示例

DROP TABLE parttable1;

CREATE TABLE parttable1(

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](16) NOT NULL,

[Id2][int] NOT NULL

) ON partschema1(Id2);

insert into parttable1 values (1 ,1);

insert into parttable1 values (1001 ,1001);

insert into parttable1 values (2001 ,2001);

insert into parttable1 values (3001 ,3001);

insert into parttable1 values (4001 ,4001);

1、在分區表上創建的唯一約束，必須包含分區列。

ALTER TABLE parttable1 ADD CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC)

報錯 Column Id2 is partitioning column of the index PK_prattable1_id . Partition columns for a unique index must be a subset of the index key.

2、分區列 id2 新建 clustered 索引，parttable1 還是分區表

create clustered index CI_prattable1_id2 on parttable1(id2);

3、分區列 id2 創建 nonclustered 索引，parttable1 還是分區表

drop index CI_prattable1_id2 on parttable1;

create nonclustered index NCI_prattable1_id2 on parttable1(id2);

4、非分區列 id 列創建 clustered 索引，parttable1 還是分區表，說明非分區列可以是 cluster 索引列

create clustered index CI_prattable1_id on parttable1(id);

5、分區列 id2 重建為 nonclustered 索引并且不使用分區方案，parttable1 還是分區表

create nonclustered index NCI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];

6、分區列 id2 重建為 clustered 索引不加 ON 條件，parttable1 還是分區表

drop index CI_prattable1_id on parttable1;

drop index NCI_prattable1_id2 on parttable1;

create clustered index CI_prattable1_id2 on parttable1(id2);

create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON);

7、分區列 id2 重建為 clustered 索引加上 ON 條件但不使用分區方案，parttable1 變成了非分區表

create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];

8、分區列 id2 重建為 clustered 索引并且使用分區方案，parttable1 變成了分區表

create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) on partschema1(Id2);

9、刪掉上面 8 的 clustered 索引后，parttable1 還是分區表

drop index CI_prattable1_id2 on parttable1;

10、分區列 id2 新建為 clustered 索引并且不使用分區方案，parttable1 變成了非分區表

create clustered index CI_prattable1_id2 on parttable1(id2) ON [PRIMARY];

11、刪掉上面 10 的 clustered 索引后，parttable1 還是非分區表

drop index CI_prattable1_id2 on parttable1;

12、分區列 id2 新建為 nonclustered 索引，雖然使用了分區方案，還是非分區表

create nonclustered index NCI_prattable1_id2 on parttable1(id2) on partschema1(Id2);

分區表轉換為普通表，遇到分區字段是主鍵的情況下，則刪除主鍵約束，再對原來主鍵的字段重建 cluster 索引或重建為主鍵，但是都不關聯分區方案

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (

CREATE CLUSTERED INDEX PK_NAME ON Table_name(column) WITH (ON [PRIMARY];

或

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY CLUSTERED(column) WITH (ON [PRIMARY];

普通表轉換為分區表，要保留原來的主鍵的情況下，則刪除主鍵約束，再創建主鍵但不設為聚集索引，再創建新的聚集索引，在該聚集索引中使用分區方案

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED(column) WITH (ON [PRIMARY];

– 創建主鍵，但不設為聚集索引

CREATE CLUSTERED INDEX index_name ON Table_name(column) ON 分區方案(分區字段)

– 創建一個新的聚集索引，在該聚集索引中使用分區方案

查詢某張分區表的總行數和大小，比如表為 crm.EmailLog

exec sp_spaceused crm.EmailLog

查詢某張分區表的信息，每個分區有多少行，比如表為 crm.EmailLog

select convert(varchar(50), ps.name

) as partition_scheme,

p.partition_number,

convert(varchar(10), ds2.name

) as filegroup,

convert(varchar(19), 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(crm.EmailLog)

and i.index_id in (0, 1)

order by p.partition_number

查詢分區函數

select * from sys.partition_functions

查看分區架構

select * from sys.partition_schemes

感謝各位的閱讀，以上就是“SqlServer 關于分區表的相關知識點有哪些”的內容了，經過本文的學習后，相信大家對 SqlServer 關于分區表的相關知識點有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完