CODEWITHSUNDEEP
sqlsql-servert-sql
Ranjan Panda
Ranjan Panda

Reputation: 81

What is the use of ASC keyword in SQL Server as ASC is the default?

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'

And I write my queries like below:

SELECT * 
FROM #cities 
ORDER BY 2, 1 DESC

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

As you can see both the query giving same result.

See query output

enter image description here

So, is there a specific use of ASC keyword in SQL Server?

Upvotes: 3

Views: 13189

Answers (4)

alex
alex

Reputation: 18

The ORDER BY default order is ASC.

So your query is the same as:

SELECT * 
FROM #cities 
ORDER BY 2, 1 DESC 

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

Upvotes: -1

Reversed Engineer
Reversed Engineer

Reputation: 1242

There is no specific use, except for clarity of code - showing the intent of the code. Since code is not just for writing, but also for reading by other people, putting in ASC even when it is not needed, can make the intention of that piece of code clearer, if the fact it's ascending is important there.

Please see this article about Intentional Programming, which says:

the programmer can express intentions explicitly in their code, rather than implicitly via inadequate language features

The same principle is well said in one of the guiding principles of the Python programming language:

Explicit is better than implicit.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

There is no specific use by mentioning ASC in Order by. Some one new to Sql can understand it better if you are mentioning it. By default, if you are not mentioning the sort order(asc/desc) of columns in Order By then sql server will consider it as ASC order. Thats why you are getting same result.

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17126

SQL server implemented it that way because of the SQL ANSI specifications which clearly state the implementation as

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).

and

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;`

And therefore the keyword ASC is omit-table in ORDER BY clause in MS SQL server too as it implemented the ANSI specifications correctly.

I would like to believe that it exists to help people easily understand a query written by others.

Also please note that you cannot change the default order from ASC to DESC in SQL server.

And as mentioned in the MSDN documentation, please refrain from using 2,1 DESC style in your order by syntax.

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.

Upvotes: 7

Related Questions