Phần này bao gồm các kỹ thuật truy vấn nâng cao trong SQL Server: Views (bao gồm Indexed Views), CTEs (Common Table Expressions), Window Functions, và Cursors.
-- Tạo view cơ bản
CREATE VIEW dbo.vw_ActiveOrders
AS
SELECT
o.OrderId,
o.OrderDate,
o.TotalAmount,
o.Status,
c.CustomerName,
c.Email,
c.Region
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerId = c.CustomerId
WHERE o.IsDeleted = 0;
GO
-- Sửa view (giữ nguyên permissions)
ALTER VIEW dbo.vw_ActiveOrders
AS
SELECT
o.OrderId,
o.OrderDate,
o.TotalAmount,
o.Status,
c.CustomerName,
c.Email,
c.Region,
o.ShippedDate -- Thêm cột mới
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerId = c.CustomerId
WHERE o.IsDeleted = 0 AND o.Status <> 'Cancelled';
GO
-- Xóa view
DROP VIEW IF EXISTS dbo.vw_ActiveOrders;
-- Xem định nghĩa view
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.vw_ActiveOrders'));
-- hoặc
EXEC sp_helptext 'dbo.vw_ActiveOrders';
-- SCHEMABINDING: Ngăn thay đổi base objects
-- BẮT BUỘC cho Indexed Views
-- Yêu cầu: Phải dùng tên đầy đủ (schema.table)
CREATE VIEW dbo.vw_ProductPriceList
WITH SCHEMABINDING -- Khóa schema
AS
SELECT
p.ProductId,
p.ProductName,
p.Price,
c.CategoryName
FROM dbo.Products p -- Phải có dbo.
INNER JOIN dbo.Categories c ON p.CategoryId = c.CategoryId
WHERE p.IsActive = 1;
GO
-- Thử DROP column của base table → LỖI
-- ALTER TABLE dbo.Products DROP COLUMN Price;
-- ERROR: Cannot DROP COLUMN 'Price' because it is referenced by view 'vw_ProductPriceList'
-- Kiểm tra view có SCHEMABINDING không
SELECT
v.name,
v.with_check_option,
m.is_schema_bound
FROM sys.views v
JOIN sys.sql_modules m ON v.object_id = m.object_id
WHERE v.name = 'vw_ProductPriceList';
-- CHECK OPTION: Đảm bảo INSERT/UPDATE qua view phải thỏa WHERE của view
CREATE VIEW dbo.vw_ActiveProducts
AS
SELECT ProductId, ProductName, Price, IsActive
FROM dbo.Products
WHERE IsActive = 1
WITH CHECK OPTION; -- Ngăn INSERT/UPDATE tạo ra rows không visible trong view
-- INSERT thỏa điều kiện → OK
INSERT INTO dbo.vw_ActiveProducts (ProductName, Price, IsActive)
VALUES ('New Product', 99.99, 1);
-- INSERT vi phạm điều kiện → ERROR
INSERT INTO dbo.vw_ActiveProducts (ProductName, Price, IsActive)
VALUES ('Inactive Product', 99.99, 0);
-- ERROR: The attempted insert or update failed because the target view
-- either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION
View có thể UPDATE/INSERT/DELETE nếu thỏa:
| Ràng buộc | Mô tả |
|---|
Không có DISTINCT | |
| Không có aggregate (SUM, COUNT…) | |
Không có GROUP BY, HAVING | |
Không có TOP hoặc UNION | |
| Chỉ reference một base table trong DML | |
-- View đơn giản → updatable
CREATE VIEW dbo.vw_EmployeeNames AS
SELECT EmployeeId, FirstName, LastName FROM Employees;
-- UPDATE trực tiếp qua view
UPDATE dbo.vw_EmployeeNames
SET LastName = 'Nguyen'
WHERE EmployeeId = 1;
-- Tương đương: UPDATE Employees SET LastName = 'Nguyen' WHERE EmployeeId = 1
-- View join nhiều bảng → KHÔNG updatable trực tiếp
-- Dùng INSTEAD OF trigger (xem phần Triggers)
Indexed View lưu kết quả vật lý trên disk, tự động cập nhật khi base tables thay đổi.
-- Bước 1: Tạo view với SCHEMABINDING (bắt buộc)
CREATE VIEW dbo.vw_DailySalesSummary
WITH SCHEMABINDING
AS
SELECT
CAST(o.OrderDate AS DATE) AS SaleDate,
p.CategoryId,
COUNT_BIG(*) AS OrderCount, -- Phải dùng COUNT_BIG(*), không phải COUNT(*)
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
SUM(CAST(od.Quantity AS BIGINT)) AS TotalQuantity
FROM dbo.Orders o
INNER JOIN dbo.OrderDetails od ON o.OrderId = od.OrderId
INNER JOIN dbo.Products p ON od.ProductId = p.ProductId
WHERE o.IsDeleted = 0
GROUP BY CAST(o.OrderDate AS DATE), p.CategoryId;
GO
-- Bước 2: Tạo UNIQUE CLUSTERED INDEX → materialize view
CREATE UNIQUE CLUSTERED INDEX UCIX_vw_DailySalesSummary
ON dbo.vw_DailySalesSummary (SaleDate, CategoryId);
GO
-- Bước 3 (Optional): Thêm non-clustered indexes
CREATE INDEX IX_vw_DailySalesSummary_Revenue
ON dbo.vw_DailySalesSummary (TotalRevenue DESC);
-- Optimizer tự động dùng indexed view khi profitable
-- (với ENTERPRISE edition, STANDARD cần NOEXPAND hint)
-- Force dùng indexed view (STANDARD edition)
SELECT SaleDate, CategoryId, TotalRevenue
FROM dbo.vw_DailySalesSummary WITH (NOEXPAND)
WHERE SaleDate = '2025-01-01';
Yêu cầu cho Indexed View:
| Yêu cầu | Chi tiết |
|---|
WITH SCHEMABINDING | Bắt buộc |
| First index là UNIQUE CLUSTERED | Bắt buộc |
Không có * | Phải explicit columns |
Không DISTINCT, TOP, subqueries | |
Không OUTER JOIN | Chỉ INNER JOIN |
COUNT_BIG(*) thay vì COUNT(*) | Khi có GROUP BY |
| Deterministic functions only | |
SET ANSI_NULLS ON và SET QUOTED_IDENTIFIER ON | |
-- Liệt kê tất cả views
SELECT
v.name AS ViewName,
s.name AS SchemaName,
v.create_date,
v.modify_date,
m.is_schema_bound,
m.uses_ansi_nulls
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
JOIN sys.sql_modules m ON v.object_id = m.object_id
ORDER BY s.name, v.name;
-- INFORMATION_SCHEMA.VIEWS: Portable across SQL products
SELECT
TABLE_SCHEMA,
TABLE_NAME,
VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'vw_%';
-- Tìm views phụ thuộc vào một bảng
SELECT DISTINCT
v.name AS ViewName
FROM sys.views v
JOIN sys.sql_expression_dependencies d ON v.object_id = d.referencing_id
JOIN sys.objects o ON d.referenced_id = o.object_id
WHERE o.name = 'Orders';
-- CTE cơ bản
WITH OrderSummary AS (
SELECT
CustomerId,
COUNT(*) AS TotalOrders,
SUM(TotalAmount) AS TotalSpent
FROM Orders
WHERE Status = 'Completed'
GROUP BY CustomerId
)
SELECT
c.CustomerName,
c.Email,
os.TotalOrders,
os.TotalSpent
FROM Customers c
INNER JOIN OrderSummary os ON c.CustomerId = os.CustomerId
WHERE os.TotalSpent > 1000
ORDER BY os.TotalSpent DESC;
WITH
-- CTE 1: High-value customers
HighValueCustomers AS (
SELECT CustomerId, SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerId
HAVING SUM(TotalAmount) >= 5000
),
-- CTE 2: Recent orders (dùng bảng gốc)
RecentOrders AS (
SELECT CustomerId, COUNT(*) AS OrdersLast30Days
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY CustomerId
),
-- CTE 3: Combine (có thể reference các CTEs trước)
CustomerInsights AS (
SELECT
hvc.CustomerId,
hvc.TotalSpent,
ISNULL(ro.OrdersLast30Days, 0) AS RecentOrders
FROM HighValueCustomers hvc
LEFT JOIN RecentOrders ro ON hvc.CustomerId = ro.CustomerId
)
-- Final query sử dụng CTE cuối cùng
SELECT
c.CustomerName,
ci.TotalSpent,
ci.RecentOrders,
CASE
WHEN ci.RecentOrders >= 3 THEN 'Very Active'
WHEN ci.RecentOrders >= 1 THEN 'Active'
ELSE 'Inactive'
END AS ActivityStatus
FROM Customers c
INNER JOIN CustomerInsights ci ON c.CustomerId = ci.CustomerId
ORDER BY ci.TotalSpent DESC;
-- CẤU TRÚC:
-- Anchor Member UNION ALL Recursive Member
-- EXAMPLE 1: Traversal hierarchy nhân viên
WITH EmployeeTree AS (
-- ANCHOR: CEO (không có manager)
SELECT
EmployeeId,
FullName,
ManagerId,
JobTitle,
0 AS Level,
CAST(FullName AS NVARCHAR(MAX)) AS HierarchyPath,
CAST(EmployeeId AS NVARCHAR(MAX)) AS IdPath
FROM Employees
WHERE ManagerId IS NULL
UNION ALL
-- RECURSIVE: Direct reports của các employees đã có
SELECT
e.EmployeeId,
e.FullName,
e.ManagerId,
e.JobTitle,
et.Level + 1 AS Level,
et.HierarchyPath + ' > ' + e.FullName,
et.IdPath + '.' + CAST(e.EmployeeId AS NVARCHAR(10))
FROM Employees e
INNER JOIN EmployeeTree et ON e.ManagerId = et.EmployeeId
)
SELECT
REPLICATE(' ', Level) + FullName AS OrgChart,
JobTitle,
Level,
HierarchyPath
FROM EmployeeTree
ORDER BY IdPath;
-- OPTION (MAXRECURSION 100); -- Giới hạn depth (default là 100, 0 = unlimited)
-- EXAMPLE 2: Đếm ngày làm việc (trừ cuối tuần)
WITH DateSeries AS (
-- Anchor: ngày bắt đầu
SELECT CAST('2025-01-01' AS DATE) AS WorkDate
UNION ALL
-- Recursive: thêm từng ngày
SELECT DATEADD(DAY, 1, WorkDate)
FROM DateSeries
WHERE WorkDate < '2025-12-31'
)
SELECT COUNT(*) AS WorkingDays
FROM DateSeries
WHERE DATENAME(WEEKDAY, WorkDate) NOT IN ('Saturday', 'Sunday')
OPTION (MAXRECURSION 366);
-- EXAMPLE 3: Tìm routes giữa các nodes (graph traversal)
WITH RouteSearch AS (
SELECT
FromCity, ToCity,
Distance AS TotalDistance,
CAST(FromCity + ' → ' + ToCity AS NVARCHAR(MAX)) AS Route,
1 AS Hops
FROM Routes
WHERE FromCity = 'Hanoi'
UNION ALL
SELECT
rs.FromCity, r.ToCity,
rs.TotalDistance + r.Distance,
rs.Route + ' → ' + r.ToCity,
rs.Hops + 1
FROM RouteSearch rs
INNER JOIN Routes r ON rs.ToCity = r.FromCity
WHERE rs.Hops < 5 -- Giới hạn số bước
AND rs.Route NOT LIKE '%' + r.ToCity + '%' -- Tránh cycle
)
SELECT Route, TotalDistance
FROM RouteSearch
WHERE ToCity = 'HCMC'
ORDER BY TotalDistance;
-- DELETE với CTE (xóa duplicates)
WITH DuplicateOrders AS (
SELECT
OrderId,
ROW_NUMBER() OVER (
PARTITION BY CustomerId, OrderDate, TotalAmount
ORDER BY OrderId
) AS RowNum
FROM Orders
)
DELETE FROM DuplicateOrders WHERE RowNum > 1;
-- Thực tế: DELETE ảnh hưởng đến bảng Orders gốc
-- UPDATE với CTE (cập nhật với ranking)
WITH RankedSalary AS (
SELECT
EmployeeId,
Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
FROM Employees
)
UPDATE RankedSalary
SET Salary = Salary * 1.10 -- Tăng 10% cho top earner
WHERE SalaryRank = 1;
| CTE | Subquery | Temp Table |
|---|
| Readable | ✅ Rất rõ | ❌ Lồng nhau | ✅ Rõ |
| Reuse | ❌ Chỉ một lần | ❌ | ✅ Nhiều lần |
| Recursive | ✅ | ❌ | ❌ |
| Statistics | ❌ | ❌ | ✅ |
| Large data | ⚠️ | ⚠️ | ✅ |
| Index | ❌ | ❌ | ✅ |
| DELETE/UPDATE | ✅ | ❌ | ✅ |
function_name(args) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression [ASC|DESC]]
[ROWS|RANGE BETWEEN frame_start AND frame_end]
)
-- Frame options:
-- UNBOUNDED PRECEDING: Từ đầu partition
-- CURRENT ROW: Row hiện tại
-- UNBOUNDED FOLLOWING: Đến cuối partition
-- N PRECEDING: N rows trước
-- N FOLLOWING: N rows sau
SELECT
EmployeeId,
FullName,
Department,
Salary,
-- ROW_NUMBER: Sequential, không ties
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
-- RANK: Ties có cùng rank, skip sau
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rnk,
-- DENSE_RANK: Ties có cùng rank, không skip
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRnk,
-- NTILE: Chia thành N buckets đều nhau
NTILE(4) OVER (PARTITION BY Department ORDER BY Salary DESC) AS Quartile
FROM Employees
ORDER BY Department, Salary DESC;
/*
Kết quả ví dụ (cùng Department, Salary: 10000, 8000, 8000, 6000):
FullName Salary RowNum Rnk DenseRnk Quartile
Alice 10000 1 1 1 1
Bob 8000 2 2 2 2
Carol 8000 3 2 2 2
David 6000 4 4 3 3
*/
SELECT
OrderDate,
Revenue,
-- LAG: Giá trị từ row TRƯỚC đó
LAG(Revenue, 1, 0) OVER (ORDER BY OrderDate) AS PrevRevenue,
LAG(Revenue, 2, 0) OVER (ORDER BY OrderDate) AS TwoPeriodsAgo,
-- LEAD: Giá trị từ row SAU đó
LEAD(Revenue, 1, 0) OVER (ORDER BY OrderDate) AS NextRevenue,
-- FIRST_VALUE: Giá trị đầu tiên trong partition/window
FIRST_VALUE(Revenue) OVER (PARTITION BY YEAR(OrderDate) ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstRevenueOfYear,
-- LAST_VALUE: Giá trị cuối cùng (cần explicit frame!)
LAST_VALUE(Revenue) OVER (PARTITION BY YEAR(OrderDate) ORDER BY OrderDate
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastRevenueOfYear,
-- Month-over-Month Growth %
CASE
WHEN LAG(Revenue) OVER (ORDER BY OrderDate) = 0 THEN NULL
ELSE ROUND(
(Revenue - LAG(Revenue) OVER (ORDER BY OrderDate)) * 100.0
/ LAG(Revenue) OVER (ORDER BY OrderDate),
2)
END AS MoMGrowthPct
FROM MonthlySales
ORDER BY OrderDate;
-- RUNNING TOTAL (Cumulative Sum)
SELECT
OrderDate,
OrderAmount,
-- Running total toàn bộ
SUM(OrderAmount) OVER (
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal,
-- Running total theo năm (PARTITION BY)
SUM(OrderAmount) OVER (
PARTITION BY YEAR(OrderDate)
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS YearlyRunningTotal,
-- Moving Average: trung bình 7 ngày gần nhất
AVG(OrderAmount) OVER (
ORDER BY OrderDate
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS MovingAvg7Day,
-- Running Count
COUNT(*) OVER (
PARTITION BY CustomerId
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CustomerOrderNumber,
-- Percent of total
ROUND(
OrderAmount * 100.0 / SUM(OrderAmount) OVER (),
2
) AS PctOfTotal,
-- Percent of year total
ROUND(
OrderAmount * 100.0 / SUM(OrderAmount) OVER (PARTITION BY YEAR(OrderDate)),
2
) AS PctOfYearTotal
FROM Orders
ORDER BY OrderDate;
DECLARE @Page INT = 2;
DECLARE @PageSize INT = 10;
WITH PagedOrders AS (
SELECT
OrderId,
OrderDate,
TotalAmount,
CustomerName,
ROW_NUMBER() OVER (ORDER BY OrderDate DESC, OrderId DESC) AS RowNum
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.IsDeleted = 0
)
SELECT OrderId, OrderDate, TotalAmount, CustomerName
FROM PagedOrders
WHERE RowNum BETWEEN (@Page - 1) * @PageSize + 1 AND @Page * @PageSize;
-- Modern approach: OFFSET/FETCH (SQL 2012+)
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET (@Page - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- Xem duplicates
WITH DuplicateDetection AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY Email, FirstName, LastName -- Cột xác định duplicate
ORDER BY CustomerId ASC -- Keep: record có ID nhỏ nhất
) AS RowNum,
COUNT(*) OVER (PARTITION BY Email, FirstName, LastName) AS DupeCount
FROM Customers
)
SELECT * FROM DuplicateDetection WHERE DupeCount > 1;
-- Xóa duplicates (giữ record đầu tiên)
WITH DuplicateDetection AS (
SELECT
CustomerId,
ROW_NUMBER() OVER (
PARTITION BY Email, FirstName, LastName
ORDER BY CustomerId ASC
) AS RowNum
FROM Customers
)
DELETE FROM DuplicateDetection WHERE RowNum > 1;
-- ROWS: Tính theo vị trí vật lý (accurate)
SELECT
SaleDate, Amount,
SUM(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_ROWS
FROM Sales;
-- RANGE: Tính theo giá trị, bao gồm cả ties (có thể không chính xác với duplicates)
SELECT
SaleDate, Amount,
SUM(Amount) OVER (ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_RANGE
FROM Sales;
-- Khi có 2 rows cùng SaleDate:
-- ROWS: Row 1 là subtotal đến row 1, Row 2 là subtotal đến row 2
-- RANGE: Cả hai rows đều hiển thị cùng tổng (tổng đến cuối ngày đó)
-- ROWS nhanh hơn và thường là điều bạn muốn
-- Tìm các dải ngày liên tiếp user login (Islands)
WITH LoginDates AS (
SELECT DISTINCT UserId, CAST(LoginDateTime AS DATE) AS LoginDate
FROM UserLogins
),
Ranked AS (
SELECT
UserId, LoginDate,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate) AS RowNum
FROM LoginDates
),
Islands AS (
SELECT
UserId,
LoginDate,
DATEADD(DAY, -RowNum, LoginDate) AS IslandKey -- Ngày - RowNum = hằng số khi liên tiếp
FROM Ranked
)
SELECT
UserId,
MIN(LoginDate) AS StreakStart,
MAX(LoginDate) AS StreakEnd,
COUNT(*) AS ConsecutiveDays,
DATEDIFF(DAY, MIN(LoginDate), MAX(LoginDate)) + 1 AS TotalDays
FROM Islands
GROUP BY UserId, IslandKey
HAVING COUNT(*) >= 3 -- Chỉ những streak >= 3 ngày
ORDER BY UserId, StreakStart;
-- Tìm Gaps (khoảng trống)
WITH AllDates AS (
SELECT
UserId, LoginDate,
LEAD(LoginDate) OVER (PARTITION BY UserId ORDER BY LoginDate) AS NextLogin
FROM (
SELECT DISTINCT UserId, CAST(LoginDateTime AS DATE) AS LoginDate
FROM UserLogins
) d
)
SELECT
UserId,
LoginDate AS GapStart,
NextLogin AS GapEnd,
DATEDIFF(DAY, LoginDate, NextLogin) - 1 AS GapDays
FROM AllDates
WHERE DATEDIFF(DAY, LoginDate, NextLogin) > 1; -- Gap lớn hơn 1 ngày
-- Cú pháp đầy đủ
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR
select_statement
[FOR UPDATE [OF column_name [,...n]]];
OPEN cursor_name;
FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
FROM cursor_name INTO @var1 [, @var2 ...];
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process...
FETCH NEXT FROM cursor_name INTO @var1;
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
| Type | Mô tả | Use Case |
|---|
| FAST_FORWARD | Read-only, forward-only, nhanh nhất | Khi chỉ cần đi qua một lần |
| STATIC | Snapshot data vào tempdb, data cố định | Khi data có thể thay đổi trong khi đọc |
| KEYSET | Keys được lưu, data rows được đọc lại | Biến đổi data visible, insert không |
| DYNAMIC | Phản ánh mọi thay đổi real-time | Khi cần thấy live changes |
-- FAST_FORWARD: Nhanh nhất, chỉ read-only, forward
DECLARE @OrderId INT;
DECLARE @Amount DECIMAL(18,2);
DECLARE cur_Orders CURSOR FAST_FORWARD FOR
SELECT OrderId, TotalAmount
FROM Orders
WHERE Status = 'Pending'
ORDER BY OrderId;
OPEN cur_Orders;
FETCH NEXT FROM cur_Orders INTO @OrderId, @Amount;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Gọi SP cho từng order (lý do duy nhất dùng cursor)
EXEC dbo.ProcessPendingOrder @OrderId, @Amount;
FETCH NEXT FROM cur_Orders INTO @OrderId, @Amount;
END;
CLOSE cur_Orders;
DEALLOCATE cur_Orders;
-- BENCHMARK: Cursor vs Set-based
-- Cursor approach: O(n) round trips
DECLARE @Id INT;
DECLARE cur CURSOR FOR SELECT ProductId FROM Products;
OPEN cur;
FETCH NEXT FROM cur INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Products SET Stock = Stock - 1 WHERE ProductId = @Id; -- 1 update mỗi lần
FETCH NEXT FROM cur INTO @Id;
END;
CLOSE cur; DEALLOCATE cur;
-- Với 100,000 products: 100,000 update statements, 100,000 lock acquisitions
-- Set-based approach: O(1) - một câu lệnh
UPDATE Products SET Stock = Stock - 1; -- 1 update cho tất cả
-- Với 100,000 products: 1 operation, optimized I/O, parallel execution
-- THAY CURSOR BẰNG:
-- 1. Set-based operations
-- Cursor: update từng row
-- Better: UPDATE Table SET col = expr WHERE condition
-- 2. Window Functions
-- Cursor: dùng biến để track previous row
-- Better: LAG(), ROW_NUMBER(), SUM() OVER()
-- 3. Recursive CTE
-- Cursor: duyệt hierarchy từng node
-- Better: Recursive CTE
-- 4. APPLY (cho SP/function per row)
-- Cursor: EXEC SP cho từng row
-- Nếu SP có thể convert thành TVF, dùng CROSS APPLY
-- 5. Batch processing (nếu phải xử lý từng batch)
DECLARE @BatchSize INT = 1000;
DECLARE @LastId INT = 0;
WHILE 1 = 1
BEGIN
UPDATE TOP (@BatchSize) p
SET p.ProcessedFlag = 1
OUTPUT inserted.ProductId
FROM Products p
WHERE p.ProcessedFlag = 0 AND p.ProductId > @LastId
ORDER BY p.ProductId;
IF @@ROWCOUNT = 0 BREAK;
SET @LastId = @LastId + @BatchSize;
WAITFOR DELAY '00:00:01'; -- Giảm tải
END;
-- 6. STRING_AGG thay vì cursor concatenate strings
-- Cursor approach: dùng biến + concatenation
-- Better:
SELECT
DepartmentId,
STRING_AGG(FullName, ', ') WITHIN GROUP (ORDER BY FullName) AS EmployeeList
FROM Employees
GROUP BY DepartmentId;
| Kỹ thuật | Dùng khi | Tránh khi |
|---|
| View | Simplify complex queries, security layer | Cần parameters (dùng TVF thay) |
| Indexed View | Pre-aggregate large fact tables | Bảng hay INSERT/UPDATE (overhead cao) |
| CTE | Readable queries, recursive, DELETE/UPDATE | Dataset lớn cần index (dùng temp table) |
| Window Function | Ranking, running totals, lag/lead | Không có ORDER BY trong OVER() (cho một số functions) |
| Cursor | Gọi SP từng row, không thể set-based | Mọi trường hợp khác |
-- Checking view dependencies
SELECT
referencing_entity_name,
referenced_entity_name
FROM sys.dm_sql_referencing_entities('dbo.Orders', 'OBJECT');
-- Find expensive window function queries
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.execution_count,
SUBSTRING(qt.text, 1, 200) AS query_snippet
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%OVER%'
ORDER BY avg_elapsed_time DESC;