Pattern and Practices - deprecate and discontinued Transact SQL features
Deprecate and Discontinued features, are not a words for Developers and DBAs which is a first point of option that they must be concentrated.
What they mean is the defined features include a complete/partial change in supported compatibility levels, for instance within SQL Server 2000 version when TOP clause used inside a cursor then implicit cursor conversion will be fast-forward, whereas within SQL 2005 verison it will be static. Similarly for ORDER BY clause within SQL 2000 version the behaviour is: Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified that differs in SQL 2005 as: Qualified column names and aliases are resolved to columns of tables listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement. You could find such information from SQL BOL aka Books OnLine, I know it is easy to say that refer to such documentation for such information, which is a best practice when you need to obtain specific information about features.
Coming back to the topic several T-SQL features have been discontinued, are deprecated in SQL Server 2008, in other words they won’t be supported in a future SQL Server version. This is nothing a complete change of supported feature which requires a re-write of TSQL code/script that has been used for considerable amount of time (years). Such features include a change in supported compatibility levels, and talking about SQL 2008 new features & introduction of new data types (for instance the TIMESTAMP type) can help you accomplish the task with few lines of code instead of multiple-conditional code from the previous versions. Another Before you upgrade, you need to remove the discontinued or deprecated T-SQL features in old code and refrain from using them in new code.
Let us take into account of few of the discontinued and deprecated features in SQL Server 2008 (and future versions). During a study at client's place for Performance tuning & optimization audit exercise, I have seen the problems defined below and for further information on Deperated & discontinued feature information of such information refer to "Tracking the Use of Deprecated SQL Server Features link. I have also seen that usage of TOP & ORDER BY clauses are heavy, as mentioned above they tend to lead fo sources of confusion. One of the Microsoft documentation refers that : 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 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 BLOCKED EXPRESSION. 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.
I believe that no matter you follow the best practices on scripting & coding, periodically you must revisit them (by testing) to ensure they are executed as expected and regardless of the implementation aspects, if you understand the fundamentals of the relational model and SQL it will be easy to manage when such version changes occurs. Take that into example of above clause that when ORDER BY is used within a view then 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. Similarly when 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.
We must consider about how SQL optimizer handles a view or stored procedure that uses TOP query within SQL 2000 version, so you should consider in redesign to support its own ORDER BY clause that’s unrelated to the presentation ORDER BY clause, it has a large scope of discussion to proceed in that direction. By default with ORDER BY clause you would expect that returned in order from the code originated from 2000 version. So if you are considering or planning to upgrade such a system to SQL Server 2008 or 2005, you’ll need to address this problem. Microsoft documentation recommends that 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 or stored procedure isn’t an option within your environment then you have a temporary solution that can be used as short term with a further test of hotfix for SQL Server 2008 Cumulative Update 1 package release.
I can see that these are commonly used and could cause trouble if not addressed, or features that aren’t well understood and require clarification. I tend to continue this topic of deprecate & discontinued features in upcoming blog posts, as continuing all in 1 blog post may not be easy and keep watching this space.