sql - 在 SQL Server 中，ASC 关键字的用途是什么，因为 ASC 是默认值？

Question

CREATE TABLE #cities(city_id INT, city_name VARCHAR(100))

INSERT INTO #cities(city_id,city_name)
SELECT 5,'New york' UNION ALL
SELECT 4,'tokyo' UNION ALL
SELECT 2,'Alaska' UNION ALL
SELECT 3,'London'  UNION ALL
SELECT 1,'Banglore'  UNION ALL
SELECT 1,'New york' UNION ALL
SELECT 2,'tokyo' UNION ALL
SELECT 3,'Alaska' UNION ALL
SELECT 4,'London'  UNION ALL
SELECT 5,'Banglore'

我的查询如下:

SELECT * 
FROM #cities 
ORDER BY 2, 1 DESC

SELECT * 
FROM #cities 
ORDER BY 2 ASC, 1 DESC

您可以看到两个查询给出相同的结果。

查看查询输出

那么，ASC 关键字在 SQL Server 中是否有特定用途？

Answer 1

SQL 服务器以这种方式实现它是因为 SQL ANSI specifications 明确说明实现为

The ORDER BY clause provides your DBMS with a list of items to sort, and the order in which to sort them: either ascending order (ASC, the default) or descending order (DESC).

和

Here's a simple example:

SELECT column_1 FROM Table_1 ORDER BY column_1;

This SQL statement retrieves all COLUMN_1 values from TABLE_1, returning them in ascending order. This SQL statement does exactly the same:

`SELECT   column_1 FROM Table_1 ORDER BY column_1 ASC;`

因此关键字 ASC 在 MS SQL 服务器的 ORDER BY 子句中也被省略了，因为它正确地实现了 ANSI 规范。

我愿意相信它的存在是为了帮助人们轻松理解其他人编写的查询。

另外请注意，您不能在 SQL Server 中将默认顺序从 ASC 更改为 DESC。

正如 MSDN documentation 中所述, 请不要在您的语法顺序中使用 2,1 DESC 样式。

Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For example, although a statement such as SELECT ProductID, Name FROM Production.Production ORDER BY 2 is valid, the statement is not as easily understood by others compared with specifying the actual column name. In addition, changes to the select list, such as changing the column order or adding new columns, will require modifying the ORDER BY clause in order to avoid unexpected results.