SQL Server中视图关于ORDER BY的限制

SQL Server数据库中在视图中使用ORDER BY是有限制的, 如下所示:

CREATE VIEW Maint.V_BlockingSQLHistory
AS
SELECT [RecordTime],[DatabaseName], [Blocking_SessionId],[Blocked_SessionId]
FROM [Maint].[BlockingSQLHistory]
ORDER BY [RecordTime];

如果在视图中这样使用ORDER BY,就会遇到错误提示信息.如下所示

Msg 1033, Level 15, State 1, Procedure V_BlockingSQLHistory, Line 5 [Batch Start Line 0]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

其实也不是说视图中绝对不能使用ORDER BY,而是使用ORDER BY是有条件的,除非你同时指定了TOP, OFFSET, FOR XML子句. 如果将上面视图改成下面这样就OK.

CREATE VIEW Maint.V_BlockingSQLHistory
AS
SELECT TOP 10 [RecordTime],[DatabaseName], [Blocking_SessionId],[Blocked_SessionId]
FROM [Maint].[BlockingSQLHistory]
ORDER BY [RecordTime];

官方文档的解释/介绍如下:

The ORDER BY clause isn't valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET 
and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows 
returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause doesn't guarantee ordered results when these constructs
 are queried, unless ORDER BY is also specified in the query itself.

在视图、内联函数、派生表和子查询中，ORDER BY 子句是无效的，除非同时指定了TOP 或 OFFSET 和 FETCH 子句。当在这些对象中使用 ORDER BY 时，
该子句仅用于确定由 TOP 子句或 OFFSET 和 FETCH 子句返回的行。除非在查询本身中也指定了 ORDER BY，否则这些构造被查询时，ORDER BY 子句不能
保证结果的有序性。

参考资料

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver17&redirectedfrom=MSDN