SQL Server 2008 TSQL features, rather we spotlight on deprecated and discontinued features?
As referred on the Pattern and Practices - deprecate and discontinued Transact SQL features we will now look into (my favs) on SQL Server 2008 TSQL features, rather we spotlight on deprecated and discontinued features!
IT is one of the best practice that you need a persistent planning to change the issues in addressing the use of deprecated features. One of the common criteria I have seen about using ALIAS that will enable logins to assume the identity of a user in a database, thereby gaining the permissions of that user while working in that database. But going forward this category is rather classed as discointued feature with a replace aliases with a combination of user accounts and database roles, whereby using CREATE ROLE and CREATE USER code. I also stress the main task about, 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. I stress that say if you are not planning to upgrade to newer version in the near future, still it is a best option for you to invest time in finding the existing code thoroughly to go over the sections in SQL Server BOL that discuss the discontinued and deprecated features, and gradually address those in your existing code.
Ok, when we talk about the code and new coding features that people often ask about, one of them I like semicolon & ordinal position.It is one of the best practice that SQL Server will eventually require the use of a semicolon as a terminator in all statements, though it is not a compulsory but it is better 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. Not only that when semicolon include the WITH statement used to define a common table expression (CTE) and the new MERGE statement in SQL Server 2008 and with the CTE 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. Such as : ;WITH CTE_Name AS(...) SELECT
Let us talk on ordinal positions within the expressions from the SELECT list in the ORDER BY clause in SQL Server, as in the following example from BOL:
SELECT Name,Address,City FROM dbo.TableName ORDER BY 3, 1;
So 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.
Now to the discontinued features within 2008 version, it has been changed the approach of supportng backward compatibility levels on database level, in particular. Within SQL 2005 version you can easily go back until number 60 (SQL 6.0 version), but in 2008 (100) version only last 2 compatibility levels 90 & 80 and below 70 are not supported anymore causing any code elements and behaviors that were supported only under those compatibility levels will no longer be supported.Such as 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.
Also you cannot use Northwind and PUBS sample databases, as it deals with AdventureWorks family (i.e., Adventure- Works, Adventure- WorksDW, Adventure- WorksLT) where you can download via CodePlex.com site. Practically I can still work with pubs database as a sample database for any code examples by downloading the SQL 2005 versioned database.
Also it is very early to talk about what will be the strategy for deprecated & discontinued features for SQL Server 2008 R2 version and I think the backward compatibility setup of 80 is not supported anymore which gives only back of 2 versions, 90 & 100. So when you need to use the main TSQL 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. Also working the code with ANSI_NULLS OFF mode which works to compare between 2 NULLS results in TRUE rather than UNKNOWN as dictated by ANSI SQL, so this is the good time to review the code for such placements. Also SET ROWCOUNT option for INSERT, UPDATE, and DELETE statements will not be supported. Going further you can only use TOP query option,as it 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.