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:
IF DB_ID('testdb') IS NOT NULL
DROP DATABASE testdb;
CREATE DATABASE testdb;
EXEC sp_dbcmptlevel testdb, 80;
SELECT * INTO dbo.OrderDatails
You want to use the SAMPLETABLE clause to sample 1000 rows from the table, so you issue the following query while connected to testdb:
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:
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:
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:
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.