Sorting Data Flashcards
How can you guarantee data is sorted as you wish?
Use an ORDER BY clause
SQL Server 70-461 03-02
Which of these two clauses are conceptually evaluated first and why is it important? ORDER BY or SELECT
- SELECT is evaluated before ORDER BY
- This is important because that means you can use column aliases in the ORDER BY clause and not just actual column names.
SQL Server 70-461 03-02
What is the difference between a query with an ORDER BY clause and one without?
- Without the ORDER BY clause the query returns a relational result. i.e. no order is guaranteed
- With the ORDER BY clause, according to standard sql, the query conceptually returns a cursor and not a relation
SQL Server 70-461 03-02
What order is assumed if you don’t indicate a direction for sorting?
Ascending
SQL Server 70-461 03-02
How do you specify order direction?
- ORDER BY column DESC
- ORDER BY column ASC
SQL Server 70-461 03-02
How can you specify more than one column to ORDER BY?
- ORDER BY column1, column2
- It will sort first by column1 and then by column2
SQL Server 70-461 03-02
How can you sort by multiple columns and have different sort directions for each column?
- ORDER BY col1 DESC, col2, col3 DESC
- Remember that when you don’t specify a direction, ascending is assumed. Therefore col2 has an ascending direction.
SQL Server 70-461 03-02
What is the best way to refer to items you want to sort in the ORDER BY clause?
Refer to column names or expressions based on column names.
SQL Server 70-461 03-02
Can you order by a column that you are not returning in the query?
- Yes. Even if you are not going to display column EmpID in the results, for example, you can still order by it.
- The one caveat is that EmpID has to have been a valid entry in the select. Meaning if you wanted to display it in the results by including it in the select clause, you could.
SQL Server 70-461 03-02
When using the DISTINCT clause, what is your ORDER BY list limited to?
ONLY elements that appear in the select list.
SQL Server 70-461 03-02
How are nulls treated when sorting?
- If the column you are sortining on has nulls, the nulls will be grouped together and placed before known values in the sort. This is how SQL Server handles them when the direction is ASC.
- Other database types may handle this differently.
SQL Server 70-461 03-02
What type of sort is considered bad practice? And why?
- Sorting by ordinal position of the columns
- Example: ORDER BY 4,1 (4 stands for the fourth expression in the select list and 1 stands for the first)
Why?
- It is not relational. Attributes in a table have no specific order where the table is relational.
- If you assign ordinal positions to expressions in a select list, what happens if the order of the select list changes and you forget to change the ORDER BY list?
SQL Server 70-461 03-02
What is deterministic ordering?
- When the ORDER BY list creates a unique identity for each row
- An ordering is deterministic if you can guarantee with certainty the order the rows will be returned in is based on the ORDER BY elements you have selected.
- An example is shipperid. if you want to ensure that rows w/ the same shipperid will return in the same order you must also sort by shipdate and orderid since one shipperid could have more than one record with the same shipdate
SQL Server 70-461 03-02
How does indexing affect the ORDER BY clause?
If the column you are ordering by does not have an index, SQL Server may have to sort before addressing your ORDER BY request. This can affect query performance.
SQL Server 70-461 03-02