SQL Server Transact-SQL SELECT Statement

If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data they don't need, consider using the TOP operator within the SELECT statement. This way, you can limit how many rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. For example, the statement:

SELECT TOP 100 fname, lname FROM customers
WHERE state = 'mo'

Limits the results to the first 100 rows returned, even if 10,000 rows actually meet the criteria of the WHERE clause. When the specified number of rows is reached, all processing on the query stops, which has the potential to save SQL Server overhead and boost performance.

The TOP operator works by allowing you to specify a specific number of rows to be returned, like the example above, or by specifying a percentage value, like this:

SELECT TOP 10 PERCENT fname, lname FROM customers
WHERE state = 'mo'

In the above example, only 10 percent of the available rows would be returned.

In SQL Server 2005, a new argument has been added for the TOP statement. Books Online specifies:

[
TOP (expression) [PERCENT]
[ WITH TIES ]
]

Example:

USE AdventureWorks
GO
SELECT TOP(10) PERCENT WITH TIES
EmployeeID, Title, DepartmentID, Gender, BaseRate
FROM HumanResources.Employee
ORDER BY BaseRate DESC

What the WITH TIES option does is to allow more than the specified number or percent of rows to be returned if the values of the last group of rows are identical. If you don't use this option, then any number of tied rows will be arbitrarily dropped so that only the exact number of rows specified by the TOP statement will be returned.

In addition to the above new feature, SQL Server 2005 allows the TOP statement to be used with DML statements, such as DELETE, INSERT and UPDATE. Also, the TOP statement cannot be used in conjunction with UPDATE and DELETE statements on partitioned views.

No changes were made to the SET ROWCOUNT statement in SQL Server 2005, and usually the SET ROWCOUNT value overrides the SELECT statement TOP keyword if the ROWCOUNT is the smaller value.

Keep in mind that using this option may prevent the user from getting the data they need. For example, the data they are looking for may be in record 101, but they only get to see the first 100 records. Because of this, use this option with discretion. [7.0, 2000, 2005] Updated 5-15-2006

*****

In your queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * may prevent the use of covered indexes, further potentially hurting query performance. [6.5, 7.0, 2000, 2005] Updated 5-15-2006

*****

If you need to verify the existence of a record in a table, don't use SELECT COUNT(*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXITS to determine if the record in question exits, which is much more efficient. For example:

Here's how you might use COUNT(*):

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

Here's a faster way, using IF EXISTS:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

The reason IF EXISTS is faster than COUNT(*) is because the query can end immediately when the text is proven true, while COUNT(*) must count go through every record, whether there is only one, or thousands, before it can be found to be true. [7.0, 2000, 2005] Updated 5-15-2006