SQL Server Magazine

In SQL Server Books Online (BOL), Microsoft documents features that aren’t supported in the current version of SQL Server, deprecated features that won’t be supported in the next version, and deprecated features that won’t be supported in a future version (after the next). I encourage you to thoroughly go over those sections of SQL Server BOL and plan changes in your code where needed to ensure you’ll have a smooth upgrade to a future version of SQL Server when the time comes. Let’s take a look at some of the discontinued and deprecated features in SQL Server 2008 and future versions. I’ll start by addressing changes in behavior of the TOP option, then I’ll discuss a couple of features that are a source for frequently asked questions, and finally I’ll highlight a few discontinued and deprecated features. The features I chose to mention are those that are commonly used and could cause trouble if not addressed, or features that aren’t well understood and require clarification. This article doesn’t provide complete coverage of all the discontinued and deprecated features mentioned in SQL Server BOL. To see a list of deprecated features that won’t be supported in the next version of SQL Server, visit msdn.microsoft.com/en-us/library/ms143729.aspx. To find out which deprecated features are being used in your environment, read the sidebar "Tracking the Use of Deprecated SQL Server Features."

TOP and ORDER BY in Table Expressions
The TOP option and the ORDER BY clause are often sources of confusion. Prior to SQL Server 2005, when you queried through a view that used the ORDER BY clause, the results were always returned in order. However, as of SQL Server 2005, order isn’t guaranteed. To see an example of the lack of order, run the code in Listing 1, to create and populate the table T1 and to create the view V1. Note that Listing 1 doesn’t specify the input to TOP in parentheses (as recommended as of SQL Server 2005) so that you can run the examples in SQL Server 2000. Next, query all the rows from the view without specifying an ORDER BY clause in the outer query using the following code:

SELECT col1 FROM dbo.V1;

If you run this code in SQL Server 2000, the output will be sorted by col1 (1, 2, 3). If you run this code in SQL Server 2005 or later, order isn’t guaranteed. There’s a lot of code out there that relies on the SQL Server 2000 behavior, and this code needs to be addressed if you’re planning to upgrade to SQL Server 2008 or 2005.

Before I describe how you can address this problem, let me explain the reason for the change in behavior. A view is supposed to represent a table; a table is supposed to represent a relation from the relational model; a relation is supposed to represent a set from mathematical set theory; a set has no order to its elements. A query without an ORDER BY clause returns a table in which order of rows isn’t guaranteed, whereas a query with an ORDER BY clause doesn’t return a table but rather what ANSI SQL calls a cursor, which guarantees the order of rows. Because a view is supposed to represent a table, and a query with an ORDER BY clause doesn’t return a table, ANSI SQL doesn’t let you define a view based on a query with an ORDER BY clause. The same applies to T-SQL, although T-SQL supports an exception that has to do with the nonstandard TOP option. However, TOP wasn’t designed with its own ORDER BY clause that defines which rows to filter; instead, TOP relies on the ORDER BY clause that’s typically used in a query for presentation purposes. This design is the source of a lot of confusion, including the confusion surrounding using TOP with ORDER BY in a view (or other table expressions). To use TOP in a view, Microsoft had to let you specify ORDER BY as well. However, what many people don’t realize is that when a TOP query isn’t used to define a table expression, the ORDER BY clause must serve two different functions—defining logical ordering for the TOP option and its usual presentation purpose. Consider the following query:

SELECT TOP 2 col1 FROM dbo.T1 ORDER BY
  col1;

This query guarantees that you’ll get the two rows with the lowest col1 values back, as well as the rows back in the output in col1 ordering. However, when the same query is used to define a table expression (e.g., a view), presentation ordering isn’t supposed to be guaranteed anymore. It’s likely that in terms of optimization to figure out which rows to filter, SQL Server will need to scan an index in order or sort the data if an index doesn’t exist. Then, there’s no reason for SQL Server to rearrange the rows in the output. However, there’s a difference between what’s likely and what’s guaranteed.

Regardless of the implementation aspects, if you understand the fundamentals of the relational model and SQL, you shouldn’t expect a view to guarantee the order of the rows when you query it without specifying an ORDER BY clause in the outer query. Using TOP 100 PERCENT with an ORDER BY clause in the definition of a table expression is an absurd way to try and trick the system into behaving in a way that it’s not supposed to guarantee. The change in behavior that took place as of SQL Server 2005 was considered an optimization improvement; when the optimizer finds TOP 100 PERCENT and an ORDER BY clause in a query defining a table expression, it ignores both, as it should.

All this confusion stems from the problematic design of the TOP option and the way the optimizer handles a view based on a TOP query prior to SQL Server 2005. Perhaps TOP should be redesigned to support its own ORDER BY clause that’s unrelated to the presentation ORDER BY clause, but that’s a topic for future consideration. The current reality is such that there’s a lot of code out there in systems running SQL Server 2000 with views based on TOP 100 PERCENT and ORDER BY, as well as queries against those views without an ORDER BY clause, with DBAs expecting the rows to be returned in order. If you’re planning to upgrade such a system to SQL Server 2008 or 2005, you’ll need to address this problem. The recommended way to resolve the problem is to add an ORDER BY clause to the queries against the views, assuming that’s in your control. Once all those queries are revised, you can modify the views not to use TOP and ORDER BY anymore to avoid future confusion. If modifying the views isn’t an option, or if you need a fast, temporary solution in the short term, there’s a hotfix available in SQL Server 2008 as of Cumulative Update 1 for release to manufacturing (RTM) and in SQL Server 2005 as of Cumulative Update 2 for SP2 that you can download. This hotfix is described at support.microsoft.com/kb/926292 and is supposed to be used only as a temporary solution.

The Use of Semicolon and Ordinal Positions
There are a couple of SQL Server coding features that people often ask about, and I think that this article is a good place to address them. People often ask if SQL Server will eventually require the use of a semicolon as a terminator in all statements. I’m not aware of any plans to make it compulsory in the near future; however, I strongly recommend making it a habit to terminate all statements with a semicolon for several reasons. The semicolon terminator is supported by ANSI SQL and mandatory in some database platforms. For now, only certain statements require the semicolon terminator, but the list gets longer with every new version of SQL Server and there’s a chance that SQL Server will make it compulsory for all statements at some point in the future.

Statements that require the use of a semicolon include the WITH statement used to define a common table expression (CTE) and the new MERGE statement in SQL Server 2008. Regarding the former, the requirement is actually to terminate the statement prior to the WITH statement using a semicolon to signify that the WITH clause doesn’t belong to the previous statement but rather starts a new one. I’ve seen people adopt an approach in which you place a semicolon right in front of the WITH statement as the following code shows:

;WITH CTE_Name AS(...) SELECT …

This approach is intended to ensure that if you add a statement before the CTE definition in the future, you won’t have to worry about terminating it with a semicolon. I have to say that I find this approach to be problematic because it leads to awkward code. If you make it a practice to terminate all statements with a semicolon, you’ll write more elegant code and won’t have to worry about such problems. Plus, you won’t have to worry about it in future versions of SQL Server in which the list of statements that require the semicolon terminator gets longer and longer.

Another question people often ask is whether SQL Server will stop supporting specifying ordinal positions of expressions from the SELECT list in the ORDER BY clause in SQL Server, as in the following example:

SELECT col3, col1, col5
FROM dbo.T1
ORDER BY 3, 1;

Regardless of whether this syntax is standard and SQL Server will stop supporting it in the future, it’s a bad practice to use it in production code because you could make revisions to the SELECT list and forget to make the corresponding revisions to the ORDER BY list. Also, the code is less readable this way. The best practice is to specify the aliases of the expressions that appear in the SELECT list in the ORDER BY clause.

As for conformance to the ANSI SQL standard, ANSI SQL-92 was the last standard that still supported this syntax. ANSI SQL:1999 removed support for this syntax. SQL Server 2008 still supports this syntax, and currently its documentation doesn’t refer to it as a deprecated feature, but it’s on Microsoft’s list of SQL Server features that are on their way out, so it’s recommended to refrain from using it.

Discontinued Features in SQL Server 2008
In SQL Server 2008, Microsoft changed its approach to supporting database backward compatibility levels. In SQL Server 2005, you could set the database compatibility level to any previous version as of 60. As of SQL Server 2008, only two backward compatibility levels are supported. So in the case of SQL Server 2008, you’ll be able to set your database to only compatibility levels 90 (2005) or 80 (2000); lower compatibility levels (i.e., 60, 65, 70) aren’t supported anymore. Therefore, code elements and behaviors that were supported only under those compatibility levels will no longer be supported.

Continue to page 2