Returning Top Rows

One of the quickest ways to sample data is through the TOP clause. The TOP clause can select a given number of rows or a percentage of rows from your table. This reduces the traffic passing through your network considerably because you're only retrieving the amount of records that you need. This is a great feature when you have a program that needs to process only a certain amount of records at a time. The TOP clause operates much faster than selecting all the records and requires less network traffic. To use the clause, you can select a given amount of records with the following syntax:

SELECT TOP <number of records> * FROM <table name>

Or select a given percentage of records with this syntax:

SELECT TOP <percent of records> PERCENT * FROM <table name>

For example, use the following statement to select the top 1 percent of the records from the Orders table in the Northwind database:

SELECT TOP 1 PERCENT * FROM Orders

Ordering Views

A common question on the newsgroups is how to order data in a view. The short answer is that you can't do this by default. However, there is a workaround. You
can try to create a view with an ORDER BY clause as shown here:

CREATE View vw_ProductCategories as SELECT     dbo.Products.ProductName, dbo.Categories.CategoryName,               dbo.Products.ProductID FROM dbo.Categories INNER JOIN            dbo.Products ON dbo.Categories.CategoryID =dbo.Products.CategoryID  ORDER BY dbo.Categories.CategoryName 

In the Trenches

One problem that may arise when selecting based on the TOP clause is with compatibility levels. If your compatibility level for the database is set to anything below 70 (SQL Server 7.0), the TOP clause does not work. For example, if you run the following query:

SELECT TOP 10 * FROM Orders

you may receive the following error:

Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '10'.

If you receive this, immediately run the following query to check the compatibility level of the database:

SP_DBCMPTLEVEL 'Northwind'

You see the following result if the level is not set appropriately:

The current compatibility level is 65.

To set the compatibility level back to SQL Server 2000 level, you can use
the same stored procedure with the added parameter ‘80', which represents
the version:

SP_DBCMPTLEVEL 'Northwind', '80'

That code produces the following error:

Server: Msg 1033, Level 15, State 1, Procedure vw_ProductCategories, Line 6 The ORDER BY clause is invalid in views, inline functions,  derived tables, and subqueries, unless TOP is also specified.

The workaround is to use the TOP clause. If you select the TOP 100 PERCENT of the records, which represents all the records, you can place ORDER BY clauses in your view. For example, the following query will allow this:

CREATE View vw_ProductCategories as SELECT     TOP 100 PERCENT        dbo.Products.ProductName, dbo.Categories.CategoryName,         dbo.Products.ProductID FROM dbo.Categories INNER JOIN            dbo.Products ON dbo.Categories.CategoryID =dbo.Products.CategoryID  ORDER BY dbo.Categories.CategoryName