SQL Selects on subsets

Question

I need to check if a row exists in a database; however, I am trying to find the way to do this that offers the best performance. This is best summarised with an example.

Let's assume I have the following table:

dbo.Person(
FirstName varchar(50),
LastName varchar(50),
Company varchar(50)
)

Assume this table has millions of rows, however ONLY the column Company has an index.

I want to find out if a particular combination of FirstName, LastName and Company exists. I know I can do this:

IF EXISTS(select 1 from dbo.Person where FirstName = @FirstName and LastName = @LastName and Company = @Company)
Begin
....
End

However, unless I'm mistaken, that will do a full table scan.

What I'd really like it to do is a query where it utilises the index. With the table above, I know that the following query will have great performance, since it uses the index:

Select * from dbo.Person where Company = @Company

Is there anyway to make the search only on that subset of data? e.g. something like this:

select * from (
  Select * from dbo.Person where Company = @Company
)
where FirstName = @FirstName and LastName = @LastName

That way, it would only be doing a table scan on a much narrower collection of data.

I know the query above won't work, but is there a query that would?

Oh, and I am unable to create temporary tables, as the user will only have read access.

Answer 1

IF EXISTS( ...) is the fastest form. The optimiser will use an available index to satisfy the filter condition if it calculates the index will be faster. IF EXISTS will quit as soon as a row is found.

Make sure your statistics are up to date...

Answer 2

FWIW, this is valid SQL:

select *
from (select * from dbo.Person where Company = @Company) t
where t.FirstName = @FirstName and t.LastName = @LastName

And only differs from your query by an alias. The IF EXISTS will be faster as Mitch says in his post.

You can use the query analyzer to find out what the optimizer will decide to do.