基本

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

我建議不要在你的程式碼中使用這些數字，除非你想在執行它之後忘記它。