Đặc thù SQL Server và Performance

-- Clustered (chỉ 1 cái — thường là PK)
CREATE CLUSTERED INDEX IX_Orders_Date ON Orders(OrderDate);

-- Non-clustered
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

-- Composite — thứ tự cột RẤT QUAN TRỌNG (left-most prefix)
CREATE INDEX IX_Orders_Cust_Date ON Orders(CustomerId, OrderDate);

-- Covering — INCLUDE thêm cột để query không cần seek về bảng
CREATE INDEX IX_Orders_Cover ON Orders(CustomerId)
INCLUDE (OrderDate, Total, Status);

-- Filtered — chỉ index 1 phần dữ liệu
CREATE INDEX IX_Orders_Active ON Orders(CustomerId)
WHERE Status <> 'Cancelled';

-- Unique
CREATE UNIQUE INDEX IX_Users_Email ON Users(Email);

-- Drop
DROP INDEX IX_Orders_CustomerId ON Orders;

CREATE INDEX IX_Orders_ABC ON Orders(A, B, C);

-- Index dùng được:
WHERE A = ?
WHERE A = ? AND B = ?
WHERE A = ? AND B = ? AND C = ?

-- Index KHÔNG dùng được (hoặc dùng không hiệu quả):
WHERE B = ?               -- bỏ qua A
WHERE A = ? AND C = ?     -- skip B
WHERE C = ?               -- chỉ có cột cuối

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders WHERE CustomerId = 1;

-- Output:
-- Table 'Orders'. Scan count 1, logical reads 8, physical reads 2, ...
-- CPU time = 0 ms,  elapsed time = 12 ms.

-- Xem fragmentation
SELECT * FROM sys.dm_db_index_physical_stats(
    DB_ID('LearnSqlServer'), NULL, NULL, NULL, 'DETAILED');

-- Reorganize (5–30%)
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;

-- Rebuild (> 30% — lock dài)
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;

-- Update statistics
UPDATE STATISTICS Orders WITH FULLSCAN;

-- 1. OPTIMIZE FOR
SELECT * FROM Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN));

-- 2. RECOMPILE — sinh plan mới mỗi lần gọi
SELECT * FROM Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

-- 3. SQL Server 2025: OPPO (Optional Parameter Plan Optimization) tự xử lý
ALTER DATABASE SCOPED CONFIGURATION
    SET OPTIONAL_PARAMETER_PLAN_OPTIMIZATION = ON;   -- 2025 only

CREATE TABLE Events (
    Id INT IDENTITY PRIMARY KEY,
    Payload JSON                       -- 2025 mới
);

INSERT INTO Events (Payload)
VALUES (N'{ "type": "click", "user": "alice", "tags": ["promo", "email"] }');

-- Path
SELECT
    JSON_VALUE(Payload, '$.type') AS EventType,
    JSON_VALUE(Payload, '$.user') AS Username,
    JSON_QUERY(Payload, '$.tags') AS Tags
FROM Events;

-- OPENJSON — biến JSON thành table
SELECT *
FROM OPENJSON(
    N'[{"id":1,"name":"A"},{"id":2,"name":"B"}]'
)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name'
);

-- Modify
UPDATE Events
SET Payload = JSON_MODIFY(Payload, '$.processed', 1)
WHERE Id = 1;

-- Trước 2025 phải dùng CLR. Giờ built-in.
SELECT Email FROM Users
WHERE REGEXP_LIKE(Email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$');

SELECT REGEXP_REPLACE('Hello World 2026', '\d+', 'XXXX');
-- 'Hello World XXXX'

SELECT REGEXP_SUBSTR(Description, '[A-Z]{2,}') FROM Articles;

CREATE TABLE Products (
    Id INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18, 2),
    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.ProductsHistory));

-- Mỗi UPDATE tự động lưu version cũ vào ProductsHistory

-- Query data ngày 2026-01-01
SELECT * FROM Products
    FOR SYSTEM_TIME AS OF '2026-01-01';

-- Tất cả version từng tồn tại
SELECT * FROM Products
    FOR SYSTEM_TIME ALL
WHERE Id = 1;

-- Cho bảng analytics rất lớn, query aggregate
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales ON Sales;

-- Hoặc non-clustered columnstore (OLTP có lẫn analytics)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales ON Sales(Year, Month, Total);

-- Partition function chia theo year
CREATE PARTITION FUNCTION pf_OrderYear (INT)
AS RANGE LEFT FOR VALUES (2023, 2024, 2025);

CREATE PARTITION SCHEME ps_OrderYear
AS PARTITION pf_OrderYear ALL TO ([PRIMARY]);

CREATE TABLE Orders (
    Id INT, Year INT, ...
) ON ps_OrderYear(Year);

-- Bước 1: Tạo bảng với cột vector
-- Dùng kiểu VECTOR(n) — n là số dimensions (vd: OpenAI ada-002 = 1536)
CREATE TABLE ProductEmbeddings (
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(200),
    Embedding VECTOR(1536)
);

-- Bước 2: Tạo vector index
CREATE VECTOR INDEX IX_Product_Vector
ON ProductEmbeddings(Embedding)
WITH (DISTANCE_METRIC = 'cosine');   -- cosine | euclidean | dot

-- Bước 3: Query similarity search
DECLARE @QueryEmbedding VECTOR(1536) = 0x...;

SELECT TOP 5
    ProductName,
    VECTOR_DISTANCE(Embedding, @QueryEmbedding, 'cosine') AS Similarity
FROM ProductEmbeddings
ORDER BY Similarity
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

-- Cách cũ: computed column + index thường (vẫn hoạt động)
ALTER TABLE Events ADD EventType AS JSON_VALUE(Payload, '$.type');
CREATE INDEX IX_EventType ON Events(EventType);

-- SQL Server 2025: JSON Index (Preview)
CREATE JSON INDEX IX_Events_Payload
ON Events(Payload)
WITH (
    JSON_PATH '$.type',
    JSON_PATH '$.user',
    JSON_PATH '$.timestamp'
);

Row locks (chi tiết, nhiều concurrency)
       |
       | Lock escalation khi memory pressure
       v
Page locks (thô hơn, ít concurrency hơn)
       |
       v
Table lock (chặn tất cả transaction khác)

-- Kiểm tra lock escalation setting trên bảng (2025: thường đã OFF)
SELECT name, is_lock_escalation_disabled
FROM sys.tables
WHERE name = 'Orders';

-- Monitor locks hiện tại
SELECT
    request_session_id AS SPID,
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('LearnSqlServer');

-- Query này trong 2025 với LAQ:
-- Chỉ lock những row có Status = 'Pending' AND Amount > 1000
-- Thay vì lock toàn bộ range scan như trước đây
UPDATE Orders
SET Processed = 1
WHERE Status = 'Pending' AND Amount > 1000;

-- Giới hạn TempDB space cho workload group "ReportUsers"
ALTER WORKLOAD GROUP ReportUsers
WITH (
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MAX_CPU_TIME_SEC = 300,
    GROUP_MAX_TEMPDB_SPACE_MB = 20480   -- 2025 mới: giới hạn 20GB
);

-- Bật ADR cho database (ảnh hưởng cả TempDB behavior)
ALTER DATABASE LearnSqlServer
SET ACCELERATED_DATABASE_RECOVERY = ON;

# Docker run với tmpfs cho TempDB
docker run -d \
  --name sql2025 \
  -e 'ACCEPT_EULA=Y' \
  -e 'MSSQL_SA_PASSWORD=YourStr0ngP@ss' \
  --tmpfs /var/opt/mssql/tempdb:rw,noexec,nosuid,size=4g \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2025-latest

-- Backup với ZSTD compression (2025)
BACKUP DATABASE LearnSqlServer
TO DISK = N'D:\Backups\LearnSqlServer_2025.bak'
WITH COMPRESSION, COMPRESSION_ALGORITHM = ZSTD;

-- Chạy full backup trên secondary replica (2025)
-- Không còn bị giới hạn COPY_ONLY
BACKUP DATABASE LearnSqlServer
TO DISK = N'\\BackupServer\Share\LearnSqlServer_Full.bak'
WITH COMPRESSION, COMPRESSION_ALGORITHM = ZSTD;

-- Kill với abort query execution (2025)
KILL 54 WITH ABORT_QUERY_EXECUTION;

# SQL Server 2025 container: cgroup v2 + tmpfs tempdb
docker run -d \
  --name sql2025 \
  --cgroup-parent=/sqlserver.slice \     # cgroup v2 support
  --tmpfs /var/opt/mssql/tempdb:size=8g \
  -e 'ACCEPT_EULA=Y' \
  -e 'MSSQL_SA_PASSWORD=YourStr0ngP@ss' \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2025-latest

-- PolyBase: query file CSV/Parquet trên S3 từ SQL Server Linux
CREATE EXTERNAL DATA SOURCE MyS3
WITH (
    LOCATION = 's3://my-bucket/',
    CREDENTIAL = S3Credential
);

SELECT * FROM OPENROWSET(
    BULK 'sales/2025/*.parquet',
    DATA_SOURCE = 'MyS3',
    FORMAT = 'PARQUET'
) AS Sales;

AI Agent (Claude / GPT / Copilot)
       |
       | MCP Protocol (standardized)
       v
SQL MCP Server
       |
       v
Azure SQL DB / SQL Server 2025

Savings Plan 1 năm           -> Tiết kiệm đến 35%
Azure Hybrid Benefit         -> Dùng on-prem license, giảm thêm
Reserved Capacity 3 năm       -> Tiết kiệm nhất
Azure Arc enabled SQL Server -> Tiết kiệm đến 20% + Entra ID auth

Fabric Database Hub
├── Azure SQL DB
├── SQL Server on-prem (qua Azure Arc)
├── Fabric SQL Database (SaaS)
└── OneLake shortcut -> query trực tiếp từ Lakehouse

+------------------------------------------------------+
|                    RAG Architecture                   |
|                                                       |
|  +----------+    +-----------+    +----------------+  |
|  |  User    |--->| SQL Server|--->|  AI Service    |  |
|  |  Query   |    |           |    |  (Azure OpenAI)|  |
|  +----------+    | - Data    |    +-------+--------+  |
|                  | - Vector  |<-----------+           |
|                  |   Index   |   embedding response    |
|                  +-----------+                        |
|                                                       |
|  1. User query -> SQL Server                          |
|  2. sp_invoke_external_rest_endpoint -> AI service    |
|  3. AI returns embedding / completion                 |
|  4. SQL Server dùng vector index tìm relevant data    |
|  5. Trả kết quả kèm context cho user                  |
+------------------------------------------------------+

-- Gọi Azure OpenAI từ T-SQL để tạo embedding
DECLARE @response NVARCHAR(MAX);

EXEC sp_invoke_external_rest_endpoint
    @url = 'https://my-openai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',
    @method = 'POST',
    @headers = '{"Content-Type":"application/json", "api-key":"xxx"}',
    @payload = N'{"input": "Sản phẩm điện thoại Samsung Galaxy"}',
    @response = @response OUTPUT;

-- Parse embedding từ response và dùng để vector search
-- SELECT ... ORDER BY VECTOR_DISTANCE(Embedding, @ParsedEmbedding, 'cosine')

-- Đăng ký AI model như database object
CREATE EXTERNAL MODEL InvoiceClassifier
WITH (
    MODEL_PROVIDER = 'AZURE_OPENAI',
    MODEL_NAME = 'gpt-4o',
    ENDPOINT = 'https://my-openai.openai.azure.com',
    API_VERSION = '2024-10-21',
    CREDENTIAL = MyAzureCredential
);

-- Gọi model như function trong T-SQL
SELECT
    InvoiceId,
    PREDICT(InvoiceClassifier, InvoiceText) AS Category
FROM Invoices;

-- SQL Server 2025 tự retry khi gọi external API
EXEC sp_invoke_external_rest_endpoint
    @url = 'https://api.example.com/classify',
    @method = 'POST',
    @payload = N'{"text": "..."}',
    @retry_count = 3,
    @retry_interval_ms = 1000,
    @response = @response OUTPUT;

-- Đăng ký nhiều version của cùng model
CREATE EXTERNAL MODEL SentimentV1
WITH (MODEL_PROVIDER = 'AZURE_OPENAI', MODEL_NAME = 'gpt-4o', ENDPOINT = '...', CREDENTIAL = MyCredential);

CREATE EXTERNAL MODEL SentimentV2
WITH (MODEL_PROVIDER = 'AZURE_OPENAI', MODEL_NAME = 'gpt-4.1', ENDPOINT = '...', CREDENTIAL = MyCredential);

-- A/B test: 50% traffic mỗi model
DECLARE @model SYSNAME = IIF(ABS(CHECKSUM(NEWID())) % 2 = 0, 'SentimentV1', 'SentimentV2');
SELECT PREDICT(@model, ReviewText) AS Sentiment FROM Reviews;

-- Vector query metrics trong Query Store (2025 mới)
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    qsq.count_vector_searches,
    qsq.avg_vector_search_duration_ms,
    qsq.avg_vector_distance_compute_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
    ON qsq.query_text_id = qsqt.query_text_id
WHERE qsq.count_vector_searches > 0
ORDER BY qsq.avg_vector_search_duration_ms DESC;

-- Lock escalation events (theo dõi Optimized Locking)
SELECT
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Locks%'
  AND counter_name IN (
      'Lock Escalations/sec',
      'Average Wait Time (ms)',
      'Number of Deadlocks/sec'
  );

-- TempDB usage theo workload group (2025 mới)
SELECT
    wg.name AS workload_group,
    wg.total_tempdb_usage_kb,
    wg.max_tempdb_space_kb
FROM sys.dm_resource_governor_workload_groups AS wg;

-- Kiểm tra query có gọi external endpoint không
SELECT
    q.query_id,
    t.query_sql_text,
    q.count_external_api_calls,
    q.avg_external_api_duration_ms,
    q.avg_external_api_retry_count
FROM sys.query_store_query q
JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id
WHERE q.count_external_api_calls > 0
ORDER BY q.avg_external_api_duration_ms DESC;

Optimized Locking (riêng):            +10-15% throughput
Intelligent Query Processing (IQP):    +5-10% throughput
Kết hợp cả hai:                        +15-25% throughput
+-----------------------------------------------------+
Giảm lock waits:                      -40% đến -60%
Giảm deadlocks:                       -20% đến -30%