Hi, I have one query about how to check wether record exists or not with particular UserId
following are the options.I have used function for this.

Case-I

if exists(select productID from ProductShipped where customerId=10)
  begin
   //for customerID=10 at least one product is shipped
   return 1
  end
return 0

Case-II
if exists(select top 1 productID from ProductShipped where customerId=10)
  begin
   //for customerID=10 at least one product is shipped
   return 1
  end

return 0


Case-III
if exists(select top 1 1 from ProductShipped where customerId=10)
  begin
   //for customerID=10 at least one product is shipped
   return 1
  end

return 0

Which option should be the best option for query optimization.

assuming that ProductShipped table contains 100K of records.

One more thing ,when we use
 select top 1 ProductID from productShipped
does SQL Server internally create temporary table? and from there it shows top 1 record??

In above query where clause contains only one conditions while in real life situation it actually
contains more conditions

Any pointer that help me to select the best option.

Thanks in Advance



Cheers :)

vinod

__________________
Cheers :)

vinod

AFAIK, it doesn't need a temp temp table. All it needs to know is whether a record matching your criteria exists. So, as soon as it finds the first record, it can return true without bothering with the actual select statement.

I could be wrong, but I don't think you need the TOP 1 clause. After all, again all that you need to know is whether a record exists or not.....

You could even do this:

IF EXISTS (SELECT 1 FROM ProductShipped WHERE customerId = 10)

Since you only need to get True or False, there's no need to specify a column.

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Actually,

If EXISTS (SELECT * FROM ProductShipped WHERE customerId = 10)

is in general more efficient.

The EXISTS predicate returns TRUE when the subquery given as its argument has any rows in it at all. The SELECT * allows the optimizer to select which column to use to satisfy the query, rather than directing it to use a specific one.

If your ProductShipped table has an index on the customerID column, then the optimizer can determine that the subquery has rows in it from the index, without having to actually SELECT a column from the table itself, which you force it to do by specifying the column in the SELECT clause.

The TOP 1 clause is very much worse, as that requires that the subquery fully generate all its rows before the result is limited by the TOP expression.

(Note that SELECT * in the EXISTS predicate subquery is an exception to the "rule" that says you really shouldn't use SELECT * in your queries, but rather always explicitly specify the columns you want selected)

Jeff Mason
[email protected]

Hii Imar and Jeff !!

Great Help from you.

Thank you very much.



Cheers :)

vinod