您好,登錄后才能下訂單哦!
這篇文章主要講解了“SqlServer關于分區(qū)表的相關知識點有哪些”,文中的講解內容簡單清晰,易于學習與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學習“SqlServer關于分區(qū)表的相關知識點有哪些”吧!
創(chuàng)建分區(qū)表的大致步驟
1、建立文件組(類似oracle的表空間),當然不建立也行,把所有分區(qū)都放一個文件組內也可以
2、建立分區(qū)函數,數據按什么范圍分配
3、建立分區(qū)方案,關聯分區(qū)函數,也會關聯文件組,分區(qū)函數把數據分了幾個范圍,就需要關聯幾個文件組,當然也可以把這幾個分區(qū)范圍都放入到同一個文件組
4、建立表,關聯分區(qū)方案
遇到的一個Bug
直接右鍵表導出表結構時導不出分區(qū)信息,只能右鍵數據庫--任務--生成腳本才能導出表的分區(qū)信息
分區(qū)表的一些結論:
1、分區(qū)字段不一定需要建立索引
2、分區(qū)字段可以創(chuàng)建為clustered索引或noclustered索引
3、分區(qū)字段不管是clustered索引還是noclustered索引,重建為clustered索引且沒有關聯分區(qū)方案時,分區(qū)表就變成了非分區(qū)表
4、普通表轉換為分區(qū)表,只要在該表創(chuàng)建一個clustered索引,并在clustered索引上使用分區(qū)方案即可。比如非分區(qū)表的字段1創(chuàng)建clustered索引且關聯分區(qū)方案時,該表轉換為分區(qū)表,且分區(qū)字段為字段1。
5、分區(qū)表轉換為普通表,如果有分區(qū)字段有索引則重建分區(qū)字段為clustered索引且不關聯分區(qū)方案即可,分區(qū)字段沒有索引的話則分區(qū)字段新建clustered索引且不關聯分區(qū)方案即可
6、普通表改成分區(qū)表或把分區(qū)表改成普通表,只能使用clustered索引來實現,因為有了clustered索引就是索引組織表,通過clustered索引的重建來實現表的重新分布。普通表變成分區(qū)表,把分區(qū)字段重建為clustered索引并關聯分區(qū)方案即可,分區(qū)表變成普通表,把分區(qū)字段重建為clustered索引不要關聯分區(qū)方案即可。
7、分區(qū)表創(chuàng)建唯一性約束,必須包含分區(qū)列
8、創(chuàng)建分區(qū)方案時,必須保證文件組數量匹配分區(qū)函數的分區(qū)范圍段,文件組名稱重復沒有關系,當然也可以使用ALL,指定一個文件組名稱,這樣所有的分區(qū)函數的分區(qū)范圍段數據都落到這一個文件組。
9、分區(qū)函數和分區(qū)方案是在一個個數據庫里面的,而不是面對整個實例的
10、分區(qū)表太大占用很多磁盤空間,delete了一些字段后大小還是沒變,這個時候進行分區(qū)合并或把分區(qū)表轉換為普通表,則大小會降下來
創(chuàng)建分區(qū)表的步驟
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、建立分區(qū)函數的示例,分區(qū)函數名為partfun1
CREATE PARTITION FUNCTION partfun1 (int)
AS RANGE LEFT FOR VALUES ('1000','2000','3000','4000')
--VALUES ('1000','2000','3000','4000')表明,將把表分為5個區(qū)了,是從根據表字段的值的大小來分區(qū),五個區(qū)分別是最小--1000,1000-2000,2000-3000,3000-4000,4000-最大
3、建立方案的例子,關聯分區(qū)函數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、建立分區(qū)表的示例
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、驗證分區(qū)表的數據
SELECT * FROM parttable1;
--返回分區(qū)表所有行
SELECT distinct $PARTITION.[partfun1](4) FROM parttable1;
--返回ID字段值為4的行屬于哪個分區(qū)
SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2
--返回第2個分區(qū)的所有行,ID就是分區(qū)字段ID
注意:不能因為SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2有結果就說明它是分區(qū)表,本文最后試驗7該表是非分區(qū)表了,但是執(zhí)行SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2還是有結果的
新增分區(qū)
1、為分區(qū)方案指定一個可以使用的文件組(新增分區(qū)方案的文件組)。
2、修改分區(qū)函數(新增分區(qū)函數的數據范圍)
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
--第一條語句,如果分區(qū)方案使用的ALL TO ([PRIMARY]),則這條語句不用執(zhí)行
--第二條語句新增一個分區(qū),范圍是4000-4500
--第三條語句驗證新增分區(qū)是否存在,是否存在行數
刪除\合并分區(qū)
ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')
就把1000-2000這個分區(qū),刪除了,合并成了1000-3000
--無法像oracle一樣執(zhí)行ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME;
刪除分區(qū)表及對應的文件組
刪除順序為:刪除分區(qū)表、刪除分區(qū)方案、刪除分區(qū)函數,最后刪除文件組,刪除完文件組后對應的文件也就刪除了
分區(qū)表轉換為普通表,普通表轉換為分區(qū)表的示例
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、在分區(qū)表上創(chuàng)建的唯一約束,必須包含分區(qū)列。
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、分區(qū)列id2新建clustered索引,parttable1還是分區(qū)表
create clustered index CI_prattable1_id2 on parttable1(id2);
3、分區(qū)列id2創(chuàng)建nonclustered索引,parttable1還是分區(qū)表
drop index CI_prattable1_id2 on parttable1;
create nonclustered index NCI_prattable1_id2 on parttable1(id2);
4、非分區(qū)列id列創(chuàng)建clustered索引,parttable1還是分區(qū)表,說明非分區(qū)列可以是cluster索引列
create clustered index CI_prattable1_id on parttable1(id);
5、分區(qū)列id2重建為nonclustered索引并且不使用分區(qū)方案,parttable1還是分區(qū)表
create nonclustered index NCI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];
6、分區(qū)列id2重建為clustered索引不加ON條件,parttable1還是分區(qū)表
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、分區(qū)列id2重建為clustered索引加上ON條件但不使用分區(qū)方案,parttable1變成了非分區(qū)表
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];
8、分區(qū)列id2重建為clustered索引并且使用分區(qū)方案,parttable1變成了分區(qū)表
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) on partschema1(Id2);
9、刪掉上面8的clustered索引后,parttable1還是分區(qū)表
drop index CI_prattable1_id2 on parttable1;
10、分區(qū)列id2新建為clustered索引并且不使用分區(qū)方案,parttable1變成了非分區(qū)表
create clustered index CI_prattable1_id2 on parttable1(id2) ON [PRIMARY];
11、刪掉上面10的clustered索引后,parttable1還是非分區(qū)表
drop index CI_prattable1_id2 on parttable1;
12、分區(qū)列id2新建為nonclustered索引,雖然使用了分區(qū)方案,還是非分區(qū)表
create nonclustered index NCI_prattable1_id2 on parttable1(id2) on partschema1(Id2);
分區(qū)表轉換為普通表,遇到分區(qū)字段是主鍵的情況下,則刪除主鍵約束,再對原來主鍵的字段重建cluster索引或重建為主鍵,但是都不關聯分區(qū)方案
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];
普通表轉換為分區(qū)表,要保留原來的主鍵的情況下,則刪除主鍵約束,再創(chuàng)建主鍵但不設為聚集索引,再創(chuàng)建新的聚集索引,在該聚集索引中使用分區(qū)方案
ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>
ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED(column) WITH (ON [PRIMARY];
--創(chuàng)建主鍵,但不設為聚集索引
CREATE CLUSTERED INDEX index_name ON Table_name(column) ON 分區(qū)方案(分區(qū)字段)
--創(chuàng)建一個新的聚集索引,在該聚集索引中使用分區(qū)方案
查詢某張分區(qū)表的總行數和大小,比如表為crm.EmailLog
exec sp_spaceused 'crm.EmailLog';
查詢某張分區(qū)表的信息,每個分區(qū)有多少行,比如表為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
查詢分區(qū)函數
select * from sys.partition_functions
查看分區(qū)架構
select * from sys.partition_schemes
感謝各位的閱讀,以上就是“SqlServer關于分區(qū)表的相關知識點有哪些”的內容了,經過本文的學習后,相信大家對SqlServer關于分區(qū)表的相關知識點有哪些這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關知識點的文章,歡迎關注!
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。