SQL Server 2005 đã giới thiệu một số thành phần mới giúp tăng cường khả năng xử lý dữ liệu logic. Các tính năng này cung cấp nhiều cách linh hoạt hơn để thao tác và phân tích dữ liệu, bao gồm:

• Các toán tử bảng: APPLY, PIVOT, UNPIVOT
• Mệnh đề OVER mới
• Các toán tử tập hợp mới: EXCEPT, INTERSECT

Toán tử APPLY

Toán tử APPLY bao gồm CROSS APPLY và OUTER APPLY. Sự khác biệt giữa chúng tương tự như sự khác biệt giữa INNER JOIN và LEFT OUTER JOIN. Mặc dù có vẻ giống JOIN, APPLY giải quyết một số hạn chế quan trọng và có cách thức hoạt động khác:

1. Khi sử dụng JOIN với một hàm giá trị bảng (table-valued function), nếu hàm đó cần tham chiếu các cột từ bảng bên trái làm tham số, thì thao tác JOIN thông thường sẽ gây lỗi. APPLY được tạo ra để giải quyết vấn đề này, cho phép truyền tham số từ mỗi hàng của bảng bên trái vào hàm giá trị bảng bên phải.
2. Trong khi JOIN thường thực hiện tích Đề-các (cross product) trước rồi mới lọc, APPLY hoạt động theo cơ chế "áp dụng từng hàng". Nó duyệt qua từng hàng của bảng bên trái và thực thi biểu thức bảng (table expression) bên phải cho mỗi hàng đó, sau đó kết hợp các kết quả. Điều này đặc biệt hữu ích khi biểu thức bên phải phụ thuộc vào các giá trị từ bảng bên trái.

PIVOT và UNPIVOT

Hai toán tử này được thiết kế để chuyển đổi cấu trúc dữ liệu từ dạng "dài" sang dạng "rộng" và ngược lại. Chúng giải quyết bài toán phổ biến trong thiết kế bảng, nơi chúng ta có thể lưu trữ thuộc tính của đối tượng theo hai cách chính:

• Dạng "rộng" (Wide/Column-oriented): [Đối tượng, Thuộc tính 1, Thuộc tính 2, ...]
  • Ưu điểm: Dễ dàng truy xuất tất cả thuộc tính của một đối tượng chỉ trong một hàng.
  • Nhược điểm: Việc thêm hoặc sửa đổi thuộc tính yêu cầu thay đổi cấu trúc bảng.
• Dạng "dài" (Tall/Row-oriented hoặc EAV - Entity-Attribute-Value): [Đối tượng, Tên thuộc tính, Giá trị thuộc tính]
  • Ưu điểm: Linh hoạt khi mở rộng thuộc tính mới mà không cần sửa đổi cấu trúc bảng.
  • Nhược điểm: Để lấy tất cả thuộc tính của một đối tượng có thể cần truy vấn nhiều hàng.

PIVOT giúp chuyển đổi dữ liệu từ dạng "dài" sang dạng "rộng", biến các giá trị duy nhất từ một cột thành các cột mới. Ngược lại, UNPIVOT chuyển đổi dữ liệu từ dạng "rộng" sang dạng "dài", biến các cột thành các hàng mới.

Ví dụ về PIVOT: Chuyển đổi bảng điểm học sinh từ dạng "dài" sang dạng "rộng".

Đầu tiên, tạo bảng chứa dữ liệu điểm:

CREATE TABLE DiemThiHocKy
(
    TenSinhVien NVARCHAR(50),
    MonHoc NVARCHAR(50),
    DiemSo INT
);
GO

Thêm dữ liệu mẫu vào bảng:

INSERT INTO DiemThiHocKy VALUES (N'An', N'Toán', 85);
INSERT INTO DiemThiHocKy VALUES (N'An', N'Lý', 70);
INSERT INTO DiemThiHocKy VALUES (N'An', N'Văn', 75);
INSERT INTO DiemThiHocKy VALUES (N'Bình', N'Toán', 90);
INSERT INTO DiemThiHocKy VALUES (N'Bình', N'Hóa', 65);
GO

Sử dụng PIVOT để nhận kết quả dạng bảng tổng hợp:

SELECT
    TenSinhVien,
    Toán,
    Lý,
    Văn,
    Hóa
FROM
    DiemThiHocKy
PIVOT
(
    SUM(DiemSo) FOR MonHoc IN ([Toán], [Lý], [Văn], [Hóa])
) AS BangTongHopDiem;

Kết quả sẽ hiển thị điểm của từng môn học dưới dạng cột:

TenSinhVien | Toán | Lý   | Văn  | Hóa
------------|------|------|------|------
An          | 85   | 70   | 75   | NULL
Bình        | 90   | NULL | NULL | 65

Mệnh đề OVER

Mệnh đề OVER được sử dụng rộng rãi với các hàm xếp hạng (ranking functions) và hàm tổng hợp (aggregate functions) để thực hiện các phép tính trên một tập hợp các hàng liên quan đến hàng hiện tại (gọi là một "cửa sổ").

Những điểm cần lưu ý:

1. Sử dụng với hàm xếp hạng: Các hàm như ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() chỉ có thể được sử dụng trong danh sách SELECT hoặc mệnh đề ORDER BY.
2. PARTITION BY và ORDER BY:
   • PARTITION BY chia tập hợp kết quả thành các nhóm (phân vùng) độc lập, và hàm sẽ được áp dụng cho từng phân vùng.
   • ORDER BY sắp xếp các hàng trong mỗi phân vùng (hoặc toàn bộ tập kết quả nếu không có PARTITION BY) để xác định thứ tự tính toán hoặc xếp hạng.

Khác với GROUP BY (gộp các hàng lại và trả về một hàng cho mỗi nhóm), OVER mệnh đề tính toán giá trị cho mỗi hàng riêng lẻ dựa trên một "cửa sổ" các hàng. Điều này cho phép chúng ta thực hiện các phép tính như tổng lũy kế, trung bình động, hoặc xếp hạng mà vẫn giữ lại tất cả các hàng dữ liệu gốc.

Ví dụ về mệnh đề OVER: Tính tổng tiền lũy kế theo từng nhân viên trong các giao dịch bán hàng.

Tạo bảng giao dịch bán hàng:

CREATE TABLE GiaoDichBanHang
(
    MaGiaoDich INT PRIMARY KEY,
    NgayGiaoDich DATE,
    MaNhanVien INT,
    TongTien DECIMAL(10, 2)
);
GO

Chèn dữ liệu mẫu:

INSERT INTO GiaoDichBanHang VALUES (101, '2023-01-01', 1, 100.00);
INSERT INTO GiaoDichBanHang VALUES (102, '2023-01-02', 2, 150.00);
INSERT INTO GiaoDichBanHang VALUES (103, '2023-01-03', 1, 50.00);
INSERT INTO GiaoDichBanHang VALUES (104, '2023-01-04', 1, 200.00);
INSERT INTO GiaoDichBanHang VALUES (105, '2023-01-05', 2, 75.00);
GO

Sử dụng OVER để tính tổng tiền lũy kế cho mỗi nhân viên:

SELECT
    MaGiaoDich,
    NgayGiaoDich,
    MaNhanVien,
    TongTien,
    SUM(TongTien) OVER (PARTITION BY MaNhanVien ORDER BY NgayGiaoDich) AS TongTienLuyKe
FROM
    GiaoDichBanHang
ORDER BY
    MaNhanVien, NgayGiaoDich;

Kết quả sẽ hiển thị tổng tiền lũy kế của từng nhân viên theo ngày:

MaGiaoDich | NgayGiaoDich | MaNhanVien | TongTien | TongTienLuyKe
-----------|--------------|------------|----------|--------------
101        | 2023-01-01   | 1          | 100.00   | 100.00
103        | 2023-01-03   | 1          | 50.00    | 150.00
104        | 2023-01-04   | 1          | 200.00   | 350.00
102        | 2023-01-02   | 2          | 150.00   | 150.00
105        | 2023-01-05   | 2          | 75.00    | 225.00

Toán tử tập hợp EXCEPT và INTERSECT

Hai toán tử này cho phép thực hiện các phép toán tập hợp chuẩn trên các tập hợp kết quả của các truy vấn SELECT khác nhau:

• EXCEPT: Trả về tất cả các hàng duy nhất từ tập hợp kết quả của truy vấn đầu tiên mà không có trong tập hợp kết quả của truy vấn thứ hai (phép trừ tập hợp).
• INTERSECT: Trả về tất cả các hàng duy nhất có trong cả hai tập hợp kết quả (phép giao tập hợp).

Chúng hoạt động tương tự như các phép toán logic và tập hợp trong toán học, yêu cầu các cột trong các truy vấn SELECT phải tương thích về số lượng và kiểu dữ liệu.