Click here to Skip to main content
13,000,469 members (58,918 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 13 May 2012

SQL - Now you see me, now you don't

, 14 May 2012
Rate this:
Please Sign up or sign in to vote.


SQL Server, undoubtedly my favourite Microsoft product, but a seasoned baseball pitcher will be impressed by the curve balls it can produce.

Using the code 

Execute the following script:

declare @var1 VARCHAR(1)
SET @var1 = '2'
SELECT ISNULL(@var1, -1)
SET @var1 = NULL
SELECT ISNULL(@var1, -1)

Sure, it is easy to see the culprit, but now read this thinking that @var1 is a column defined in a table, so it is not so obvious what the length of the column/variable is.

  • ISNULL – replaces the NULL value with the given replacement.
  • COALESCE – returns the first not NULL expression in the argument list.

When reading the MSDN topic on T-SQL ISNULL it does say that it returns the SAME type as the checked expression, the value is IMPLICITLY converted to the checked expression’s type when the two types differ.

The interesting part is that the conversion does not procedure a String or binary data will be truncated error?

This does not prove that COALESCE is better than ISNULL, all it is saying: know the animal you are working with.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Technical Lead First Tech Digital Solutions
South Africa South Africa
I escaped from the mental hospital on 25th June and was captured by a zookeeper. Escaped from the zoo on 15th July and killed the zoo guard in the attempt. So now I just eat bananas and hang out on the Code Project.

You may also be interested in...


Comments and Discussions

GeneralThoughts Pin
PIEBALDconsult14-May-12 9:30
memberPIEBALDconsult14-May-12 9:30 
What are you on about? I think you should explain more clearly exactly what it is you're trying to communicate.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170624.1 | Last Updated 14 May 2012
Article Copyright 2012 by Programm3r
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid