Index Seek in where clause instead of scan

[MartinCroft]

Hi

I have the following bit of code in a where clause

(@BranchID is null or BranchID = @BranchID)

This will cause a clustered index scan where

(BranchID = @BranchID)

does a clustered index seek.

I am sure there is another way of doing the search so if a null @branchID value is passed it returns all values, tried COALESCE and ISNULL but my heads not working today, I am sure there is a way of doing this and I dont want to use dynamic SQL

Mart

 

[markros]

Check

http://sqlserver2000.databases.aspf...ional-parameters-to-my-stored-procedures.html

 

[MartinCroft]

Cheers

Markros, the example worked in the pubs database, but still doing a scan against the table I am using for some reason, but will look further into it

Thanks for your help.

 

[Lamprey13]

If you only have that one parameter to worry about the best performance is to create two separate select statements by using an IF statement, for example:

IF @BrachID IS NOT NULL
BEGIN

	SELECT *
	FROM MyTable
	WHERE BrachID = @BranchID
END
ELSE
BEGIN

	SELECT *
	FROM MyTable
END

Depending on your actaul code, this may or may not make sense to do.

-Ryan

 

[Qik3Coder]

Try this and see if it works:

IsNull(@BranchID, BranchID) = BranchID

-The answer to your problem may not be the answer to your question.

 

[MartinCroft]

Hi

Its actually sevaral parameters, but was trying to get to work with just the one first off, have tried ISNULL and as CASE statement but always does a scan against the table. Seemed to work using dynamic SQL but wasn't an avenue that I wanted to go down if can be avoided. its an indexed view that the data is coming from, but i removed this from the rquation with selecting into a table, basically i cant force it to do a seek. hard coding it works or @parameter = column, but any method to bring through values if no value is passed always does a scan .next step is recreate from scratch a test table on another server.

Mart

 

[SQLDenis]

The optimizer has to evaluate every single row, this is why I always code it like this instead

insert into bla123
select id from master..sysobjects


DECLARE @id int
set @id = 2

if @id is null
select id from bla123
else
select id from bla123 where id = @id

you might want to take a look at sp_executesql at least you will be able to reuse the plan

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions