*****
If you need to use the SELECT INTO option, keep in mind that it can lock system tables, preventing other users from accessing the data they need while the data is being inserted.
In order to prevent or minimize the problems caused by locked tables, try to schedule the use of SELECT INTO when your SQL Server is less busy. In addition, try to keep the amount of data inserted to a minimum. In some cases, it may be better to perform several, smaller SELECT INTOs instead of performing one large SELECT INTO [2000, 2005, 2008] Updated 2-3-2009
*****
If you need to write a SELECT statement to retrieve data from a single table, don’t SELECT the data from a view that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains the table you are interested in. If you SELECT the data from the multi-table view, the query will experience unnecessary overhead, and performance will be hindered. [2000, 2005, 2008] Updated 2-3-2009
*****
If your application allows users to run queries against your database’s tables, 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 and later, 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, SQL Server 2005 and later 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.
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. [2000, 2005, 2008] Updated 2-3-2009
*****
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. [2000, 2005, 2008] Updated 2-3-2009
*****
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 = ‘xx’)
Here’s a faster way, using IF EXISTS:
IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xx’)
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. [2000, 2005, 2008] Updated 2-3-2009
]]>