| Downloads |
|---|
| 22992.zip |
In SQL Server/Office Integration: "Stored Procedures and Access 2000" (March 2000), Rick Dobson noted that SQL Server 7.0 and 6.5 don't allow the ORDER BY statement in views. Brad Aisa's Reader to Reader tip "Ordering by SQL Views" (October 2000) offered a workaround for this problem. I'd like to add a tip. Our company used the stored procedure sp_dbcmptlevel to enable the TOP and ORDER BY statements for SQL Server 7.0 views.
Initially, all our company's databases were in SQL Server 6.5, and after we migrated to SQL Server 7.0, we discovered that we needed to use the ORDER BY statement in various views to return ordered data. SQL Server 6.5—compatible databases don't allow the TOP statement, even when you run the databases in SQL Server 7.0. Running the system stored procedure sp_dbcmptlevel makes the databases SQL Server 7.0—compatible and enables the ORDER BY statement in views that use TOP. Listing 3 shows the script we used to enable the TOP statement in SQL Server 7.0. You can select the required database through the Query menu or through the USE statement (i.e., USE <DatabaseName>). The SQL script that Listing 3 shows declares a variable to capture the database name so that the procedure can run on any database without requiring the database name.
Using TOP and ORDER BY can cause a performance penalty on the data return. Performance might suffer if you use ORDER BY on a column that's not indexed or if you reorder a clustered index. To analyze performance cost, use Query Analyzer to examine the execution plan that the view uses.