Problem in join update

Question

Problem in join update

Viewing 7 posts - 16 through 21 (of 21 total)

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

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 1

I meant only that that is my experience. I hope the OP will try it for himself rather than take my word for it.

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 2

The Dixie Flatline (3/1/2010)
Keith,
To handle concatenation of nulls, you need use either the ISNULL() or COALESCE() function. .

I recommend that you use COALESCE. ISNULL is also due to be deprecated and can truncate data in certain instances. -- To quote BOL

for ISNULL

Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

vs COALESCE

Return Types

Returns the data type of expression with the highest data type precedence.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Julie Breutzmann (3/2/2010)
The Dixie Flatline (3/1/2010)
Keith,
To handle concatenation of nulls, you need use either the ISNULL() or COALESCE() function. .
I recommend that you use COALESCE. ISNULL is also due to be deprecated and can truncate data in certain instances. -- To quote BOL
for ISNULL
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
vs COALESCE
Return Types
Returns the data type of expression with the highest data type precedence.

Can you cite where MS has flagged ISNULL for depreciation?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Lynn Pettis (3/2/2010)
Julie Breutzmann (3/2/2010)
The Dixie Flatline (3/1/2010)
Keith,
To handle concatenation of nulls, you need use either the ISNULL() or COALESCE() function. .
I recommend that you use COALESCE. ISNULL is also due to be deprecated and can truncate data in certain instances. -- To quote BOL
for ISNULL
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
vs COALESCE
Return Types
Returns the data type of expression with the highest data type precedence.
Can you cite where MS has flagged ISNULL for depreciation?

heh, If it is scheduled for deprecation - I can't find it.

Maybe I'm being thick, but I am trying to see the connection between truncated data and the quote being used. Please explain.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 5

Sorry, I misstated this. :blush: Because it's not ANSI-92 standard, it COULD be deprecated. For a comparison between ISNULL and COALESCE, see http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

Julie

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 6

heh, If it is scheduled for deprecation - I can't find it.

Maybe I'm being thick, but I am trying to see the connection between truncated data and the quote being used. Please explain.

======================================================================

First, what are u guys doing to get the persons post that your replying to at the top of your post?

Secondly, in the instance given there would be no truncation issues however it can occure if your not careful as i learned on a post some time ago.

declare @var1 varchar(5)

declare @var2 varchar(10)

set @var1 = null

set @var2 = '123456789'

select isnull( @var1, @var2)

--returns 12345 incorrectly.

Keep in mind the coalesce isn't as efficient as the isnull so if it is accross many many recs u may want to stick to isnull.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

BaldingLoopMan (3/4/2010)
heh, If it is scheduled for deprecation - I can't find it.
Maybe I'm being thick, but I am trying to see the connection between truncated data and the quote being used. Please explain.
======================================================================
First, what are u guys doing to get the persons post that your replying to at the top of your post?
Secondly, in the instance given there would be no truncation issues however it can occure if your not careful as i learned on a post some time ago.
declare @var1 varchar(5)
declare @var2 varchar(10)
set @var1 = null
set @var2 = '123456789'
select isnull( @var1, @var2)
--returns 12345 incorrectly.
Keep in mind the coalesce isn't as efficient as the isnull so if it is accross many many recs u may want to stick to isnull.

Good point on the truncation - this can cause truncation, if performed as you outlined. Wouldn't that be a victim of poor design though?

I wouldn't hesitate to use coalesce of isnull. I have seen the articles claiming isnull to be faster - all have tested with 2 values. When you must compound the isnull to support several values then that efficiency gain is wiped out.

select isnull( @var1, isnull(@var2,isnull(@var3,isnull(@var4,isnull(@var5,isnull(@var6,isnull(@var7,@var8)))))))

vs.

Select coalesce(@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8)

For me, the coalesce is easier to read and is a lot faster to type.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events