Deprecated T-SQL Features

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 Semicolonand 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 statementsat 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, col5FROM dbo.T1ORDER 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

The BACKUP LOG WITH TRUNCATE_ONLY option isn’t included in SQL Server 2008. This option was used in the past to indicate that you wanted to truncate the log without actually backing it up and enter a log truncate mode that’s similar to working under the Simple recovery model. There’s no replacement for this option; instead, simply remove all occurrences of this statement from your code. If you don’t back up your logs and need SQL Server to automatically truncate the log, set the database recovery model to Simple.

SQL Server 2008 discontinues support for the Northwind and pubs sample databases. It supports the Adventure- Works family of sample databases (i.e., Adventure- Works, Adventure- WorksDW, Adventure- WorksLT) that you can download via CodePlex. You might find it a bit odd that Microsoft would discontinue support for sample databases. Besides the introduction of newer sample databases, there were changes in the internal guidelines regarding the use of people and company names, phone numbers, and addresses. The scripts used to create Northwind and pubs in SQL Server 2005 are available for download at go.microsoft.com/fwlink/?LinkId=30196, and they work just fine in SQL Server 2008. However, these databases aren’t supported and aren’t supposed to be used in official Microsoft documentation such as BOL or books published by Microsoft Press. I think that’s a shame because I find Northwind to be a very good sample database for learning purposes. It contains small amounts of data but with enough variety and a simple model for creating useful examples. Although you can still download Northwind and pubs, I created sample databases that are similar to Northwind, with a few improvements, using new guidelines and fictitious names I got from Microsoft. You can find the sample databases TSQL Fundamentals2008 and InsideTSQL2008 at www.insidetsql.com as part of the source code download for my books about SQL Server 2008. Note that this section was just a sampling of the discontinued features. To see the full list, visit msdn.microsoft.com/en-us/library/ms144262.aspx.

Deprecated Features in the Next Version of SQL Server

This section highlights a few features that aren’t going to be supported in the next version of SQL Server (aka version 11). As I mentioned earlier, starting with SQL Server 2008, only two database backward compatibility levels are supported, meaning that the next version won’t support compatibility level 80. Features and behaviors that were available only under compatibility level 80 won’t be supported anymore. Examples of such features include the old-style syntax for outer joins (e.g., *=, =*) and the temporary solution for the problem with TOP and ORDER BY in views that I discussed earlier. So if you have code that uses those language elements or relies on those behaviors, now is a good time to start planning for the required revisions.

The next version of SQL Server also won’t support working under the ANSI_NULLS OFF mode. This mode means that a comparison between two NULLs results in TRUE rather than UNKNOWN as dictated by ANSI SQL. It’s recommended that you revise all code that relies on this behavior. For example, instead of col1 = NULL use col1 IS NULL, and instead of col1 <> NULL use col1 IS NOT NULL. When referring to a parameter and needing to treat a comparison between two NULLs as TRUE, use col1 = @p OR (col1 IS NULL AND @p IS NULL) instead of col1 = @p. Note that it’s not recommended to use the ISNULL or COALESCE functions to substitute NULL with a known value that can’t appear in the data because once you apply manipulation on the filtered column, the optimizer can’t rely on index ordering anymore.

In addition, the next version of SQL Server won’t support the SET ROWCOUNT option for INSERT, UPDATE, and DELETE statements. This session option causes SQL Server to stop processing a statement once the specified number of rows is processed. The problem with this option is that it has a global effect on the session, meaning that if, for example, the modification statement causes a trigger to fire, the code within the trigger is also bound by the session’s ROWCOUNT limitation. Instead, you should use the TOP query option. As of SQL Server 2005, the TOP option supports an expression as input (e.g., a parameter) and is supported with both data modifications and data retrieval statements. So there’s no reason to use the SET ROWCOUNT option anymore. Although the current plan is to stop supporting the SET ROWCOUNT option with modification statements first, it’s recommended to replace SET ROWCOUNT with the TOP query option in all statements, including SELECT statements.

Deprecated Features in Future SQL Server Versions

Some deprecated features that SQL Server won’t support in future versions require attention because there’s a lot of code still using them. The following are a few features that Microsoft plans to remove from future SQL Server versions:

• Compatibility level 90.
• The TIMESTAMP data type. As a replacement, use ROWVERSION. The two aren’t really different types, but rather are the old and new names of the same data type.
• The TEXT, NTEXT, and IMAGE types. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.
• Default and rule objects. As a replacement, use the DEFAULT and CHECK constraints.
• Compatibility views such as sysobjects, syscolumns, syscomments, and sysprocesses. Instead, use catalog views and dynamic management objects. SQL Server BOL includes a section with a mapping between compatibility views and the corresponding catalog views and dynamic management objects that you can view at msdn.microsoft.com/en-us/library/ms187997.aspx.
• Several Database Consistency Checker (DBCC) commands such as DBCC DBREINDEX and DBCC INDEXDEFRAG. Use ALTER INDEX REBUILD and ALTER INDEX REORGANIZE instead of the aforementioned examples.
• Several system procedures, including sp_attach_ db. Use the newer alternatives (e.g., CREATE DATABASE FOR ATTACH) instead. • Column aliasing using the form 'string_alias' = expression. Instead, use one of the supported forms. My personal preference is the form expression AS alias.

Addressing the Use of Deprecated Features

Before upgrading to a newer version of SQL Server, you’ll need to address the use of features that are discontinued in the target version. Better yet, if you aren’t planning to upgrade to a newer version of SQL Server in the near future, I recommend investing the time to thoroughly go over the sections in SQL Server BOL that discuss the discontinued and deprecated features, and gradually address those in your existing code. Also, make sure you’re not using deprecated features in new code. Doing so well ahead of a planned upgrade will help prevent problems and ensure a smooth upgrade in the future. I’d like to thanks Greg Low, Rubén Garrigós, Kevin G. Boles, and other members of Solid Quality Mentors, as well as Michael Coles, Tibor Karaszi, Erland Sommarskog, and other MVPs, for their input regarding the deprecated features.