Sorting Data

Question 1

How can you guarantee data is sorted as you wish?

Answer 1

Use an ORDER BY clause

SQL Server 70-461 03-02

Question 2

Which of these two clauses are conceptually evaluated first and why is it important? ORDER BY or SELECT

Answer 2

• 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

Question 3

What is the difference between a query with an ORDER BY clause and one without?

Answer 3

• 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

Question 4

What order is assumed if you don’t indicate a direction for sorting?

Answer 4

Ascending

SQL Server 70-461 03-02

Question 5

How do you specify order direction?

Answer 5

• ORDER BY column DESC
• ORDER BY column ASC

SQL Server 70-461 03-02

Question 6

How can you specify more than one column to ORDER BY?

Answer 6

• ORDER BY column1, column2
• It will sort first by column1 and then by column2

SQL Server 70-461 03-02

Question 7

How can you sort by multiple columns and have different sort directions for each column?

Answer 7

• 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

Question 8

What is the best way to refer to items you want to sort in the ORDER BY clause?

Answer 8

Refer to column names or expressions based on column names.

SQL Server 70-461 03-02

Question 9

Can you order by a column that you are not returning in the query?

Answer 9

• 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

Question 10

When using the DISTINCT clause, what is your ORDER BY list limited to?

Answer 10

ONLY elements that appear in the select list.

SQL Server 70-461 03-02

Question 11

How are nulls treated when sorting?

Answer 11

• 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

Question 12

What type of sort is considered bad practice? And why?

Answer 12

• 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

Question 14

How does indexing affect the ORDER BY clause?

Answer 14

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

Question 15

What happens if you try to order by a column that is not included in the select clause and the DISTINCT statement is used?

Answer 15

The query will fail. Say the query is asking for distinct cities and wants to order by birthdate of the employee cities. Since the query is only asking for one row per city and there could be multiple birthdates for different employees in the same city, instead of making an assumption as to which birthdate to use it just fails.

SQL Server 70-461 03-02

Question 16

What is deterministic ordering?

Answer 16

• 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