T-SQL

Question

T-SQL

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

tjskidmore SSC Veteran Points: 248 More actions · Answer 1

This is of course a limitation with the ISNULL() function. The datatype and in this case str1 is varchar(5) meaning that the replacing statement will be confined to 5 characters. Whereas COALESCE() is not dependent in this way but will return the first not null value of whatever datatype it may be. COALESCE() is the better choice.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

tjskidmore (12/19/2014)
This is of course a limitation with the ISNULL() function. The datatype and in this case str1 is varchar(5) meaning that the replacing statement will be confined to 5 characters. Whereas COALESCE() is not dependent in this way but will return the first not null value of whatever datatype it may be. COALESCE() is the better choice.

Not quite so. COALESCE uses the data type with the highest data type precedence which can generate unexpected errors if you're not aware of this.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 3

Good question, though not the first time I have seen it.

The explanation can be improved. What happens is that ISNULL is defined as returning the same data type as the first argument. Whereas the (otherwise mostly similar, but more portable and more flexible) alternative keyword COALESCE uses the standard rules of data type preference between all arguments passed in.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

SALIM ALI (12/19/2014)
the answer is only correct when using SqlServer 2012 and above. Previous versions prompt an error.

That`s not correct, as SQL 2k8 will work fine with the in-line assignement.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11358 More actions · Answer 6

A nice question. But a shame that 'SQL String' wasn't an option, and that there was no reference as to what happens when you assign a value longer than the definition (ie it truncates it rather than raising an error).

mohammad.bhaji SSC Enthusiast Points: 119 More actions · Answer 7

mohammad.bhaji

SSC Enthusiast

Points: 119

December 22, 2014 at 6:30 am

#1765961

Very Tricky.. Learning Lot.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

December 22, 2014 at 9:32 am

#1766017

Thanks for the question.

Nevyn SSChampion Points: 14210 More actions · Answer 9

Toreador (12/22/2014)
A nice question. But a shame that 'SQL String' wasn't an option, and that there was no reference as to what happens when you assign a value longer than the definition (ie it truncates it rather than raising an error).

+1

Literally exactly what I was going to say.