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.
