2

We used Stored Procedure on our Queries. Some of our field got NULL value, so for us to get this value we Put the conversion of ISNULL inside the WHERE condition but per checking it affects the Process of our strodproc based on the SQL performance tool.

Ex.

SELECT * FROM tblInfo
WHERE ISNULL(fldInfo,'') <> ''

tblInfo

fldinfo
NULL
30
NULL
20

Query 

SELECT * FROM tblinfo WHERE fldinfo NOT IN (30,20) - different result
SELECT * FROM tblinfo WHERE ISNULL(fldinfo,'') NOT IN (30,20) - Correct

Result

Any other Substitute process of script we can use so that we can get the value but not affecting the performance of the query.

Nirav Joshi
  • 2,924
  • 1
  • 23
  • 45
BebeArg
  • 23
  • 6
  • This question reminds me a little bit of the Brexit negociations: You seem to want to *have* your cake *and eat it* at the same time. I. e. you want to enjoy the liberty of leaving the "empty" `fldinfo` entries `null` but at the same time you want to profit from a "sargable" behaviour whenever carrying out comparisons. Is there a possibility of changing your column's content or of creating a dependent column with `''` instead of `null`s? – Carsten Massmann Jul 07 '17 at 05:37
  • @cars10 , Hi, thanks for replying. Currently the old structure is got the NULL value already and we cannot change that value anymore – BebeArg Jul 07 '17 at 07:03
  • Have you tried `SELECT * FROM tblinfo WHERE ISNULL(fldinfo,0) NOT IN (30,20)` – bastos.sergio Jul 07 '17 at 09:50
  • @cars10, yeap i tried, the ISNULL is working. but in terms of Performance i want to change it, even though we put index and re index ta database, it still hit deadlock and per checking in sql doctor its because of the ISNULL function. – BebeArg Jul 07 '17 at 10:06

3 Answers3

2

Your approach will be non sargable. Even though you have an index it will not be used.

Right way to do this would be using IS NOT NULL condition 

SELECT * FROM tblInfo 
WHERE fldInfo <> '' 
  AND fldInfo IS NOT NULL

If you don't have a index, then create a index on fldinfo to improve the performance

Update :

Not In fails to compare the NULL values. Comparison with NULL values are unknown so it is fails to return the NULL values. Here is the correct way to do this

SELECT *
FROM   tblInfo
WHERE  (fldinfo NOT IN ( 30, 20 ) OR fldinfo IS NULL)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Hi PrDp, thanks for replying, in some cases yes we can put the IS NOT NULL or <> '' but most of our query need some value condtion on it like ex. ISNULL(flduser,'') NOT IN ('123','123','123') so we need to convert it to ISNULL but then again it affects the performance of our query. – BebeArg Jul 07 '17 at 04:36
  • @BebeArg -It is not `OR` it should be `AND`. Add sample data and expected result to make your requirement clear.. Am sure we can avoid `ISNULL` function and improve the performance – Pரதீப் Jul 07 '17 at 04:39
  • For Example some of the Value of the fldinfo is NULL, some of it got the value inside. so in this case we need to convert to ISNULL under WHERE condition so that it will show the correct count, because we tried to put the condition plus the value but the result is in Correct. – BebeArg Jul 07 '17 at 04:44
  • thanks for editing hehehehe. sorry im just new here in stackoverflow :) – BebeArg Jul 07 '17 at 04:53
  • i tried this one but still it shows different value, and based on the condition it will filter out the NULL which is needs to be included in the condition. – BebeArg Jul 07 '17 at 05:01
  • @BebeArg - Do you have any other conditions in `Where` clause ? If yes, then make sure you have used parenthesis properly. – Pரதீப் Jul 07 '17 at 05:03
  • Yeap i edited already putting () but same thing. By the way the current condition that we have we inner join it to other table. so some of the value is null some of them got the value. so Using the inner join it gets all the Value with NULL and without null value. – BebeArg Jul 07 '17 at 05:10
  • @BebeArg - You need to post your original query here with relevant sample data and expected result. – Pரதீப் Jul 07 '17 at 05:11
1

COALESCE is one option that you can try. It behaves in the same way. However the differences between performance has to be evaluated by you with some test

Some differences between ISNULL and COALESCE are outlined here: SQL - Difference between COALESCE and ISNULL?

EDIT: Based on the tests done by multiple people and by theory, ISNULL seems to be a better option over COALESCE

Which is quicker COALESCE OR ISNULL?

Sujith
  • 1,604
  • 9
  • 16
  • HI Sujit thanks for your reply, based on performance COALESCE is more slower than ISNULL, but still if we are using ISNULL value, if there are lots of transaction processing it causes deadlock. – BebeArg Jul 07 '17 at 04:05
0

No need for IIF. Simply check for NULL

WHERE fldinfo IS NOT NULL

Or, of course, use IS NULL if you want rows where this condition is met

S3S
  • 24,809
  • 5
  • 26
  • 45
  • thanks for replying scsimon, we already Implemented the IS NOT NULL before but some of the fields are not showing, thats why we used to ISNULL to convert the value in where condition. ex. ISNULL(flduser,'') NOT IN ('123','123','123') – BebeArg Jul 07 '17 at 04:06
  • Don't do that @BebeArg it's pointless. If you simply stated flduser not in ('123','234') and flduser is not null it would be the same thing – S3S Jul 07 '17 at 07:34