SQL 2000 — Argh!

Dang you, SQL Server 2000! Yes, it was a big step forward from SQL Server 7, but jeez there is a lot of functionality that is just so convenient since SQL Server 2005. Here’s another example, this time using the system functions in combination with metadata functions.

-- Set the context to a compatibility level 80 (SQL Server 2000) database
USE [db80];
GO

-- Try using the DB_ID() function within a system table-valued function
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
;

While the previous statement works fine with compatibility level 90 (SQL Server 2005) or greater, it fails for level 80. You’ll receive a syntax error around the “(“. And yet, both the DB_ID() function and the index physical stats function work by themselves at level 80.

-- Both the system function and the metadata function work
SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS ips
WHERE ips.database_id = DB_ID()
;

So, what’s the big deal? Well, if you’re in an environment that still supports SQL Server 2000 databases (maybe even side-by-side with newer databases on the same SQL server?), watch your step. Don’t expect to be able to develop for 2005 (not to mention 2008, R2, or 2012) and easily track down and fix the errors you get when running commands at the server level.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: