July 14, 2020 at 11:32 am
david.edwards 76768 wrote:Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).
So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.
Well you're right the OP did confirm the fldinfo cannot be NULL. Imo it still makes more sense to have 2 queries (or proc's or whatever) than it does to put "WHERE @parameter IS NULL or..." into 1 query (which I would never do). So I change my original answer to drop the WHERE clause entirely when @parameter is null. It could be 2 procs: 1 with mandatory parameter, and 1 without.
if @parameter is null
select * from test_tblinfo;
else
select * from test_tblinfo where fldinfo=@parameter;
Also not a good idea... Give this a read: https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
July 14, 2020 at 11:34 am
david.edwards 76768 wrote:Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).
So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.
Well you're right the OP did confirm the fldinfo cannot be NULL. Imo it still makes more sense to have 2 queries (or proc's or whatever) than it does to put "WHERE @parameter IS NULL or..." into 1 query (which I would never do). So I change my original answer to drop the WHERE clause entirely when @parameter is null. It could be 2 procs: 1 with mandatory parameter, and 1 without.
if @parameter is null
select * from test_tblinfo;
else
select * from test_tblinfo where fldinfo=@parameter;
Always good to hear differing methods. Optional parameters are the bane of my life, but that's a reasonable well established method of dealing with it, and fairly low cost usually. Useful when there are multiple optional parameters when using IF could get cumbersome.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 14, 2020 at 11:41 am
Also not a good idea... Give this a read: https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/%5B/quote%5D
Ha, my recommendation is 2 procs so there's nothing to confuse. If the OP wants to keep dealing with avoidable issues they could add RECOMPILE or whatever.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 14, 2020 at 12:28 pm
Thanks all for your responses,
My question now is Why? Why does not works as is ? we are suggested to a very strict change control policy
Thanks
Why questions are tricky! https://www.youtube.com/watch?v=36GT2zI8lVA
The OP's original question stated:
"...it is not using indexes, never index seek always table scan."
"if I remove the isnull function then index seek appears"
That's all the "why" SQL developers need to hear. To use an old fashioned analogy: who would use a telephone book that wasn't in alphabetical order?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 14, 2020 at 12:36 pm
Always good to hear differing methods. Optional parameters are the bane of my life, but that's a reasonable well established method of dealing with it, and fairly low cost usually. Useful when there are multiple optional parameters when using IF could get cumbersome.
Lately I've found JSON helps in these situations. If the optional parameters are serialized into an "array of objects" then it's possible to pick off individual pieces with SQL queries and avoid IF/THEN/ELSE...
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 14, 2020 at 12:38 pm
Lou wrote:Thanks all for your responses,
My question now is Why? Why does not works as is ? we are suggested to a very strict change control policy
Thanks
Why questions are tricky! https://www.youtube.com/watch?v=36GT2zI8lVA
The OP's original question stated:
"...it is not using indexes, never index seek always table scan."
"if I remove the isnull function then index seek appears"
That's all the "why" SQL developers need to hear. To use an old fashioned analogy: who would use a telephone book that wasn't in alphabetical order?
No... you recommended using 2 queries. Not two procedures. If you go further up, you'll see that I already suggested adding OPTION(RECOMPILE) if the OP was going to use Scott's solution.
July 14, 2020 at 12:54 pm
It could be 2 procs: 1 with mandatory parameter, and 1 without.
No... you recommended using 2 queries. Not two procedures. If you go further up, you'll see that I already suggested adding OPTION(RECOMPILE) if the OP was going to use Scott's solution.
Maybe so. That's all the OP posted tho. My point is sometimes 2 is better than 1 in these types of situations. It all depends.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 14, 2020 at 7:48 pm
For a single conditional column comparison, there's no reason to use multiple queries or procs, as it's unnecessarily overly complex.
As to using ISNULL(), that should simply never be used in a WHERE clause on a table column. Furthermore, any function used on the column will nearly always prevent SQL from using index seeks rather than having to do index scans (*). In tech-speak, it makes it "non-sargable".
(*) SQL will adjust some date / datetime values for you so that scalar functions still allow seeks, but I wouldn't rely on that crutch, i.e., make the original code clean anyway if at all possible.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply