is it possible to replace ISNULL with some other data types?

Question

Hello,

is it possible to replace ISNULL with some other data types?

please give a some suggestion for the same...

thank u....

Answer 1

The point is you should not put other data type to the current column being changed.

---

Many Thanks & Best Regards, Hua Min

Answer 2

Hello,

ISNULL (Transact-SQL) is a function, not a data type.

What exactly are you trying to archieve?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Yep.. so far as other type can be implicitly converted to first datatype. The return type of ISNULL will be always the datatype of the first argument

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 4

isnull(AssignDate,'') <>''and (isnull(CC_Status,'')='Show' or isnull(CC_Status,'')='No Show'

when i used ISNULL function is not consider index and getting time to execute..

Any other function can i used instead of ISNULL...

Thank U...

Answer 5

ISNULL function is not consider index

The usage of function on a column in the WHERE clause always prevents the usage of an index; rewrite your query, e.g. by using the IS NULL compare

E.g. isnull(AssignDate,'') <>'' and can be replaced by

AND NOT (AssignDate = '' OR AssignDate IS NULL) AND

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

isnull(AssignDate,'') <>''and (isnull(CC_Status,'')='Show' or isnull(CC_Status,'')='No Show'

when i used ISNULL function is not consider index and getting time to execute..

Any other function can i used instead of ISNULL...

Thank U...

why do you want to use ISNULL function in your case

AssignDate<>'' and  (CC_Status='Show' or CC_Status='No Show')   will do your need, this will eliminate NULL also when you compare column with any value, NULL will be eliminated both case(= & <>)

if you want to get value you need to specify by

col1 is null

isnull(col1,'')='' -- in this case it will match both '' and NULL

select * from 
(select '' as AssignDate ,'Send' as CC_Status
union all
select null as AssignDate,'No Show' as CC_Status
union all
select 'aug-01' as AssignDate,'' CC_Status
)a where AssignDate<>''
--o/p aug-01

select * from 
(select 'aug-02' as AssignDate ,'Send' as CC_Status
union all
select null as AssignDate,'No Show' as CC_Status
union all
select 'aug-01' as AssignDate,null CC_Status
)a where AssignDate<>'' and( CC_Status = 'send' or CC_Status= 'No Show')
--o/p  aug-01, Send

Thanks

Saravana Kumar C

Answer 7

>isnull(AssignDate,'') <>''and

That is a known issue. It is called non-SARGable predicate (index will not be used by the optimizer).

Discussion of SARGable predicates: http://www.sqlusa.com/bestpractices/sargable/

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012