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:
SET NOCOUNT ON; USE master; GO IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb; GO CREATE DATABASE testdb; GO EXEC sp_dbcmptlevel testdb, 80; GO 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.