bridge surrounded by fog

Using SQL Server 2005 Features with Earlier SQL Server Versions

You can use new features in SQL Server 2005 even when your source data resides in a database that's set at a lower compatibility level—one that doesn't support those features. I'll show you how to do so by providing examples that use the TABLESAMPLE clause, which was introduced in SQL Server 2005.

Suppose that in your SQL Server 2005 instance, you have the testdb database that's set at compatibility level 80 (i.e., SQL Server 2000). Testdb contains a table called OrderDetails. Run the following code to create the testdb database, set its compatibility level to 80, and create and populate the OrderDetails table:

USE master;
IF DB_ID('testdb') IS NOT NULL
EXEC sp_dbcmptlevel testdb, 80;
USE testdb;
SELECT * INTO dbo.OrderDatails 
FROM AdventureWorks.Sales.SalesOrderDetail;

You want to use the SAMPLETABLE clause to sample 1000 rows from the table, so you issue the following query while connected to testdb:

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
FROM dbo.OrderDatails TABLESAMPLE (1000 ROWS);

Because testdb’s compatibility level is 80, you can't use features that were introduced in SQL Server 2005. You get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'ROWS'.

However, you can change the database context to one that wasn't set at a lower compatibility level (e.g., tempdb) and query the data referring to database-qualified object names, by using code like this:

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
FROM Sales.SalesOrderDetail TABLESAMPLE (1 PERCENT);

In fact, when you specify a number of rows, SQL Server internally converts it to a percentage.

If you want to ensure that you'll get a data sample that's repeatable (assuming the source table hasn't changed), you can do so by using the REPEATABLE clause and specifying an integer seed value, like this:

USE tempdb;

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
FROM testdb.dbo.OrderDatails TABLESAMPLE (1000 ROWS);

This technique lets you use new product features against source data that resides in a database with a lower compatibility level.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.