SQLServer数据库跨版本升级中的兼容问题

-- 原2016版本高效查询（兼容级别130）
SELECT * FROM Orders 
ORDER BY CreateTime DESC
OFFSET 1000 ROWS 
FETCH NEXT 20 ROWS ONLY;

-- 2019版本需要调整索引策略
CREATE NONCLUSTERED INDEX IX_Orders_CreateTime
ON Orders(CreateTime DESC)
INCLUDE (TotalAmount, CustomerID);

-- 旧版语法（兼容级别120）
DECLARE @Log TABLE (ID INT);
DELETE FROM Products 
OUTPUT DELETED.ProductID INTO @Log
WHERE Discontinued = 1;

-- 新版要求（兼容级别130+）
DECLARE @Log TABLE (ID INT PRIMARY KEY); -- 必须定义主键
DELETE FROM Products 
OUTPUT DELETED.ProductID INTO @Log(ID)   -- 显式列映射
WHERE Discontinued = 1;

-- SQL Server 2012的等待统计查询
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%';

-- 2016+版本新增列
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms -- 新增列
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'PAGEIOLATCH%';

-- 即将失效的语法
SELECT * FROM Documents
WHERE CONTAINS(Content, 'ISABOUT("SQL" WEIGHT(0.8), "Database" WEIGHT(0.2))');

-- 替代方案
SELECT * FROM Documents
WHERE CONTAINS(Content, '"SQL" OR "Database"')
AND document_score > 0.5; -- 使用新增的评分函数

-- 示例表结构
CREATE TABLE Sales (
    OrderID INT PRIMARY KEY,
    RegionCode CHAR(2),
    SaleDate DATETIME,
    Amount MONEY
);

-- 旧版执行计划使用索引查找
SELECT RegionCode, SUM(Amount) 
FROM Sales
WHERE YEAR(SaleDate) = 2022
GROUP BY RegionCode;

-- 2019可能选择列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON Sales (SaleDate, RegionCode, Amount);

-- 旧版备份脚本
EXEC sp_addrolemember 'db_backupoperator', 'BackupUser';

-- 新版需要额外授权
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION TO BackupUser;
ALTER ROLE db_backupoperator ADD MEMBER BackupUser;

-- 阶段式升级脚本
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 130; -- 2016级别
EXEC dbo.RunCompatibilityTests; -- 自定义测试过程

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 140; -- 2017级别
EXEC dbo.RunCompatibilityTests;

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 150; -- 2019级别

ALTER DATABASE MyDB SET QUERY_STORE = ON;
ALTER DATABASE MyDB SET QUERY_STORE CLEAR;

-- 升级后对比计划
SELECT 
    qsp.query_id,
    qsqt.query_sql_text,
    qsp.plan_id,
    qsp.last_execution_time
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsp.last_execution_time > '2023-01-01';

CREATE EVENT SESSION [UpgradeMonitor] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([severity]>(10)))
ADD TARGET package0.event_file(SET filename=N'UpgradeMonitor')
WITH (STARTUP_STATE=OFF);