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