Advanced Features - Tính năng Nâng cao

Giới thiệu

Section này bao gồm các tính năng nâng cao của SQL Server thường gặp trong phỏng vấn Senior/Mid level. Mỗi tính năng có file riêng với nội dung chi tiết.

Danh sách Sub-topics

Temporal Tables (System-Versioned)

Tự động lưu lịch sử thay đổi dữ liệu — built-in audit trail.

-- Tạo temporal table
CREATE TABLE dbo.Employees (
    EmployeeID      INT PRIMARY KEY,
    Name            NVARCHAR(100) NOT NULL,
    Department      NVARCHAR(50),
    Salary          DECIMAL(12, 2),
    -- Hai cột system-period BẮTBUỘC
    ValidFrom       DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo         DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.EmployeesHistory  -- SQL Server tự tạo nếu không tồn tại
));

-- DML operations tự động lưu history
UPDATE dbo.Employees SET Salary = 80000 WHERE EmployeeID = 1;
-- Bản ghi cũ tự động chuyển sang dbo.EmployeesHistory

DELETE FROM dbo.Employees WHERE EmployeeID = 2;
-- Deleted row lưu trong history

-- Query thời điểm hiện tại (thông thường)
SELECT * FROM dbo.Employees;

-- Query tại một thời điểm trong quá khứ
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2025-01-01T00:00:00';

-- Query trong một khoảng thời gian (tất cả versions)
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2026-01-01';

-- Query tất cả rows bao gồm đã xóa trong khoảng
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';

-- Xem lịch sử thay đổi của một nhân viên cụ thể
SELECT 
    EmployeeID, Name, Department, Salary,
    ValidFrom, ValidTo,
    CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' 
         THEN 'Current' ELSE 'Historical' END AS RecordType
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1
ORDER BY ValidFrom;

-- Disable và enable system versioning
ALTER TABLE dbo.Employees SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employees SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.EmployeesHistory,
    DATA_CONSISTENCY_CHECK = ON
));

Temporal Table Use Cases

• Audit trail: ai sửa gì, khi nào
• Point-in-time reporting: báo cáo số liệu cuối tháng, cuối năm
• Slowly Changing Dimensions (SCD) trong data warehouse
• Regulatory compliance: GDPR, SOX — lưu lịch sử thay đổi

Full-Text Search

Tìm kiếm văn bản nâng cao hơn LIKE — linguistic analysis, word inflections, thesaurus.

-- Bước 1: Enable Full-Text trên database
-- CREATE FULLTEXT CATALOG FullTextCatalog AS DEFAULT;

-- Bước 2: Tạo Full-Text Index
CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.Articles (
    Title LANGUAGE 1033,    -- English
    Body LANGUAGE 1033
)
KEY INDEX PK_Articles
ON FTCatalog
WITH CHANGE_TRACKING AUTO;  -- Auto sync với DML

-- CONTAINS: tìm từ/cụm từ chính xác
SELECT ArticleID, Title
FROM dbo.Articles
WHERE CONTAINS(Body, '"SQL Server"');

-- Proximity: hai từ gần nhau
SELECT * FROM dbo.Articles
WHERE CONTAINS(Body, '"performance" NEAR "optimization"');

-- Inflectional forms: "run" tìm cả "ran", "running", "runs"
SELECT * FROM dbo.Articles
WHERE CONTAINS(Body, 'FORMSOF(INFLECTIONAL, run)');

-- Thesaurus: "car" tìm cả "automobile", "vehicle"
SELECT * FROM dbo.Articles
WHERE CONTAINS(Body, 'FORMSOF(THESAURUS, car)');

-- Weighted search: boost title hơn body
SELECT ArticleID, Title, 
    KEY_TBL.RANK
FROM dbo.Articles
INNER JOIN CONTAINSTABLE(dbo.Articles, (Title, Body), 
    'SQL Server performance', 10) AS KEY_TBL
ON dbo.Articles.ArticleID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

-- FREETEXT: ngôn ngữ tự nhiên (less precise, more recall)
SELECT ArticleID, Title
FROM dbo.Articles
WHERE FREETEXT(Body, 'how to improve database performance');

-- FREETEXTTABLE: với ranking
SELECT a.Title, kt.RANK
FROM dbo.Articles a
JOIN FREETEXTTABLE(dbo.Articles, Body, 
    'database tuning optimization') AS kt
ON a.ArticleID = kt.[KEY]
ORDER BY kt.RANK DESC;

Full-Text vs LIKE vs Elasticsearch

In-Memory OLTP (Hekaton)

Memory-optimized tables và natively compiled stored procedures — tốc độ cao nhất.

-- Bước 1: Thêm memory-optimized filegroup
ALTER DATABASE SalesDB 
ADD FILEGROUP SalesDB_InMem CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE SalesDB ADD FILE (
    NAME = 'SalesDB_InMem',
    FILENAME = 'D:\SQL\SalesDB_InMem'
) TO FILEGROUP SalesDB_InMem;

-- Bước 2: Tạo memory-optimized table
CREATE TABLE dbo.ShoppingCart (
    CartID      INT NOT NULL,
    UserID      INT NOT NULL,
    ProductID   INT NOT NULL,
    Quantity    INT NOT NULL,
    AddedDate   DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT PK_ShoppingCart PRIMARY KEY NONCLUSTERED 
        HASH (CartID) WITH (BUCKET_COUNT = 1000000)
    -- HASH index: O(1) lookups; Bucket count = expected rows * 1-2
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-- DURABILITY: SCHEMA_AND_DATA (persistent) hoặc SCHEMA_ONLY (temp, fast)

-- Tạo nonclustered index trên memory-optimized table
CREATE INDEX IX_Cart_UserID ON dbo.ShoppingCart (UserID)
WITH (BUCKET_COUNT = 500000);

-- Natively compiled stored procedure (compiled to machine code)
CREATE OR ALTER PROCEDURE dbo.usp_AddToCart
    @CartID INT, @UserID INT, @ProductID INT, @Quantity INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    INSERT INTO dbo.ShoppingCart (CartID, UserID, ProductID, Quantity)
    VALUES (@CartID, @UserID, @ProductID, @Quantity);
END;

-- Regular DML trên memory-optimized table (không cần native compilation)
INSERT INTO dbo.ShoppingCart VALUES (1, 1001, 5, 2, DEFAULT);
SELECT * FROM dbo.ShoppingCart WHERE UserID = 1001;

-- Monitor memory usage
SELECT 
    object_name(object_id) AS table_name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb,
    memory_allocated_for_indexes_kb,
    memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE database_id = DB_ID();

In-Memory OLTP - Khi nào dùng?

Phù hợp:

• High-concurrency OLTP (nhiều insert/update/delete cùng lúc)
• Session state, shopping carts, queue tables
• Short-lived transient data
• Eliminate latch contention (TempDB hotspots)

Giới hạn:

• Không hỗ trợ ALTER TABLE (phải drop/recreate)
• Không hỗ trợ một số T-SQL features (joins trong natively compiled, subqueries)
• FK constraints phức tạp
• Dữ liệu phải fit trong RAM (với DURABILITY = SCHEMA_AND_DATA vẫn persistent)

Change Data Capture (CDC)

Capture INSERT, UPDATE, DELETE thay đổi vào change tables — phổ biến trong ETL.

-- Enable CDC ở database level
USE SalesDB;
EXEC sys.sp_cdc_enable_db;

-- Enable CDC cho một table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Orders',
    @role_name = N'cdc_admin',  -- NULL = không restrict
    @supports_net_changes = 1,  -- Tổng hợp net change per row
    @captured_column_list = NULL;  -- NULL = capture tất cả columns

-- CDC tạo các change tables tự động:
-- cdc.dbo_Orders_CT: bảng chứa thay đổi
-- Columns: __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, + all source columns
-- __$operation: 1=DELETE, 2=INSERT, 3=Before UPDATE, 4=After UPDATE

-- Query changes trong một LSN range
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT 
    __$operation,
    CASE __$operation 
        WHEN 1 THEN 'DELETE' 
        WHEN 2 THEN 'INSERT' 
        WHEN 3 THEN 'Before UPDATE'
        WHEN 4 THEN 'After UPDATE'
    END AS operation_name,
    OrderID, CustomerID, TotalAmount, OrderDate
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all');

-- Net changes (chỉ kết quả cuối cùng per row)
SELECT * 
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all with mask');

-- Convert LSN sang thời gian
SELECT sys.fn_cdc_map_lsn_to_time(@from_lsn) AS from_time,
       sys.fn_cdc_map_lsn_to_time(@to_lsn) AS to_time;

-- Convert thời gian sang LSN
DECLARE @since_lsn BINARY(10) = 
    sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', 
        '2026-04-01 00:00:00');

-- Disable CDC
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'Orders',
    @capture_instance = N'dbo_Orders';

Change Tracking (nhẹ hơn CDC)

-- Enable Change Tracking ở database level
ALTER DATABASE SalesDB
SET CHANGE_TRACKING = ON (
    CHANGE_RETENTION = 7 DAYS,  -- Giữ history 7 ngày
    AUTO_CLEANUP = ON
);

-- Enable cho table
ALTER TABLE dbo.Orders
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

-- Query changes (chỉ biết row nào đổi, không biết value cũ)
DECLARE @sync_version BIGINT = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Orders'));
DECLARE @last_sync_version BIGINT = 0;  -- Lưu version từ lần sync trước

SELECT 
    o.OrderID,
    o.CustomerID,
    o.TotalAmount,
    CT.SYS_CHANGE_OPERATION,  -- I=Insert, U=Update, D=Delete
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_COLUMNS  -- Bitmap: columns bị thay đổi
FROM CHANGETABLE(CHANGES dbo.Orders, @last_sync_version) AS CT
LEFT JOIN dbo.Orders o ON o.OrderID = CT.OrderID;

-- Lấy current version để save cho lần sync kế tiếp
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version;

CDC vs Change Tracking

Q&A - Advanced Features Tổng hợp

Junior Level

Q1: Temporal Tables trong SQL Server là gì? Use case?

Temporal tables (system-versioned) tự động lưu lịch sử thay đổi dữ liệu. SQL Server tự động ghi row cũ vào history table khi có UPDATE/DELETE.

Use cases: audit trail, point-in-time reporting, regulatory compliance (ai sửa gì lúc mấy giờ), slowly changing dimensions trong data warehouse.

Q2: CONTAINS và FREETEXT khác nhau thế nào?

• CONTAINS: tìm kiếm chính xác theo terms, phrases, proximity. Cho phép boolean operators (AND, OR, AND NOT). Precision cao hơn
• FREETEXT: tìm kiếm ngôn ngữ tự nhiên — SQL Server analyze câu và tìm các từ có ý nghĩa tương tự. Recall cao hơn, ít control hơn

Q3: CDC và Change Tracking khác nhau thế nào?

CDC capture đầy đủ before/after values và DML type — dùng cho ETL, auditing cần biết giá trị thay đổi. Change Tracking chỉ track primary key và operation type (I/U/D) — nhẹ hơn, dùng cho sync scenarios khi chỉ cần biết row nào thay đổi.

Q4: In-Memory OLTP (memory-optimized tables) có ưu điểm gì?

• Loại bỏ latch contention (không có pages/locks theo nghĩa truyền thống)
• Natively compiled stored procedures chạy nhanh hơn interpreted T-SQL
• Optimistic concurrency (SNAPSHOT isolation mặc định)
• Throughput cao hơn nhiều cho high-concurrent OLTP workloads

Mid Level

Q5: Cách query dữ liệu trong Temporal Table tại một thời điểm trong quá khứ?

Dùng FOR SYSTEM_TIME AS OF 'datetime':

SELECT * FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2025-06-30T23:59:59';

SQL Server tự động query cả bảng hiện tại và history table để tìm rows có ValidFrom <= datetime < ValidTo.

Q6: Memory-optimized table dùng HASH index vs RANGE index — khi nào dùng cái nào?

• HASH index: O(1) point lookup cho equality predicates (WHERE ID = ?). Cần chỉ định BUCKET_COUNT (nên >= expected rows). Không hỗ trợ range scans hay ORDER BY
• NONCLUSTERED index (trong memory-optimized): hỗ trợ range scans và ordering, nhưng chậm hơn HASH cho point lookups

Quy tắc: nếu query chủ yếu là equality lookup → HASH; nếu cần range scan hoặc ORDER BY → NONCLUSTERED.

Q7: CDC sử dụng LSN — LSN là gì và tại sao quan trọng trong CDC?

LSN (Log Sequence Number) là identifier duy nhất và tăng dần cho mỗi log record trong transaction log. CDC dùng LSN để:

• Track exactly which changes đã được xử lý (idempotent incremental loads)
• Convert sang thời gian: sys.fn_cdc_map_lsn_to_time()
• Query changes trong range: fn_cdc_get_all_changes_*(from_lsn, to_lsn)

Trong ETL, sau mỗi batch bạn lưu to_lsn làm điểm bắt đầu cho batch kế tiếp → không bỏ sót và không duplicate changes.

Q8: Tại sao không nên dùng Full-Text Search cho tất cả text queries?

Full-Text có overhead:

• Index maintenance (sync change tables)
• Disk space cho FT indexes
• Linguistic analysis complexity

Dùng FT khi: cần tìm kiếm ngôn ngữ tự nhiên, stemming, proximity, relevance ranking. Không cần FT khi: tìm kiếm chính xác có thể cover bằng index seek, LIKE với prefix (Name LIKE 'abc%' dùng index tốt), hoặc exact string matching.

Senior Level

Q9: Thiết kế audit solution cho GDPR compliance dùng Temporal Tables. Những gì cần xem xét?

Requirements GDPR:

• Lưu lịch sử thay đổi (ai sửa gì, khi nào)
• “Right to be forgotten”: có thể xóa data
• Data không được giữ lâu hơn cần thiết

Solution với Temporal Tables:

1. Temporal tables cho tất cả tables chứa PII
2. Retention policy: để history lâu hơn operational data (ví dụ: operational 2 năm, history 7 năm cho compliance)
3. “Right to be forgotten”: phức tạp với temporal — SYSTEM_VERSIONING = OFF để update/delete history, sau đó bật lại. Cân nhắc pseudonymization thay vì physical delete
4. Separate history database để có thể backup/restore policy khác nhau
5. Compression trên history table (ROW/PAGE COMPRESSION)
6. Audit of who queried: Extended Events session capture selects trên sensitive tables

Q10: Giải thích In-Memory OLTP isolation levels. Tại sao không có traditional locking?

Memory-optimized tables dùng Optimistic Concurrency Control (OCC) thay vì pessimistic locking:

• SNAPSHOT isolation (mặc định trong natively compiled procs): mỗi transaction thấy consistent snapshot tại thời điểm bắt đầu → không đọc uncommitted data, không block
• REPEATABLE READ: thêm validation tại commit time — nếu row đã bị modify bởi concurrent transaction → commit fail, retry
• SERIALIZABLE: full serializable validation

Nếu conflict tại commit: transaction phải retry (application cần handle 1205 hoặc 41302 errors).

Không có traditional locking vì: data structure dùng multi-version concurrency (Bw-Tree, linked lists), mỗi version immutable → readers không block writers, writers không block readers.

Q11: Query Store plan forcing — khi nào dùng và rủi ro là gì?

Khi dùng plan forcing:

• Plan regression đột ngột sau SQL Server upgrade hoặc statistics update
• Biết rõ plan cụ thể là optimal cho workload
• Emergency fix trong khi tìm root cause thực sự

Cách dùng:

-- Tìm plan_id tốt từ Query Store history, sau đó force
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Rủi ro:

• Forced plan có thể không optimal khi data distribution thay đổi (ví dụ: tháng sau bảng có 10x rows)
• Schema changes (thêm index mới, thay đổi table) làm forced plan invalid → tự fallback về optimizer choice
• Che giấu vấn đề gốc (outdated stats, bad query design) thay vì fix

Best practice: dùng như giải pháp tạm thời, kèm theo ticket để fix root cause. Theo dõi performance sau khi force. Unforce plan sau khi fix xong.

Q12: Partition Switching + CDC — có vấn đề gì không?

Vấn đề: Partition SWITCH là metadata-only operation → CDC không capture data movement từ SWITCH. Nếu SWITCH partition từ staging vào production, CDC change table sẽ không có records cho các rows đó.

Giải pháp:

1. Dùng SWITCH cho loading (bypass CDC capture) → sau SWITCH, apply CDC capture thủ công nếu cần replication
2. Đặt CDC ở downstream (sau khi data đã fully loaded)
3. Dùng Change Tracking thay vì CDC nếu chỉ cần row-level tracking (Change Tracking cũng không capture SWITCH)
4. Implement custom audit logic cho partition switching operations