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:

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:

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: