Microsoft SQL Server 2012 Bible

Ordering the Result Set

Logically, relational data should always be considered an unordered list. The primary key's purpose is to uniquely identify the row, not sort the table. SQL Server usually returns the data in the order of the primary key (because that's probably the clustered index), but there's no logical guarantee of that order. The only correct way to sort the results is with an ORDER BY clause.

SQL can sort by multiple columns, and the sort columns don't have to be columns that are returned by the SELECT, so there's a lot of flexibility in how you can specify the columns. Using Management Studio's Query Designer, you can create the ORDER BY by selecting the sort order for the column, as shown in Figure 6.4.

Figure 6.4 Within Management Studio's Query Designer, you can define the sort order and sort type in the column pane. The TOP() predicate is set for the Query Designer inside the query's Properties page.

Although there is not a limit on the number of columns that you can specify in the ORDER BY clause of the select statement, an internal operation exists that indirectly enforces a limit. During a sort operation, a worktable see Chapter 44, “Interpreting Query Execution Plans” is created. This worktable has a maximum row size of 8060 bytes. As a result, this limits the total size of the columns specified in an ORDER BY clause.

Specifying the ORDER BY Using Column ...