SQL Server Transact-SQL SELECT Statement

When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final recordset. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset. If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows.

On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement. The advantage of UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being used. [2000, 2005, 2008] Updated 2-3-2009



SELECT column_name1, column_name2

FROM table_name1

WHERE column_name1 = some_value


SELECT column_name1, column_name2

FROM table_name1

WHERE column_name2 = some_value

This same query can be rewritten, like the following example, and when doing so, performance will be boosted:

SELECT DISTINCT column_name1, column_name2

FROM table_name1

WHERE column_name1 = some_value OR column_name2 = some_value

And if you can assume that neither criteria will return duplicate rows, you can even further boost the performance of this query by removing the DISTINCT clause. [2000, 2005, 2008] Updated 2-3-2009




The DISTINCT clause creates a lot of extra work for SQL Server, and reduces the physical resources that other SQL statements have at their disposal. Because of this, only use the DISTINCT clause if it is necessary. [2000, 2005, 2008] Updated 2-3-2009





Pages: 1 2

Related Articles :

  • No Related Articles Found

