sql-server - 兼容模式允许新命令?
问题描述
我有一个在 2012 兼容模式下运行数据库的 SQL Server 2017 服务器。但是,当我尝试使用新到 2017 T-SQL 命令string_agg()
时,它可以工作。我预计会出现语法错误,因为string_agg()
它对 2012 模式无效。
这是我正在使用的 SQL:
SELECT
Compatibility_Level,
CASE Compatibility_Level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
WHEN 130 THEN 'SQL Server 2016'
WHEN 140 THEN 'SQL Server 2017 <<<'
ELSE 'new unknown - ' + CONVERT(VARCHAR(10), Compatibility_Level)
END AS VersionName
FROM
sys.databases
WHERE
Name = DB_NAME()
DECLARE @x TABLE (col1 INT, col2 VARCHAR(5))
INSERT INTO @x
VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2)
SELECT * FROM @x
SELECT col1, string_agg(col2,', ')
FROM @x
GROUP BY col1
这是输出:
Compatibility_Level VersionName
------------------- ------------------------
110 SQL Server 2012
(1 row affected)
(5 rows affected)
col1 col2
----------- -----
1 1
1 2
1 3
2 1
2 2
(5 rows affected)
col1
----------- ------------------------------------
1 1, 2, 3
2 1, 2
(2 rows affected)
解决方案
The purpose of Compatibility Mode is to ensure that statements which were valid in previous versions of SQL Server behave in the same way in newer versions of SQL server.
This includes statements which were previously valid but have become invalid in the new version, and statements which have a different meaning or behave differently between versions.
Statements which were invalid in previous versions are not included in this goal.
Why? Consider upgrading. You first want to convert everything to the new syntax and functions, then when you are satisfied everything is ready, you can raise the compatibility level. If you can't allow things which are valid in the new compatibility level (but invalid or meaningless before) then it becomes difficult to migrate to the higher level.
推荐阅读
- excel - 使用动态数组公式转换表格
- android - Android:在隐藏顶部/状态栏的同时实现边到边
- php - 获取当前产品类别所有产品父 sku 并以数组形式显示它们 - Woocommerce
- typescript - 为什么 TypeScript 编译器将函数包装在 (0, module.fn) 中?
- python - 逐元素添加 numpy 数组列表
- matlab - 如何将具有 16 位整数数据的 .dat 文件组转换为 .txt 文件组?
- c++ - 无法开始调试。意外的 GDB 输出
- java - Android ImageAnalysis - 每 n 帧分析一次
- c - 如何检查日期格式是否正确
- java - JSON 到 JSON 的转换:可能的方法