Tip: Testing for the Existence of Data

, 2001-12-20

There are times when we

need to know that rows of data that meet specific criteria exist in a table. 

There are 2 basic ways to find this information:  COUNT() and EXISTS().  Here

are some examples.

 

In the Northwind database,

we want to enter orders for a particular customer.  A business rule says that if

this customer has placed an order before, they get a 10% discount.  To determine

this, we need to find out if there are rows in the Orders table for this

customer.

 

Example #1

In this example, we will count the number of

rows in the Orders table for the specified customer.  We will then check to see

if the this count is greater than zero. 

IF (SELECT COUNT(*)

FROM Orders WHERE CustomerID = 'GREAL') > 0

               

BEGIN

/*** PUT DISCOUNT

CODE HERE*/

                END

This technique works well, but an aggregate

function must be computed each time.  We can save this computation time by using

EXISTS().

 

Example #2

 

In this example, the

EXISTS() function returns a boolean value based on the subquery passed to the

function.  If rows exist, the function returns TRUE.

 

IF EXISTS(SELECT NULL FROM Orders WHERE CustomerID = 'GREAL')

BEGIN

/*

** PUT DISCOUNT CODE HERE

*/

END

 

You find that example #2 is

more efficient and helps with performance, especially when used on a table that

has a large amount of data.  To see the performance differences, use the

"Display Estimated Execution Plan" feature in Query Analyzer or set the

SHOWPLAN_ALL option ON before executing the queries.  These will show you the

extra steps needed to process the COUNT() aggregation.

Rate

Share

Share

Rate

Related content

Making Dynamic Queries Static

Building and executing dynamic sql in a stored procedure - is it the only way to solve problems like supporting a simple search function? Leon offers a couple alternatives that let you continue to provide the functionality in a stored procedure without using dynamic sql. Interesting ideas worth exploring!

1 (2)

2002-05-02

12,884 reads

Optimizing Stored Procedures To Avoid Recompiles

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. A database getting thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database users. This article by Randy Dyess shows you some of the ways you can avoid stored procedure recompiles.

3.67 (3)

2002-02-26

21,162 reads

Introduction to ADO Part 4 - Combining It All

In three previous articles Andy has done a very basic introduction to the ADO connection, command, and recordset objects. In this wrap up article he talks about how to use the power of ADO client side filtering and disconnected recordsets, then adds some code which shows how to combine all the objects. ADO is not simple, but Andy has done a good job in limiting his dicussion to the things you REALLY need to know about ADO to get started.

5 (1)

2001-12-07

9,957 reads