Tu Dang's blog: 2018

Monday, November 19, 2018

Join 2 bảng chính, phụ nhưng chỉ trả về 1 dòng đơn nhất cho mỗi dòng bảng chính

Sử dụng Cross Apply

https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row?rq=1

Friday, October 05, 2018

Switch In Và Switch Out Với Table Partitioning

Bài viết tham khảo tại: http://www.sqlviet.com/blog/switch-in-va-switch-out-voi-table-partitioning

Một trong những ưu điểm của phân đoạn bảng là bạn có thể dễ dàng loại bỏ một lượng lớn bản ghi ra khỏi bảng, cũng như dễ dàng nhập một lượng lớn bản ghi vào bảng, thay vì phải sử dụng các lệnh INSERT và DELETE.

Ví dụ, bạn có bảng BanHang chứa dữ liệu bán hàng qua nhiều năm. Nay nhận thấy dữ liệu của năm 2010 không còn cần dùng đến thường xuyên, bạn muốn chuyển dữ liệu này sang một bảng khác để giảm nhẹ bảng chính. Nếu không có partition, bạn phải dùng hai lệnh: INSERT vào bảng mới và DELETE trên bảng chính với điều kiện năm = 2010. Hai lệnh này đều rất tốn kém vì mỗi bản ghi được thêm/xóa đều được ghi vào transaction log. Khi bảng được phân đoạn theo năm, bạn có thể dễ dàng chuyển đoạn cho 2010 sang bảng mới. Thao tác này gọi là switch out. Vì đây là thao tác DDL, dữ liệu sẽ được di chuyển tức thì.

Một ví dụ khác, bạn làm việc ở trung tâm dữ liệu của một hội sở ngân hàng, tập hợp dữ liệu của tất cả các chi nhánh. Mỗi cuối tháng, bạn tiếp nhận dữ liệu giao dịch từ các chi nhánh và gộp chung vào một bảng giao dịch chính. Nếu không có partition, bạn cần chạy lệnh INSERT cho từng chi nhánh. Khỏi cần bàn thêm cũng thấy việc này rất tốn thời gian. Tuy nhiên, nếu bạn phân đoạn bảng chính theo chi nhánh (ví dụ phân đoạn theo mã chi nhánh), bạn có thể nạp dữ liệu của từng chi nhánh vào bảng chính thông qua thao tác switch in. Cũng giống như switch out, đây là thao tác DDL nên cần rất ít tài nguyên hệ thống và dữ liệu được nạp ngay tức thì.

Switch out

Tôi dùng lại ví dụ trong bài Table Partitioning – Các Khái Niệm Cơ Bản, và tạo một bảng phân đoạn theo giá trị của cột id, đoạn 1: id nhỏ hơn 1000; đoạn 2: id từ 1001 – 2000; đoạn 3: id từ 2001 – 3000; và đoạn 4: id từ 3001 trở lên.

CREATE DATABASE MyPartDB
GO
 
-- tạo filegroup
ALTER DATABASE MyPartDB ADD FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILEGROUP FG4
 
-- thêm data file vào mỗi filegroup
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F1', FILENAME = N'D:\DATA\MyPartDB_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F2', FILENAME = N'D:\DATA\MyPartDB_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F3', FILENAME = N'D:\DATA\MyPartDB_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F4', FILENAME = N'D:\DATA\MyPartDB_F4.ndf') TO FILEGROUP FG4
GO
 
USE MyPartDB
GO
 
-- tạo partition function
CREATE PARTITION FUNCTION MyPartFunc_1(INT) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)
 
-- tạo partition scheme
CREATE PARTITION SCHEME MyPartScheme_1 AS PARTITION MyPartFunc_1 TO (FG1, FG2, FG3, FG4)
 
-- tạo bảng
CREATE TABLE dbo.MyTablePart(
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)
) ON MyPartScheme_1(MyID)
 
--thêm dữ liệu vào bảng
INSERT INTO dbo.MyPartTable(MyData)
SELECT DISTINCT a.name
FROM master.SYS.syscolumns a

Nay tôi muốn chuyển toàn bộ dữ liệu của đoạn 1 sang một bảng khác. Tôi tạo một bảng mới và switch out đoạn 1 sang đó:

-- tạo bảng tạm
CREATE TABLE dbo.Tbl_1000 (
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)) ON FG1
GO
 
--Switch out. Boom!
ALTER TABLE dbo.MyPartTable SWITCH PARTITION 1 TO dbo.Tbl_1000
 
-- kiểm tra kết quả
SELECT * FROM  dbo.MyPartTable
 
SELECT * FROM  dbo.Tbl_1000

Vậy là dữ liệu đã được chuyển sang bảng Tbl_1000. Ở đây có vài điểm cần lưu ý:

Bảng tạm phải có cấu trúc giống hệt với bảng chính.
Bảng tạm phải được tạo trên cùng filegroup với đoạn mà bạn sắp chuyển sang. Ở ví dụ trên tôi cần chuyển đoạn 1 và tôi biết đoạn này nằm trên filegroup FG1 (vì sao tôi lại biết điều này?), nên tôi cũng cần tạo bảng tạm trên filegroup FG1.
Và tuy không thể hiện trong ví dụ trên, bảng tạm phải trống trước khi switch out. Khi bảng tạm có chứa dữ liệu, SQL Server sẽ báo lỗi chứ không xóa dọn dữ liệu.
Một điểm nữa là các nonclustered index trên bảng chính (MyPartTable) cũng phải được phân đoạn cùng với bảng. Khi đó index được gọi là aligned. Tôi sẽ viết kỹ hơn về aligned index trong một bài tới, tuy nhiên để bạn hình dung bạn hãy chèn lệnh sau đây vào ngay sau lệnh tạo bảng Table_1000:
```
CREATE UNIQUE INDEX ui_Tbl_1000 ON dbo.Tbl_1000(MyData)
```
Lệnh trên tạo một non-aligned index, và SQL Server sẽ từ chối thực hiện switch out và trả về một thông báo lỗi.

Switch in

Giả sử giờ tôi muốn chuyển lại dữ liệu trong bảng Tbl_1000 vào đoạn 1 của bảng MyPartTable. Các điều kiện áp dụng đối với switch out cũng vẫn cần được thỏa mãn (đồng thời đoạn cần được chuyển vào phải trống). Ngoài ra switch in còn đòi hỏi một điều kiện nữa, đó là cột trên bảng tạm tương ứng với partition key phải có chứa ràng buộc check theo đó nó chỉ cho phép chứa dải giá trị trùng khớp với đoạn tương ứng. Vì tôi muốn chuyển dữ liệu vào đoạn 1, và tôi biết đoạn 1 chỉ chứa các giá trị <= 1000, nên tôi cần tạo một ràng buộc check tương ứng trên bảng tạm rồi mới có thể switch in:

-- tạo ràng buộc
ALTER TABLE dbo.Tbl_1000 ADD CONSTRAINT ck_id CHECK(MyID <= 1000)
 
-- Switch in. Boom!
ALTER TABLE Tbl_1000 SWITCH TO MyPartTable PARTITION 1

Phiên bản áp dụng: SQL Server 2005 trở lên

Table Partitioning – Split và Merge

Bài viết tham khảo tại: http://www.sqlviet.com/blog/table-partitioning-split-va-merge

Khi định nghĩa partition function bạn phải chỉ rõ giá trị biên của từng đoạn và theo đó, số lượng các đoạn cũng được xác định. Qua thời gian sẽ đến lúc bạn cần định nghĩa thêm các đoạn mới cho dữ liệu được thêm vào sau này. Ví dụ bạn phân đoạn bảng bán hàng theo năm của ngày giao dịch, tại thời điểm ban đầu bạn chia bảng làm 3 đoạn cho các năm 2009 trở về trước, 2010, và 2011 trở đi. Tại một thời điểm nào đó trước năm 2012 bạn cần tạo thêm đoạn mới cho năm 2012, và tương tự sau này cho 2013… Thao tác này gọi là split (chia đoạn).
Ngược lại, bạn cũng có thể muốn hợp các đoạn chứa dữ liệu lại với nhau để giảm bớt số lượng đoạn cần quản lý, hoặc đáp ứng yêu cầu thay đổi về nghiệp vụ (ví dụ Hà Tây nhập vào Hà nội). Với bảng bán hàng ở trên, có thể bạn muốn hợp hai đoạn 2009 và 2010 để gom tất cả dữ liệu cũ từ năm 2010 trở về trước vào một đoạn. Thao tác này gọi là merge (hợp đoạn). Lưu ý là partition merge khác với lệnh MERGE được đưa vào từ bản 2008.
Setup
Tôi dùng lại database trong bài Switch In Và Switch Out Với Table Partitioning và tạo thêm các đối tượng mới:

USE MyPartDB
GO
CREATE PARTITION FUNCTION MyPartFuncD(DATETIME) AS RANGE RIGHT FOR VALUES ('2009-01-01', '2010-01-01', '2011-01-01')
GO
CREATE PARTITION SCHEME MyPartSchemeD AS PARTITION MyPartFuncD TO (FG1, FG2, FG3, FG4)
GO
CREATE TABLE dbo.BanHang(
BanHang_ID INT IDENTITY,
Ngay DATETIME,
MaSP VARCHAR(10),
SoLuong INT,
ThanhTien INT,
CONSTRAINT PK_BanHang PRIMARY KEY (BanHang_ID,Ngay)
) ON MyPartSchemeD(Ngay)
GO
 
--thêm dữ liệu vào bảng
INSERT INTO dbo.BanHang(Ngay,MaSP,SoLuong,ThanhTien)
SELECT '2008-03-25 19:26:12','100',2,500000 
UNION ALL
SELECT '2009-06-12 07:14:27','200',1,300000 
UNION ALL
SELECT '2010-01-22 10:01:44','300',3,600000 
UNION ALL
SELECT '2011-10-26 12:06:42','400',1,1000000 
UNION ALL
SELECT '2012-02-17 15:32:16','500',5,400000 
GO
--kiểm tra dữ liệu lưu vào các đoạn
SELECT OBJECT_ID, index_id, partition_number, ROWS
FROM SYS.partitions WHERE OBJECT_NAME(OBJECT_ID)='BanHang'

------------------------------
object_id   partition_number   rows
229575856        1       1
229575856        2       1
229575856        3       1
229575856        4       2

Như vậy bảng được lưu trữ đúng như mong đợi: bản ghi 2008 vào đoạn 1, bản ghi 2009 vào đoạn 2, bản ghi 2010 vào đoạn 3, còn hai bản ghi 2011 và 2012 vào đoạn 4. Số bản ghi của mỗi đoạn (cột “rows”) phản ánh điều này.

Split
Nay bạn muốn tạo thêm một đoạn cho 2012, bạn cần làm theo các bước như sau:

--bước 1: tạo thêm 1 filegroup mới để chứa đoạn sắp được tạo
ALTER DATABASE MyPartDB ADD FILEGROUP FG5
GO 
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F5', FILENAME = N'D:\DATA\MyPartDB_F5.ndf') TO FILEGROUP FG5
GO
--bước 2: mở rộng thêm partition scheme
ALTER PARTITION SCHEME MyPartSchemeD NEXT USED FG5
GO
--bước 3: định nghĩa thêm giá trị biên trong partition function
ALTER PARTITION FUNCTION MyPartFuncD() SPLIT RANGE ('2012-01-01')
GO
--Thêm một số bản ghi mới và kiểm tra dữ liệu lưu vào các đoạn
INSERT INTO dbo.BanHang(Ngay,MaSP,SoLuong,ThanhTien)
SELECT '2012-03-12 14:25:12','100',3,750000 
UNION ALL
SELECT '2012-06-21 07:24:12','200',1,300000 
GO
SELECT OBJECT_ID, index_id, partition_number, ROWS
FROM SYS.partitions WHERE OBJECT_NAME(OBJECT_ID)='BanHang'

object_id   partition_number   rows
229575856        1       1
229575856        2       1
229575856        3       1
229575856        4       1
229575856        5       3

Lưu ý ở bước 2, bạn yêu cầu đoạn tiếp theo sẽ sử dụng filegroup FG5 qua mệnh đề “NEXT USED FG5″. Ở bước 3, bạn tạo thêm một giá trị biên trong partition function, theo đó miền từ 2011-01-01 đến ngày tận thế được cắt tại 2012-01-01. Do đó đoạn 4 giờ được điều chỉnh để chứa dải giá trị từ 2011-01-01 đến trước 2012-01-01, và đoạn mới chứa dải từ 2012-01-01 đến ngày tận thế.
Vậy là đoạn mới đã được tạo (partition number 5), bản ghi 2012 cũ được chuyển vào đây và hai bản ghi 2012 mới cũng được lưu vào đoạn này.

Merge
Việc hợp đoạn đơn giản hơn, để hợp đoạn 2009 vào 2010 bạn chỉ cần thực hiện lệnh sau

ALTER PARTITION FUNCTION MyPartFuncD() merge RANGE ('2009-01-01')

Hiểu một cách đơn giản là xóa đi giá trị biên 2009-01-01 và do đó tất cả các giá trị từ trước đến 2010-01-01 được lưu chung vào một đoạn. Vậy đoạn nào được dùng? Trước khi hợp, đoạn 1 lưu vào filegroup FG1, đoạn 2 lưu vào filegroup FG2. Sau khi hợp, đoạn 1 và đoạn 2 được gộp chung vào thành đoạn 1. Vậy đoạn 1 mới bây giờ được lưu ở FG1 hay FG2? Xin nhường cho bạn tìm câu trả lời.

Lưu ý
Không giống như Switch in và Switch out vốn chỉ thay đổi metadata trong database, Split và Merge kéo theo sự di chuyển của dữ liệu. Các bản ghi sẽ được chuyển sang đoạn mới thích hợp với giá trị partition key của nó. Trong ví dụ split ở trên, bản ghi 2012 ban đầu ở đoạn 4 được chuyển sang đoạn 5; còn ở ví dụ merge hai đoạn 2009 và 2010, các bản ghi ở một đoạn được chuyển qua đoạn kia. Điều này có thể gây kéo dài thời gian thực hiện khi bảng có lượng bản ghi lớn.

Index Partitioning

Bài viết tham khảo tại: http://www.sqlviet.com/blog/index-partitioning

Với một bảng đã được phân đoạn, mỗi index được tạo ra theo mặc định được phân đoạn theo giống như bảng. Ví dụ bảng được phân đoạn theo năm của ngày giao dịch thì index cũng được phân đoạn theo cách đó. Index khi đó được gọi là aligned với bảng. Tuy nhiên bạn cũng có thể không phân đoạn cho index và để nó nguyên một khối, hoặc phân đoạn theo cách khác. Khi đó index được gọi là non-aligned.
Bạn hãy hình dung bảng như một cái bánh tét, và index là sợi lạt buộc xung quanh. Khi phân đoạn, chiếc bánh được cắt thành từng lát. Nếu sợi lạt cũng được cắt theo và mỗi sợi con giờ buộc xung quanh từng lát bánh, sợi lạt được gọi là aligned với chiếc bánh. Nếu sợi lạt không được cắt và vẫn bao xung quanh cả chiếc bánh to, sợi lạt được gọi là non-aligned với chiếc bánh.
SQL Server luôn ưu tiên phân đoạn index để nó aligned với bảng và luôn cố gắng phân đoạn khi có thể, vì nó đem lại nhiều thuận lợi cho các tác vụ trên bảng. Tuy nhiên khi đi vào chi tiết từng loại index, cách cư xử này được thể hiện qua những sắc thái khác nhau, ta sẽ xem xét kỹ hơn ở dưới đây.
Non-clustered Index Khi bạn tạo nonclustered index mà không chỉ định filegroup, SQL Server sẽ mặc định tạo index trên partition scheme của bảng, nghĩa là phân đoạn index cùng với bảng:

USE PartTest
GO
CREATE TABLE dbo.BanHang(
BanHang_ID INT IDENTITY,
NgayGiaoDich DATETIME,
MaSP INT,
SoLuong INT,
ThanhTien INT
) ON PScheme_NGD(NgayGiaoDich)
GO
CREATE INDEX CI_BanHang_MaSP ON dbo.BanHang(MaSP)
GO

Bạn sẽ thấy index cũng được phân đoạn bằng partition scheme PScheme_NGD:

--code kiểm tra xem index có được phân đoạn
SELECT i.name AS IndexName, i.type_desc, ps.name AS PartitionName
FROM SYS.indexes i
JOIN SYS.partition_schemes ps
 ON ps.data_space_id = i.data_space_id 
WHERE i.name = 'IX_BanHang_MaSP'

Đồng thời index cũng tự động được thêm trường partition key là NgayGiaoDich, mặc dù bạn chỉ tạo index trên trường MaSP:

--code để liệt kê các cột chứa trong index
SELECT ind.name AS Index_Name, col.name AS Column_Name
FROM SYS.indexes ind 
INNER JOIN SYS.index_columns ic 
    ON  ind.OBJECT_ID = ic.OBJECT_ID and ind.index_id = ic.index_id 
INNER JOIN SYS.COLUMNS col 
    ON ic.OBJECT_ID = col.OBJECT_ID and ic.column_id = col.column_id 
WHERE ind.name = 'IX_BanHang_MaSP'

Ở ví dụ ở trên, index là non-unique. Khi bạn tạo unique index, tình hình sẽ khác đi một chút. SQL Server không thể tự động thêm trường partition key vào index vì như thế sẽ làm thay đổi tính chất của index. Ví dụ khi bạn tạo unique index trên trường BangHang_ID, trường này chỉ được chứa các giá trị duy nhất trong toàn bảng. Nếu SQL Server thêm trường NgayGD vào index, index đó sẽ chỉ đảm bảo tính duy nhất cho cặp BanHang_ID+NgayGD và khi đó BanHang_ID vẫn có thể chứa các giá trị lặp lại miễn là khác NgayGD. Vì thế, thay vì tự động sửa index, SQL Server sẽ kiểm tra xem index có chứa trường partition key hay không, nếu không thì nó báo lỗi và không cho tạo index. Ví dụ, lệnh tạo index sau sẽ báo lỗi:

CREATE UNIQUE INDEX UI_BanHang_BanHang_ID ON dbo.BanHang(BanHang_ID)

Msg 1908, Level 16, State 1, Line 1

Column ‘NgayGiaoDich’ is partitioning column of the index ‘UI_BanHang_BanHang_ID’. Partition columns for a unique index must be a subset of the index key.

Để khắc phục, bạn cần chỉ định rõ filegroup cho index:

CREATE UNIQUE INDEX UI_BanHang_BanHang_ID ON dbo.BanHang(BanHang_ID) ON [PRIMARY]

Khi đó index được tạo ra trên PRIMARY filegroup thay vì trên partition scheme PScheme_NGD, nói cách khác là nó không được phân đoạn theo bảng.

Clustered Index Vì clustered index chính là bảng nên nó đương nhiên phải được phân đoạn cùng với bảng. Cũng giống như trên, nếu index là non-unique SQL Server sẽ tự động bổ sung partition key vào index và phân đoạn nó. Còn nếu index là unique, trường partition key bắt buộc phải có mặt trong index nếu không sẽ bị lỗi. Tuy nhiên lưu ý là khi bạn chỉ định filegroup cho index, toàn bộ bảng sẽ được kéo sang filegroup mới và bảng sẽ không còn được phân đoạn nữa. Vì thế bạn chỉ nên làm điều này khi chủ định thay đổi cấu trúc lưu trữ vật lý của bảng.
Bảng dưới đây tóm tắt các loại index và các tình huống tương ứng:

Loại Index	Không chỉ định filegroup	Chỉ định filegroup
*Non-Clustered non-unique*
- Không chứa partition key	aligned, partition key được thêm vào index	not partitioned, non-aligned
- Chứa partition key	partitioned, aligned	not partitioned, non-aligned
*Non-clustered unique*
- Không chứa partition key	Lỗi, cần thêm partition key vào index	not partitioned, non-aligned
- Chứa partition key	partitioned, aligned	not partitioned, non-aligned
*Clustered non-unique*
- Không chứa partition key	partitioned, aligned, partition key được thêm vào index
- Chứa partition key	partitioned, aligned
*Clustered unique*
- Không chứa partition key	Lỗi, cần thêm partition key vào index
- Chứa partition key	partitioned, aligned

Như vậy bạn thấy index luôn được phân đoạn cùng với bảng chỉ trừ trường hợp với non-clustered index và bạn chỉ định filegroup cho nó. Vì sao SQL Server luôn ưu tiên phân đoạn index? Lý do là vì những lợi ích sau:
- Bạn có thể backup/restore từng đoạn của bảng và index cũng được backup/restore theo.
- Bạn có thể rebuild hoặc reorganize lại index trên từng đoạn mà bạn thấy cần phải rebuild hoặc reorganize
- Bạn có thể thực hiện các thao tác trên từng đoạn dễ dàng như switch/switch out hoặc split/merge
- Bộ optimizer khi sử dụng aligned index có thể áp dụng loại bỏ các đoạn không chứa dữ liệu cần cho câu lệnh
Hãy liên hệ bảng với chiếc bánh tét như đã nêu ở đầu bài, tất cả các thao tác kể trên có thể thực hiện dễ dàng nếu như từng lát bánh có một sợi lạt riêng. Còn nếu vẫn còn một sợi lạt buộc vòng tất cả các lát bánh với nhau (non-aligned index) thì các thao tác này sẽ bị sợi lạt đó làm vướng và không thực hiện được.

Vậy trong trường hợp nào vẫn cần non-aligned index? Khi bạn cần tạo unique index (non-clustered) trên một cột để đảm bảo tính duy nhất cho nó, bạn không có cách nào khác là tạo index trên một filegroup khác và để nó non-aligned với bảng.

Wednesday, October 03, 2018

Partition Bảng Đã Có Sẵn

Bài viết tham khảo tại:http://www.sqlviet.com/blog/partition-bang-da-co-san

Partition Bảng Đã Có Sẵn

Vũ Huy Tâm

Trong loạt bài về phân đoạn bảng và index, tôi đã giới thiệu về kiến trúc phân đoạn của SQL Server. Các ví dụ nêu trong các bài đó đều sử dụng bảng được tạo lần đầu và áp dụng phân đoạn lên nó. Một câu hỏi thường gặp là, làm thế nào để phân đoạn một bảng đã có sẵn dữ liệu? Bảng này có thể chưa được phân đoạn, hoặc đã được phân đoạn theo một cách khác, nay bạn muốn phân đoạn lại theo cách hợp lý hơn.
Có một cách làm thực ra rất đơn giản mà nếu bạn để ý một tẹo là thấy ngay, đó là trong SQL Server clustered index chính là bảng, nói cách khác là bảng được lưu trữ thành một cây index và cây này chính là clustered index. Vì vậy để phân đoạn bảng bạn chỉ việc phân đoạn clustered index của nó, bằng cách tạo lại clustered index trên partition scheme.
Trong trường hợp phổ biến, clustered index thường đi liền với primary key. Ở ví dụ dưới, giả sử bạn đã có bảng BanHang với primary key và clustered index trên trường ID, bảng này không được phân đoạn.

CREATE TABLE dbo.BanHang(ID INT IDENTITY NOT NULL, 
NgayGD DATETIME NOT NULL, 
MaSP INT NOT NULL,
SoLuong INT NOT NULL, 
Gia INT NOT NULL,
CONSTRAINT PK_BanHang PRIMARY KEY CLUSTERED (ID)
)

Nay bạn muốn phân đoạn bảng, có hai khả năng xảy ra:
(1) bạn phân đoạn bảng trên trường ID, nghĩa là vẫn giữ nguyên định nghĩa của primary key và clustered index (giữ nguyên trường cũ) chỉ muốn phân đoạn nó ra, dựa vào partition scheme PScheme_ID. Bạn chỉ việc tạo lại index như sau:

CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID) 
WITH (DROP_EXISTING = ON)
ON PScheme_ID(ID)

Điểm chính của đoạn lệnh trên là mệnh đề “WITH (DROP_EXISTING = ON)”, nó sẽ tự động xây dựng lại index thay vì bạn phải xóa đi rồi tạo lại.
(2) Bạn muốn phân đoạn bảng trên một trường khác, ví dụ NgayGD. Vì trường partition key cần phải có mặt trong clustered index, bạn cần tạo lại clustered index (và primary key) trong đó phải chứa thêm trường NgayGD. Vì định nghĩa của primary key và clustered index khác so với trước, bạn không thể dùng WITH (DROP_EXISTING = ON) được nữa. Nếu cố tình chạy đoạn lệnh này:

CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID,NgayGD) 
WITH (DROP_EXISTING = ON)
ON PScheme_NGD(NgayGD)

Bạn sẽ gặp lỗi:
Cannot recreate index ‘PK_BanHang’. The new index definition does not match the constraint being enforced by the existing index.
Thay vào đó, bạn cần xóa đi và tạo lại:

ALTER TABLE BanHang DROP CONSTRAINT PK_BanHang
GO
CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID,NgayGD) 
ON PScheme_NGD(NgayGD)
--hoặc
ALTER TABLE dbo.BanHang ADD CONSTRAINT PK_BanHang PRIMARY KEY CLUSTERED (ID,NgayGD) ON PScheme_NGD(NgayGD)

Lý do tại sao cần 2 trường ID và NgayGD trong index là vì ID cần thiết để duy trì tính duy nhất, và NgayGD cần có mặt để làm partition key.
Trong trường hợp ít phổ biến hơn, clustered index không đi kèm với primary key, hay nói tổng quát là nó đứng một mình không gắn với một constraint nào. Khi đó bạn có thể tạo lại clustered index với bổ sung thêm trường mới mà vẫn dùng được mệnh đề “WITH (DROP_EXISTING = ON)”:

CREATE TABLE dbo.BanHang(ID INT IDENTITY NOT NULL, 
NgayGD DATETIME NOT NULL, 
MaSP INT NOT NULL,
SoLuong INT NOT NULL, 
Gia INT NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX CI_BanHang ON dbo.BanHang(ID) 
GO
CREATE UNIQUE CLUSTERED INDEX CI_BanHang ON dbo.BanHang(ID,NgayGD) 
WITH (DROP_EXISTING = ON)
ON PScheme_NGD(NgayGD)

Table Partitioning – Các Khái Niệm Cơ Bản

Bài viết tham khảo tại:http://www.sqlviet.com/blog/table-partitioning-cac-khai-niem-co-ban

Table Partitioning – Các Khái Niệm Cơ Bản

Vũ Huy Tâm

Bài Table Partitioning Trong SQL Server giới thiệu về kỹ thuật phân đoạn của SQL Server và cũng giới thiệu qua các khái niệm xung quanh kỹ thuật này, như partition function, partition schema, filegroup. Bài viết này đề cập chi tiết hơn các khái niệm trên để giúp bạn hiểu thêm về kiến trúc partitioning của SQL Server.

Partition Function
Bước đầu tiên bạn cần làm khi thực hiện phân đoạn là tạo partition function để định nghĩa giá trị biên của các đoạn. Partition function không giống với các hàm UDF thông thường (bạn không thể gọi trực tiếp), mà chỉ dùng vào mục đích phân đoạn. Nó đưa ra định nghĩa về mặt logic, rằng mỗi đoạn chứa dải giá trị từ đâu đến đâu chứ không qui định các đoạn được lưu trữ như thế nào, cũng như tên trường dùng để phân đoạn là gì.
Ví dụ 1: phân đoạn dựa vào dãy số

CREATE DATABASE MyPartDB
GO
USE MyPartDB
GO
CREATE PARTITION FUNCTION MyPartFunc_1(INT) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)

Partition		Partition 1	Partition 2	Partition 3	Partition 4
Dải giá trị		giá trị <= 1000	1000< giá trị <= 2000	2000 < giá trị <= 3000	3000 < giá trị

Ví dụ 2: phân đoạn theo năm

CREATE PARTITION FUNCTION MyPartFunc_2(DATETIME) AS RANGE RIGHT FOR VALUES ('2010-01-01', '2011-01-01')

Partition		Partition 1	Partition 2	Partition 3
Dải giá trị		giá trị < 01/01/2010	01/01/2010<= giá trị < 01/01/2011	01/01/2011 <= giá trị

Bạn có thể thấy ở ví dụ 1 chỉ có ba giá trị liệt kê trong hàm để tạo ra bốn đoạn. Tương tự ở ví dụ 2, chỉ có hai giá trị được dùng để tạo ra ba đoạn. Việc phân đoạn bảng giống như khi bạn cắt một sợi dây, trong đó partition function định nghĩa vị trí cho các nhát cắt, và để cắt sợi dây làm n đoạn bạn chỉ cần n-1 nhát cắt.
Trong hai ví dụ trên bạn có để ý đến từ khóa “LEFT” ở hàm MyPartFunc_1 và “RIGHT” ở hàm MyPartFunc_2? Mỗi partition function phải thuộc một trong hai kiểu, LEFT hoặc RIGHT. Như ở ví dụ trên, hàm MyPartFunc_1 có kiểu LEFT và hàm MyPartFunc_2 có kiểu RIGHT. Hai kiểu hàm này liên quan đến việc qui định giá trị biên thuộc về đoạn nào, đoạn bên trái hay bên phải. Với hàm kiểu LEFT, giá trị biên thuộc về đoạn phía trước nó (bên trái), như với hàm MyPartFunc_1 các giá trị biên 1000, 2000, và 3000 luôn thuộc về đoạn phía bên trái của chúng. Ngược lại với hàm kiểu RIGHT, các giá trị biên luôn thuộc về đoạn tiếp theo, tức là phía bên phải (hãy quan sát dải giá trị của các đoạn trong hàm MyPartFunc_2).
Việc phân chia làm hai loại hàm như vậy giúp bạn định nghĩa dải giá trị cho đúng với yêu cầu. Khi dải giá trị là các số rời rạc (discrete) như kiểu INT thì bạn chỉ cần một kiểu hàm và có thể chuyển đổi qua lại dễ dàng bằng cách tăng/giảm giá trị biên đi 1, do đó kiểu hàm không quá quan trọng. Như với ví dụ 1 ở trên, tôi có thể viết lại thành hàm kiểu RIGHT như sau:

CREATE PARTITION FUNCTION MyPartFunc_3(INT) AS RANGE RIGHT FOR VALUES (1001, 2001, 3001)

Tuy nhiên với kiểu dữ liệu có dải giá trị liền nhau, như DATETIME hay FLOAT, vấn đề trở nên phức tạp hơn và bạn cần chọn kiểu hàm cho đúng. Như ví dụ 2 ở trên, yêu cầu đặt ra là các giá trị thuộc về năm 2009 trở về trước vào một đoạn, và thời khắc đầu tiên của năm 2010 đã thuộc về đoạn sau, do đó dùng hàm RIGHT là thích hợp. Nếu dùng hàm LEFT bạn cần chuyển qua đại để thế này:

CREATE PARTITION FUNCTION MyPartFunc_4(DATETIME) AS RANGE LEFT FOR VALUES ('2009-12-31 23:59:59.999', '2010-12-31 23:59:59.999')

trong đó ’2009-12-31 23:59:59.999′ và ’2010-12-31 23:59:59.999′ tương ứng là thời điểm cuối cùng của năm 2009 và 2010 theo độ phân giải thời gian của hệ thống (các giá trị trên đây chỉ là ví dụ, tôi không rõ SQL Server chính xác đến bao nhiêu phần giây).

Partition Scheme
Sau khi định nghĩa dải giá trị cho các đoạn, việc tiếp theo là tạo partition scheme để định nghĩa không gian lưu trữ cho chúng. Partition scheme ánh xạ từng đoạn đã được định nghĩa trong partition function vào các filegroup (Các filegroup này cần được tạo trước khi tạo partition scheme):

CREATE PARTITION SCHEME MyPartScheme_1 AS PARTITION MyPartFunc_1 TO (FG1, FG2, FG3, FG4)

Partition	Partition 1	Partition 2	Partition 3	Partition 4
Dải giá trị	giá trị <= 1000	1000< giá trị <= 2000	2000 < giá trị <= 3000	3000 < giá trị
Filegroup	FG1	FG2	FG3	FG4

Về nguyên tắc bạn có thể gán tất cả các đoạn vào một filegroup, nhưng làm như vậy là vô hiệu hóa nhiều lợi điểm của việc phân đoạn. Trên thực tế bạn nên giành riêng mỗi filegroup cho một đoạn.

Filegroup
Filegroup được đưa vào SQL Server đã lâu, từ trước khi có partitioning. Khái niệm filegroup tương tự như folder trong windows, chỉ là một tên logic để nhóm các data file chứ không bản thân nó chứa dữ liệu. Filegroup chứa một hoặc nhiều data file và các data file này mới thực chứa dữ liệu. Khi mới tạo database, một filegroup có tên PRIMARY tự động được tạo và chứa data file chính (.mdf). Tuy nhiên bạn không nên dùng PRIMARY filegroup vào việc phân đoạn mà nên tạo filegroup mới.

-- tạo filegroup
ALTER DATABASE MyPartDB ADD FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILEGROUP FG4
 
-- thêm data file vào mỗi filegroup
 
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F1', FILENAME = N'D:\DATA\MyPartDB_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F2', FILENAME = N'D:\DATA\MyPartDB_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F3', FILENAME = N'D:\DATA\MyPartDB_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F4', FILENAME = N'D:\DATA\MyPartDB_F4.ndf') TO FILEGROUP FG4

Phân đoạn bảng
Sau khi các bước trên hoàn tất, giờ bạn có thể tạo bảng và đồng thời phân đoạn nó:

CREATE TABLE dbo.MyTablePart(
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)
) ON MyPartScheme_1(MyID)

Trong ví dụ trên, việc phân đoạn được gói gọn ở mệnh đề “ON MyPartScheme_1(MyID)”, và bảng được phân đoạn thông qua partition scheme MyPartScheme_1 dựa vào trường MyID. Khi đó trường MyID được gọi là partition key. Nếu bạn đã quen với việc dùng mệnh đề “ON some_filegroup_name” khi tạo bảng để chỉ định filegroup cho nó, bạn sẽ thấy việc phân đoạn chỉ đơn giản là thay tên filegroup bằng một partition scheme. Nói cách khác là thay vì chỉ định lưu dữ liệu vào một filegroup cụ thể, thì nay vào một partition scheme, và thông qua đó dữ liệu được phân đoạn.

Một vài lưu ý
- Ở đây bạn có thể thấy một sự phân cấp rất rõ ràng:
Trong một database bạn có thể tạo nhiều partition function.
Mỗi partition function có thể được dùng cho nhiều partition scheme
Đến lượt mỗi partition scheme lại được dùng cho nhiều bảng.

- Một bạn hỏi qua email muốn phân đoạn dựa vào mã tỉnh thành, ví dụ Hà nội (HN) vào một đoạn, Sài gòn (SG) vào một đoạn, và các tỉnh thành khác vào một đoạn. Bạn không thể áp dụng trực tiếp partition function lên các giá trị này vì chúng không tạo ra một dải tăng tuần tự. Ví dụ Bình dương (BD) đứng trước Hà nội, còn Quảng ninh (QN) đứng sau Hà nội do đó không thể nằm chung trong một đoạn. Trong trường hợp này bạn cần tạo một ID cho mỗi tỉnh thành ở dạng số (ví dụ HN=1, SG=2, else=3,4,5…) và phân đoạn trên ID này.
- Việc phân đoạn chỉ có thể được thực hiện trên một dải giá trị duy nhất. Có những trường hợp bạn muốn phân đoạn dựa vào 2 hoặc nhiều trường, ví dụ phân đoạn hóa đơn bán hàng theo năm và trong mỗi năm tiếp tục phân chia các đơn hàng có trị giá hơn 1 triệu vào một đoạn và nhỏ hơn 1 triệu vào một đoạn. Khi đó bạn cần tạo một computed field đại diện cho hai trường kia và phân đoạn dựa vào computed field này.

Phiên bản áp dụng: SQL Server 2005 trở lên