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.