December 18, 2013 at 5:45 pm
Okay Scott, you're right and I am wrong. Now, I will go off and mumble to myself with the dust bunnies. No beer popsicles where I am at the moment.
Actually, you could still use IS NULL by using a CASE clause if you needed to replace a possible NULL value with a default value, but ISNULL would simplify the code.
Edit: And don't forget that you should still understand what the function does by the reading the manual.
December 18, 2013 at 5:58 pm
ScottPletcher (12/18/2013)
Huh? Where can't "IS NULL" be used in place of ISNULL()? At any rate, the ANSI-standard COALESCE() could always be used in place of ISNULL().
There's the importance of reading the manual. This is an example of why people should know what each function is doing even if they seem equivalent. It's not the best option, it's bad database design, but it exists.
CREATE TABLE #Test(
myString varchar(10) NULL,
myInt int NULL)
INSERT INTO #Test
VALUES( 'Test', NULL),
(NULL, 15)
GO
SELECT ISNULL( myString, myInt)
FROM #Test
GO
SELECT COALESCE( myString, myInt)
FROM #Test
GO
DROP TABLE #Test
Why do I prefer ISNULL(@category,'') = 0 to @category IS NULL OR @category = ''?
It's easier to type and I'm lazy.:-D
December 18, 2013 at 6:13 pm
ScottPletcher (12/18/2013)
Yikes! Table values should be trimmed by a trigger once during insert/update if necessary rather than in every query afterward. If you really need to, you can store a count of the leading/trailing spaces originally present in separate columns.
That's seriously overcomplicating their desire to preserve the original send for some arcane purpose they've usually mumbled at me about when I ask them why it's so necessary. Vendor software in particular can be notorious about this.
Also, trigger and once? Curious. But yes that concept would be the preference.
Besides, if you're doing <> '', you don't need ISNULL() in the first place:
LTRIM(RTRIM(a.field)) <> ''
is functionally the same, since NULL won't ever be "not equal" to anything.
True enough, I just write it the same way for = and <> for my own sanity and clarity to inheritors. It does little harm since it's already function wrapped.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2013 at 8:17 pm
ScottPletcher (12/18/2013)
Lynn Pettis (12/18/2013)
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Only unclear until one takes the time to read BOL to see what it is doing. Same with any function in any programming language.
Basic rule, RTM (Read The Manual).
Obscurity for the sake of "proving" one "knows more" -- no thanks.
Anyone who doesn't know what ISNULL is shouldn't be programming in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 8:24 pm
ScottPletcher (12/18/2013)
Evil Kraig F (12/18/2013)
Scott,I'm not sure how that's more or less obscure, but it's certainly easier for me to read. I work with a lot of 'iffy' data in loaders, and a structure like this is pretty common for me:
WHERE
ISNULL( LTRIM(RTRIM(a.field)), '') <> ''
ISNULL and NULLIF are pretty obvious about their meanings to me, too, though the enforced data typing isn't that transparent. Now, COALESCE is something that I'd never have found as the answer to the problem it solves without someone once having pointed it out to me.
I'm all for SARG-ability, but sometimes it's just not there. No matter what you do, you have to adjust the source for the comparison(s) in certain cases.
Yikes! Table values should be trimmed by a trigger once during insert/update if necessary rather than in every query afterward. If you really need to, you can store a count of the leading/trailing spaces originally present in separate columns.
Besides, if you're doing <> '', you don't need ISNULL() in the first place:
LTRIM(RTRIM(a.field)) <> ''
is functionally the same, since NULL won't ever be "not equal" to anything.
Heh... Make up your mind, Scott. Why would someone who didn't know what ISNULL was ("Obscurity for the sake of "proving" one "knows more" -- no thanks") as you mentioned in one of your posts on this thread even have a clue that "NULL won't ever be "not equal" to anything".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 8:39 pm
ScottPletcher (12/18/2013)
At any rate, the ANSI-standard COALESCE() could always be used in place of ISNULL().
Why? Do you believe in the myth of "portability"?
COALESCE also has a hidden problem that ISNULL does not have. COALESCE returns a result using whatever the highest datatype precedence of all it's operands are. If you don't know that, you can end up with non-SARGable code for something like the following where the entire SomeVarcharColumn will first be implicitly converted to NVARCHAR making an INDEX SCAN the absolute best that you can do.
SELECT yada-yada
FROM dbo.Sometable
WHERE SomeVarcharColumn = COALESCE(@SomeVarcharVariable,@SomeNVarcharVariable)
;
ISNULL will always return the datatype of the first operand.
Of course, ISNULL has a problem of its own in that it won't short-circuit if the second operand is a SELECT whereas COALESCE will. So, as with all else, "It Depends". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 8:47 pm
Evil Kraig F (12/18/2013)
ScottPletcher (12/18/2013)
Yikes! Table values should be trimmed by a trigger once during insert/update if necessary rather than in every query afterward. If you really need to, you can store a count of the leading/trailing spaces originally present in separate columns.That's seriously overcomplicating their desire to preserve the original send for some arcane purpose they've usually mumbled at me about when I ask them why it's so necessary. Vendor software in particular can be notorious about this.
Also, trigger and once? Curious. But yes that concept would be the preference.
Besides, if you're doing <> '', you don't need ISNULL() in the first place:
LTRIM(RTRIM(a.field)) <> ''
is functionally the same, since NULL won't ever be "not equal" to anything.
True enough, I just write it the same way for = and <> for my own sanity and clarity to inheritors. It does little harm since it's already function wrapped.
On the other hand, I'm actually hoping that the people that inherit my code actually know how to program in T-SQL. Using no more arcane knowledge than knowing that NULL can't be "not equal" to anything, the following code will do the same thing even on fixed length datatypes such as CHAR and still be SARGable.
WHERE a.field > '' --Not Null and Not Blank
It's also faster and burns a lot less clock cycles than LTRIM/RTRIM or even ISNULL() or COALESCE.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 9:10 pm
ScottPletcher (12/18/2013)
I'm truly curious now:Can anyone tell me:
Where "IS NULL" can't be used in place of ISNULL()?
Without the help of a CASE statement, lot's of places. Here's a simple example.
SELECT [Status] = ISNULL([Status],'Unknown')
FROM dbo.SomeTable
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 11:07 pm
Jeff Moden (12/18/2013)
Using no more arcane knowledge than knowing that NULL can't be "not equal" to anything, the following code will do the same thing even on fixed length datatypes such as CHAR and still be SARGable.
WHERE a.field > '' --Not Null and Not Blank
It's also faster and burns a lot less clock cycles than LTRIM/RTRIM or even ISNULL() or COALESCE.
That is way cool Jeff, even if it is a bit arcane!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 19, 2013 at 7:58 am
I don't object to ISNULL() in the SELECT list, just in WHERE conditions. Since it shouldn't be used in WHERE, it's only consistent not to use it in IF or CHECK either.
Bizarre that you so vociferously support something that confuses enough people to generate many qs like this. Whatever, go for it, deliberately make it more difficult to follow your code if you want.
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".
December 19, 2013 at 8:57 am
ScottPletcher (12/19/2013)
I don't object to ISNULL() in the SELECT list, just in WHERE conditions. Since it shouldn't be used in WHERE, it's only consistent not to use it in IF or CHECK either.Bizarre that you so vociferously support something that confuses enough people to generate many qs like this. Whatever, go for it, deliberately make it more difficult to follow your code if you want.
ISNULL is not that confusing if you take the time to read and understand what is in the BOL documentation. ISNULL is a very simple function to use and understand. If it is confusing when used in a WHERE clause how can it be less confusing in a SELECT list? It does the same thing in either location.
If you look really close at the question, it was about what was in the () of the ISNULL(@category,''), the variable @category and the replacement value '' (and empty string). not how it was being used in the IF statement. This is basic knowledge that would be easily learned by simply reading the documentation (in this case Books Online), and whould be needed to use ISNULL properly in a SELECT list, WHERE clause, IF statement, etc.
December 19, 2013 at 10:24 am
WOW!!!!!! Reading through these posts makes me wonder whether I DARE post any more "please help" questions considering my lack of expertise. I assure you my intent is to LEARN T-sql to the best of my ability but a girl has to start somewhere!
To those who replied to my post with the intent of helping, I thank you. I learned something that will help me on my path to becoming a better T-sql programmer.
To those more pretentious fools who replied with the intent to educate the SQL world as to how brilliant they are and to condescend to those less so, GET OVER YOURSELVES. You should be ashamed of some of your comments.
That is all.
December 19, 2013 at 11:17 am
I apologize on behalf of everyone, this discussion went too far away from the original question. This forum is a place to learn and it's great that you have learned something.
It's common to have different opinions based on our experiences but we're always trying to improve. The discussion has important points that might help you to go further than just understanding ISNULL() and find out when it's appropiate to use and when it's not, as well as other options such as COALESCE() and CASE statements.
I hope that this won't stop you to come, ask questions and continue learning. 😉
December 19, 2013 at 12:08 pm
IT2012 (12/19/2013)
WOW!!!!!! Reading through these posts makes me wonder whether I DARE post any more "please help" questions considering my lack of expertise. I assure you my intent is to LEARN T-sql to the best of my ability but a girl has to start somewhere!
Oh, don't worry about most of this conversation. Think of it as watching some old guys bicker about the proper usage of a vacuum tube in a room full of iPads. Yes, I'm included in that description.
Threads around these parts are known, on occassion, to go wandering. Occasionally they'll even take a walkabout. This one... well, didn't quite wander but that horse is particularly dead now. 😀 But I wouldn't worry about it. It certainly doesn't reflect on you that we got into an esoteric debate on your thread.
To those who replied to my post with the intent of helping, I thank you. I learned something that will help me on my path to becoming a better T-sql programmer.
Which means this thread was worth it. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 12:48 pm
Thanks for the reassurance! I was beginning to get a little afraid there might be cyber-bloodshed! 🙂
Honestly, I am learning what I can. My situation is I am supporting an EMR (Electronic Medical Records) system that was written by a thousand different programmers and consultants who were concerned with results, not effective, considerate programming techniques. As is the case with all "canned" software, seems we always need functionality that doesn't quite come with the package. SO.... I'm teaching myself with the aid of about 6 trusty T-SQL books, but the books only go so deep.
What I need is a seasoned SQL programmer who would like to spend a week in sunny Southwest Florida teaching T-SQL to a beginner. Any takers??? 😀
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply