A Difference Between ISNULL And COALESCE You Might Care About In SQL Server
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Related Posts
- Query Plan Patterns For Windowing Functions: Row Mode vs Batch Mode
- Troubleshooting Security Cache Growth In SQL Server (USERSTORE_TOKENPERM And TokenAndPermUserStore)
- Catch Me On The Azure DevOps Podcast: Technical Debt
- Join Me And @Kendra_Little At @PASSDataSummit For 2 Days Of SQL Server Performance Tuning Precons!
Hi Eric,
Huge fanboy of your content! in the dark reaches of the past, in a galaxy far far away, I heard the blasphemous phrase – “IsNull” will be deprecated by Microsoft sql server. Not only that, my team believed it and stopped using it by order of our data lord
your thoughts on this event in history
I rebelled as much as I could with the analysis that there was too much usage to deprecate it… my logic fell on deaf ears
Yeah that was never a thing. Not even in the least. It sounds like someone lying to enforce their preferences.
Also, turn on Actual Execution Plan and run this.
select coalesce((select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = ‘CommandLog’),’No Table Found’)
select isnull((select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = ‘CommandLog’),’No Table Found’)
🫨🫨🫨
COALESCE doesn’t hold onto the value when checking for NULL, so when it finds a non-null value that is a subquery, it has to run it again to return the value. ISNULL doesn’t have that problem.
Yes, I’d imagine it’s a case of CASE not short circuiting as ~expected~