URGENT T- SQL QUESTION. NEED HELP.

Question

URGENT T- SQL QUESTION. NEED HELP.

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

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Mr. Kapsicum (1/30/2013)
Thanks uravindarreddy,,, Ur query is Great.
But Can U explain its working to me.,? I m a bit naive to SQL , cant understand its in depth working.!!
Thanks again for ur help.

The COALESCE function uses the first non null field in the list, which makes it effectively an easy to read Nested ISNULL

eg

ISNULL(Col,ISNULL(col2,ISNULL(col3,'')))

Can be replaced with

COALESCE(Col,col2,col3,'')

Which means that if Col is NULL then it checks Col2, which if that is NULL then it will check col3 will be used, and finally if all 3 are NULL then it will use ''.

The final point COALSECE is more readable than the nested ISNULL especially if you have 4/5 columns that you want to check in a specific order.

Though I'm sure I read somewhere there can be some interesting sideffects of using COALESCE, though I cant remember wher I read it.

One thing to remember is that it will probably cause the optimiser to chose an inefficient query plan.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jason-299789 (1/30/2013)
Sorry I spotted that, and just changed them to work. Theres still a problem with the ISNULL though.
this is the isnull
SELECT ID,COLOR
FROM #TBLCOLOR
WHERE ISNULL(COLOR,'')=ISNULL(@COLOR,ISNULL(COLOR,''))

Just bear in mind that can't use indexes, so that's a table scan every time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

GilaMonster (1/30/2013)
Jason-299789 (1/30/2013)
Sorry I spotted that, and just changed them to work. Theres still a problem with the ISNULL though.
this is the isnull
SELECT ID,COLOR
FROM #TBLCOLOR
WHERE ISNULL(COLOR,'')=ISNULL(@COLOR,ISNULL(COLOR,''))
Just bear in mind that can't use indexes, so that's a table scan every time.

Very true, but the OP wanted to see how to do this using an ISNULL. My prefered option in this case would be WHERE Colour=@Colour or @Colour is NULL.

On a more complex where you have several of those type clauses then a 'Catch all' query is most likely the better option.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 4

Jason-299789 (1/30/2013)
Mr. Kapsicum (1/30/2013)
Thanks uravindarreddy,,, Ur query is Great.
But Can U explain its working to me.,? I m a bit naive to SQL , cant understand its in depth working.!!
Thanks again for ur help.
The COALESCE function uses the first non null field in the list, which makes it effectively an easy to read Nested ISNULL
eg
ISNULL(Col,ISNULL(col2,ISNULL(col3,'')))
Can be replaced with
COALESCE(Col,col2,col3,'')
Which means that if Col is NULL then it checks Col2, which if that is NULL then it will check col3 will be used, and finally if all 3 are NULL then it will use ''.
The final point COALSECE is more readable than the nested ISNULL especially if you have 4/5 columns that you want to check in a specific order.
Though I'm sure I read somewhere there can be some interesting sideffects of using COALESCE, though I cant remember wher I read it.
One thing to remember is that it will probably cause the optimiser to chose an inefficient query plan.

Mr.Jason;

SELECT * FROM TBLCOLOR

WHERE COALESCE (COLOR, '') = COALESCE (@COLOR, COLOR, '');

in this query coalesce(@color,color,'')

the field color is always not null then how can we compare ''.?

and which is more efficient ISNULL or Coalesce.? 😉 Plz Help

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Mr. Kapsicum (1/30/2013)
...
i want my query to use BOTH ISNULL and COALESCE...

Only homework would impose such a restriction.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Mr. Kapsicum (1/30/2013)
Mr.Jason;
SELECT * FROM TBLCOLOR
WHERE COALESCE (COLOR, '') = COALESCE (@COLOR, COLOR, '');
in this query coalesce(@color,color,'')
the field color is always not null then how can we compare ''.?
and which is more efficient ISNULL or Coalesce.? 😉 Plz Help

Neither. They're both going to suck because you have wrapped a function aroud a column name in the WHERE clause. Go back and look at Gila Monster's post for confirmation of that. Then go look at the article for "SQL in the Wild" (by Gila Monster again) that was posted early on in the game for the right way to do this.

You asked for "urgent" help. I urgently suggest you find that link and read it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

ChrisM@Work (1/30/2013)
Mr. Kapsicum (1/30/2013)
...
i want my query to use BOTH ISNULL and COALESCE...
Only homework would impose such a restriction.

Yep; I'm sure of that too. 100% homework (or bad interview type of q).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".