Tag Archives: Ordering

T-SQL Ordering Myth #2: ORDER BY in Views

• , ,

Since a view is a saved query and queries can specify ordering, adding ORDER BY a view definition might seem a reasonable proposition. Try it and Microsoft SQL Server chokes. Then you learn the trick: include a TOP clause and SQL Server will be a-ok with ordering clauses in views. You want all rows returned so you add “TOP 100 PERCENT” to your definition and SQL Server is happy!

Yet you notice something strange. Sometimes the rows returned aren’t sorted according to the view’s ORDER BY clause. As you ponder this puzzlement, your mind wonders back to the roundabout syntax required to finagle ORDER BY into the view definition. Is something funny going on? But the answer eludes you. Continue reading →

T-SQL Ordering Myth #1: Clustered Indexes Determine Default Sort Order

• , ,

“If I query a table that has a clustered index without specifying an ORDER BY clause, the resultset will be sorted according to the clustered index” may sound reasonable. After all, since “a clustered index determines the physical order of data in a table” (per TechNet), isn’t it logical to assume that a query with no ORDER BY clause returns data sorted this way? Continue reading →