基本

Created: November-22, 2018

首先，让我们设置示例表。

-- Create a table as an example
CREATE TABLE SortOrder
(
    ID INT IDENTITY PRIMARY KEY,
    [Text] VARCHAR(256)
)
GO

-- Insert rows into the table
INSERT INTO SortOrder ([Text]) 
SELECT ('Lorem ipsum dolor sit amet, consectetur adipiscing elit')
UNION ALL SELECT ('Pellentesque eu dapibus libero')
UNION ALL SELECT ('Vestibulum et consequat est, ut hendrerit ligula')
UNION ALL SELECT ('Suspendisse sodales est congue lorem euismod, vel facilisis libero pulvinar')
UNION ALL SELECT ('Suspendisse lacus est, aliquam at varius a, fermentum nec mi')
UNION ALL SELECT ('Praesent tincidunt tortor est, nec consequat dolor malesuada quis')
UNION ALL SELECT ('Quisque at tempus arcu')
GO

请记住，在检索数据时，如果未指定行排序子句（ORDER BY），则 SQL Server 不保证随时排序（列的顺序）。真的，在任何时候。并且没有必要争论这个问题，它已经在互联网上显示了数千次。

没有 ORDER BY ==没有排序。故事结局。

-- It may seem the rows are sorted by identifiers, 
-- but there is really no way of knowing if it will always work.
-- And if you leave it like this in production, Murphy gives you a 100% that it wont.
SELECT * FROM SortOrder
GO

可通过以下方式订购两个方向的数据：

使用 ASC 上升（向上移动）
使用 DESC 下降（向下移动）

-- Ascending - upwards
SELECT * FROM SortOrder ORDER BY ID ASC
GO

-- Ascending is default
SELECT * FROM SortOrder ORDER BY ID
GO

-- Descending - downwards
SELECT * FROM SortOrder ORDER BY ID DESC
GO

按文本列（（n）char 或（n）varchar）排序时，请注意该顺序是否符合排序规则。有关排序规则的更多信息，请查找该主题。

对数据进行排序和排序会消耗资源。这是正确创建的索引派上用场的地方。有关索引的更多信息，请查找该主题。

有可能伪随机化结果集中的行顺序。只是强制排序看起来不确定。

SELECT * FROM SortOrder ORDER BY CHECKSUM(NEWID())
GO

可以在存储过程中记住排序，如果它是在向最终用户显示行集之前操作行集的最后一步，那么就应该这样做。

CREATE PROCEDURE GetSortOrder
AS
    SELECT * 
    FROM SortOrder 
    ORDER BY ID DESC
GO

EXEC GetSortOrder
GO

对 SQL Server 视图中的排序提供了有限的（和 hacky）支持，但是建议不要使用它。

/* This may or may not work, and it depends on the way 
   your SQL Server and updates are installed */
CREATE VIEW VwSortOrder1
AS
    SELECT TOP 100 PERCENT * 
    FROM SortOrder 
    ORDER BY ID DESC
GO

SELECT * FROM VwSortOrder1
GO

-- This will work, but hey... should you really use it?
CREATE VIEW VwSortOrder2
AS
    SELECT TOP 99999999 * 
    FROM SortOrder 
    ORDER BY ID DESC
GO

SELECT * FROM VwSortOrder2
GO

对于订购，你可以在 ORDER BY 中使用列名，别名或列号。

SELECT * 
FROM SortOrder 
ORDER BY [Text]

-- New resultset column aliased as 'Msg', feel free to use it for ordering
SELECT ID, [Text] + ' (' + CAST(ID AS nvarchar(10)) + ')' AS Msg
FROM SortOrder 
ORDER BY Msg

-- Can be handy if you know your tables, but really NOT GOOD for production
SELECT * 
FROM SortOrder 
ORDER BY 2

我建议不要在你的代码中使用这些数字，除非你想在执行它之后忘记它。