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.