sql - 在 90 或更高版本的兼容模式下，ORDER BY 子句中不允许使用常量表达式

Question

将数据库从 SQL Server 2014 升级到 SQL Server 2016。升级顾问说我有一个 View 给我这个问题:

Object [dbo].[view_PHistory] uses a constant expression which are not allowed in the ORDER BY clause in database compatibility level 90 or later.

Constant expressions are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. However, these expressions in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90 or later.

Here is an example of such problematic statements:

SELECT * FROM Production.Product ORDER BY CASE WHEN 1=2 THEN 3 ELSE 2 END"

Before you change the database compatibility mode to 90 or later, modify statements that use constant expressions in the ORDER BY clause to use a column name or column alias, or a nonnegative integer representing the position of the name or alias in the select list.

这是我的观点:

ALTER VIEW [dbo].[vw_PHistory]
AS
    SELECT  
        table1.SerialNo,
        ROW_NUMBER() OVER (PARTITION BY table1.SerialNo 
                           ORDER BY DATEADD(ms, table2.TimeStamp, table3.RegTime) DESC) AS LatestPoll    
    FROM    
        table1 
    INNER JOIN
        table2 ON table2.SerialNo = table1.SerialNo
               AND table2.Counted = 1 
    LEFT OUTER JOIN
        table3 ON table3.scanID = table2.scanID

此 View 在兼容级别为120的SQL Server 2014上运行没有问题。

当我将 ORDER BY DATEADD 函数与特定列名交换时，升级顾问的错误提示消失了。

此外，我知道 DATEADD 函数不会返回常量表达式。我不知道为什么会引发此错误。过去两天我一直在谷歌搜索。

有人愿意在这方面阐明我吗？

提前致谢。非常感谢。

编辑:TimeStamp 是 int，RegTime 是 dateTime，例如 2009-12-08 17:23:36.267

Answer 1

我认为问题出在 LEFT OUTER JOIN 到 table3 以及 ORDER BY< 中 table3.RegTime 的使用 子句。如果 RegTime 为 NULL，则表达式将是常量且无法计算。