SYNTAX Help please...
-
IT2012 (12/19/2013)
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.I would recommend adding a book to your collection. Look for Fundamentals of T-SQL (your version) by Itzik Ben-Gan, particularly if you're worried that the books don't go deep enough.
Another excellent one for deeper learning is SQL Server Internals. Many authors, but you'll find it. Again, get it for the version you're working with.
- Craig FarrellNever 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 -
IT2012 (12/19/2013)
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??? 😀I'm no longer living in Florida :crying: but you can find people that are willing to help others on PASS local chapters. Take a look here: http://www.sqlpass.org/PASSChapters/LocalChapters.aspx
-
IT2012 (12/19/2013)
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??? 😀
Ooh yes please! Haven't had a proper holiday in yonks!
I wish. On a more serious note, when these heavyweight dudes go off on one like this, the learning opportunity is always far greater than when they don't. You can learn a fortune for nothing. Welcome aboard and enjoy the ride!
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Got it!! It's dog-eared and worn but within reach at all times!!! As well as Joe Celko's SQL for Smarties, Murach's SQL Server 2008, Microsoft's Inside T-SQL Querying and a few others I can't see the title at the moment!! We have SQL Server 2008 and 2012 here, so my nose is in one of these books OFTEN!!
Thanks for the link to the PASS sites.... amazing what info you can get for free is right!!!!!
So I guess you're not all scary! 😀
Carol
-
IT2012 (12/19/2013)
Got it!! It's dog-eared and worn but within reach at all times!!! As well as Joe Celko's SQL for Smarties, Murach's SQL Server 2008, Microsoft's Inside T-SQL Querying and a few others I can't see the title at the moment!! We have SQL Server 2008 and 2012 here, so my nose is in one of these books OFTEN!!Thanks for the link to the PASS sites.... amazing what info you can get for free is right!!!!!
So I guess you're not all scary! 😀
Carol
I will also apologize for my misbehavior. It was nothing that you said or did, but another member's series of comments that caused a lack of judgement on my part. I do hope that my firt post did help you with understanding how ISNULL works.
-
Reading this post wants me to go back to BOL and read up on ISNULL.... whow.. Note to self.... be very careful posting a question with ISNULL as a part of a t-sql query.
I will say this, it is a rather entertaining post...
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
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.But you CAN use it in WHERE clauses and still get SARGability, not because ISNULL is SARGable by any means, but because other things in the WHERE clause may be sufficiently SARGable to make it not matter if ISNULL is present or not. "It Depends".
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.
BWAAA-HAAAA!!!!! Listen to the pot calling the kettle black! Not only is your code non-SARGable but you've even had to explain how confusing your code is because it does require somewhat arcane knowledge of NULLs!
Besides, if you're doing <> '', you don't need ISNULL() in the first place:
LTRIM(RTRIM(a.field)) <> ''
is functionally the same, [font="Arial Black"]since NULL won't ever be "not equal" to anything[/font].
Even many experienced programmers have difficulty getting their arms around the fact that NULL is not equal to anything else, never mind the idea that it is also not "not equal" to anything else.
Here's my code. Tell me what is difficult or confusing about it.
WHERE a.field > '' --Not Null and Not BlankIt's not only SARGable, but even total neophytes to the art of writing good T-SQL know exactly what that code does because the comment tells them exactly what it does.
--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) -
Jeff Moden (12/20/2013)
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.But you CAN use it in WHERE clauses and still get SARGability, not because ISNULL is SARGable by any means, but because other things in the WHERE clause may be sufficiently SARGable to make it not matter if ISNULL is present or not. "It Depends".
So you're claiming that functions should be used on some columns in the WHERE because there might not be other columns that don't have functions? Ridiculous! All functions against columns should always be avoided in WHERE conditions whenever reasonably possible.
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".
-
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!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.
I don't know who these comments are directed to and, so, don't know whether to take exception to them or not. To be honest, I don't believe that anyone was as far out of line as you suggest. Such heated debate is caused by one and only one thing and it's truly honorable in nature... we're all trying to help a newbie understand the pitfalls of T-SQL and, if you look beyond the "heat", there is much to learn from such posts... much more than you'll find even in Instructor-Led classes, books on certification, or Books Online, itself (or at least not in all one spot).
To be sure, the information that I posted was meant to correct what I believe is some terribly egregious misinformation and bad recommendations that I didn't want a newbie or others to adopt as being any form of best practice or even a good recommendation. To be equally sure, none of my comments were directed at you or anything you posted.
Regardless, I hope you learned some more about T-SQL, ISNULL, COALESCE, SARGability, and a couple of other things from all the posts on this thread.
Shifting gears, Lynn hit the nail on the head with his first reply to your original post. Just to add a bit more information to help a newbie in the future, it does't matter how many spaces there are between the quotes in T-SQL. '' = ' ' = ' {virtually any number of spaces} ' = SPACE(n) {wheren 'n' is a positive INTEGER expression). I'm bringing this up because, in many other SQL "languages", two single quotes with no spaces between them means NULL (like it does in Oracle) and to point out that there is no need to waste CPU time or make non-SARGable (cannot do a high performance Index Seek) code by adding the likes of LTRIM/RTRIM to any number of spaces when comparing spaces to spaces. Except for the difference between LEN() and DATALENGTH(), one space equals a thousand spaces. On a slightly different tack, trailing spaces almost never matter in comparisons whereas leading spaces do.
I also don't know if you know what "Books Online" is so please don't think me "condescending" for the following. I'm just trying to make sure that a self-admitted newbie is aware of the tool. The link that Lynn posted in his first post took you to a WebSite that described ISNULL. You don't necessarily need to use Google to get there. If, while in SSMS, you press the {f1} key, it will take you to either the locally installed (if it was installed) version of Books Online or to the "online" version of Books Online.
In that same vein, if you open Books Online, you will see a tab at the bottom of the window labelled "Contents". If you open that tab, one of the items that you can delve into is "SQL Server 20xx Tutorials" (the "xx" being whatever version of SQL Server you're using). The subjects won't help much for T-SQL (use the INDEX tab for that... I'd suggest looking up "Functions" to see all the marvelous things that T-SQL can do using functions), but they'll give you the proverbial "leg up" to begin learning of the other facets of SQL Server.
Last but not least (again, just making sure a newbie is aware), the need for things like ISNULL() and COALESCE() and IS NULL is because of the default settings for SQL Server's SSMS. There's a setting called "ANSI NULLS" that you can find in Books Online (I strongly urge you to read it because it's incredibly important) and the default is "ON" for SSMS. To summarize, when ANSI NULLS is ON, you cannot compare a NULL value to anything else. Instead, you must use things like ISNULL(), COALESCE() and IS NULL. Being able to set ANSI NULLS to OFF has been deprecated (will be removed from a future version) and it's causing quite the rowe in the world of managed code developers because, for them (external calls to the DB server), ANSI NULLS has always been defaulted to OFF. The change will break huge amounts of their code.
--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 (12/20/2013)
Jeff Moden (12/20/2013)
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.But you CAN use it in WHERE clauses and still get SARGability, not because ISNULL is SARGable by any means, but because other things in the WHERE clause may be sufficiently SARGable to make it not matter if ISNULL is present or not. "It Depends".
So you're claiming that functions should be used on some columns in the WHERE because there might not be other columns that don't have functions? Ridiculous! All functions against columns should always be avoided in WHERE conditions whenever reasonably possible.
Poor wording on my part but certainly not ridiculous. I'm saying that if you have your INDEX SEEK because of other criteria, there is no harm in having otherwise non-SARGable criteria in your WHERE clause. Certainly, I would not force someone to change their production code because of it unless the code came back for other rework and needed to be redeployed. For example, the following produces the desired INDEX SEEK even with the non-SARGable criteria and the forbidden "*".
SELECT *
FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND ISNULL(EndDate,'9999') > GETDATE() --Non-SARGable by itself but will not prevent INDEX SEEK here because of the other criteria
;
My point in hilighting this fact is because a lot of people (including me, at one time) who spend time tuning code would jump on such code as being "non-SARGable" and change it without even looking. In the world of properly controlled QA and UAT testing, this would require the changed code to be retested whereever it's used and, to use your word, that would be a "ridiculous" waste of time because the change would change nothing.
To your point, though, I'd also prefer the following code simply because it avoids the perception that the code might be totally non-SARGable and would have someone rework the code if it came back for rework as follows for any reason.
SELECT *FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND (EndDate IS NULL OR EndDate > GETDATE())
;
Of course, what I'd really prefer is if someone actually designed the EndDate column correctly (NOT NULL and defaults to 9999-01-01) so that there are no need for such tests of NULLability, which would make the following totally obvious code work correctly.
SELECT *
FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND EndDate > GETDATE())
;
--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) -
Jeff Moden (12/20/2013)
ScottPletcher (12/20/2013)
Jeff Moden (12/20/2013)
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.But you CAN use it in WHERE clauses and still get SARGability, not because ISNULL is SARGable by any means, but because other things in the WHERE clause may be sufficiently SARGable to make it not matter if ISNULL is present or not. "It Depends".
So you're claiming that functions should be used on some columns in the WHERE because there might not be other columns that don't have functions? Ridiculous! All functions against columns should always be avoided in WHERE conditions whenever reasonably possible.
Poor wording on my part but certainly not ridiculous. I'm saying that if you have your INDEX SEEK because of other criteria, there is no harm in having otherwise non-SARGable criteria in your WHERE clause. Certainly, I would not force someone to change their production code because of it unless the code came back for other reqork and needed to be redeployed. For example, the following produces the desired INDEX SEEK even with the non-SARGable criteria and the forbidden "*".
SELECT *
FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND ISNULL(EndDate,'9999') > GETDATE() --Non-SARGable by itself but will not prevent INDEX SEEK here because of the other criteria
;
To your point, though, I'd also prefer the following code simply because it avoids the perception that the code might be totally non-SARGable and would have someone rework the code if it came back for rework as follows for any reason.
SELECT *FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND (EndDate IS NULL OR EndDate > GETDATE())
;
Of course, what I'd really prefer is if someone actually designed the EndDate column correctly (NOT NULL and defaults to 9999-01-01) so that there are no need for such tests of NULLability, which would make the following totally obvious code work correctly.
SELECT *
FROM AdventureWorks.Production.BillOfMaterials
WHERE ProductAssemblyID IS NULL
AND StartDate >= '2001-01-01'
AND EndDate > GETDATE())
;
There could be great harm in it if either (1) SQL could have done seeks on both terms and produced the final output more efficiently than for only one seek; and/or (2) the index on the SARGable condition is dropped for whatever reason, or that condition is removed from the query in the future.
I don't allow ISNULL() in a WHERE period -- there is no need for it, ever.
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".
-
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!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.
Ah... now I see it.
Jeff Moden (12/18/2013)
Anyone who doesn't know what ISNULL is shouldn't be programming in T-SQL.That was certainly not directed at you but I can also see how you think it might have been, especially when taken out of context like I just did above.
My point was in argument against ISNULL() providing any more obscurity than any of the other methods for testing something for the NULL condition. The unspoken part here is that anyone that works with T-SQL had damned well better know what ISNULL() is or be on their way to looking up what it is... exactly as you have done with your good original post.
With that in mind, I certainly didn't mean that about you (you did do the right thing... you asked a question about ISNULL) nor for you to become collateral damage in a heated debate. My most humble and sincere apologies.
--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 (12/20/2013)
There could be great harm in it if either (1) SQL could have done seeks on both terms and produced the final output more efficiently than for only one seek; and/or (2) the index on the SARGable condition is dropped for whatever reason, or that condition is removed from the query in the future.I don't allow ISNULL() in a WHERE period -- there is no need for it, ever.
Agreed. As I stated previously, I'd not allow the ISNULL() in the WHERE clause on new code or code that came back for rework. I would, however, consider not changing existing old (before my time) production code if that were the only problem because that would require expensive re-testing and there are a whole lot more things that require immediate attention. Instead, I'd evaluate the code for the very conditions and possibilities you mention and make a judgement call as to the risk of those possibilities. And that was my whole point of mentioning that non-SARGable criteria can exist in the WHERE clause without affecting the SARGability of the overall criteria.
As for removing "that condition" (the SARGable one) from the query in the future causing a problem, I also made it clear that the ISNULL would be removed if the code came back for rework for any reason because it would have to be retested anyway.
--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) -
Jeff, neat trick with the a.Field > ''. Was unaware of that.
ScottPletcher (12/20/2013)
There could be great harm in it if either (1) SQL could have done seeks on both terms and produced the final output more efficiently than for only one seek;Which it can't. The WHERE clause contains a non-equal search. As I'm sure you're well aware and are ignoring to prove a point, once you hit a range search in an index, the rest is ignored.
So, even with an index of (StartDate, EndDate), the StartDate range precludes any usage of additional columns. Since that's a uniquely described predicate after the initial seek for further filtering, you could put dancing monkeys in there for all it'd affect the seek efficiency.
(2) the index on the SARGable condition is dropped for whatever reason, or that condition is removed from the query in the future.
A condition removed from the query in the future would require it to be modified, which is a case that Jeff was pointing out earlier that he would change the item, particularly since it is no longer going to seek and that would be pretty obvious on a quick review of the execution plan.
If you're going to start modifying indexes, however, I would personally assume a code review would occur or you're willing to take your lumps.
But, I'd like to bring us back to the origination of this minor alteration in the direction of this topic, and that's SARGability and effeciency and if it should even be allowed to exist. The following construct was built in SQL 2k5 and tested there.
IF OBJECT_ID( 'tempdb..#tester') IS NOT NULLDROP TABLE #tester
CREATE TABLE #tester
(tidINTIDENTITY(1,1) NOT NULL,
SomeDateDATETIMENOT NULL,
SomeCharacterVARCHAR(10)NULL
)
CREATE CLUSTERED INDEX idx_test ON #tester (SomeDate, SomeCharacter)
CREATE NONCLUSTERED INDEX idx_test2 ON #tester (SomeCharacter, SomeDate)
INSERT INTO #tester (SomeDate, SomeCharacter)
SELECT
DATEADD( dd, ABS(CHECKSUM( NEWID())) % 1000, '20000101'),
CASE WHEN t.n % 3 = 0
THEN NULL
WHEN t.n % 3 = 1
THEN ''
ELSE '@@@@'
END
FROM
tally AS t
CROSS JOIN
Tally AS t2
WHERE
t.n <= 10000
AND t2.n <= 100
SELECT MIN(SomeDate), MAX(SomeDAte) FROM #tester
SET STATISTICS IO, TIME ON
SELECT
*
FROm
#tester
WHERE
SomeDate = '20001231'
AND ( SomeCharacter IS NULL OR SomeCharacter = '')
SELECT
*
FROm
#tester
WHERE
SomeDate = '20001231'
AND ISNULL( SomeCharacter, '') = ''
SET STATISTICS IO, TIME OFF
Now, of curiousity, a few multiple runs showed the ms to match nearly exactly and the ISNULL took a little extra CPU. like 16 ms of the 74 (average) ms it took to run this.
But, of significant curiousity is the execution plans.
First, the IS NULL OR ='' component, creates a much more complex plan. Not a bad thing in its own right but it needs review. I'll attach screen shots here shortly of the mouseover. It predicates on the scalar as expected, and then range seeks on the characters. (Expr 1012 and expr 1013).
The ISNULL, however, simply Seeks on the Scalar for the date and then Predicates the output but the ISNULL check. The resultant query is simpler, but again, that doesn't count for everything, as batch cost (always a suspect number but for this case) is less for the more complex plan. However, time speaks to everything for me, and neither of these queries differ in time to complete further than a ms.
But, you say, you're drilling into a deeper portion of the index on the cluster! If you inverted that, what would happen? Well, let's see. We'll flip our index creation order and who's the cluster.
IF OBJECT_ID( 'tempdb..#tester') IS NOT NULLDROP TABLE #tester
CREATE TABLE #tester
(tidINTIDENTITY(1,1) NOT NULL,
SomeDateDATETIMENOT NULL,
SomeCharacterVARCHAR(10)NULL
)
CREATE CLUSTERED INDEX idx_test2 ON #tester (SomeCharacter, SomeDate)
CREATE NONCLUSTERED INDEX idx_test ON #tester (SomeDate, SomeCharacter)
INSERT INTO #tester (SomeDate, SomeCharacter)
SELECT
DATEADD( dd, ABS(CHECKSUM( NEWID())) % 1000, '20000101'),
CASE WHEN t.n % 3 = 0
THEN NULL
WHEN t.n % 3 = 1
THEN ''
ELSE '@@@@'
END
FROM
tally AS t
CROSS JOIN
Tally AS t2
WHERE
t.n <= 10000
AND t2.n <= 100
SELECT MIN(SomeDate), MAX(SomeDAte) FROM #tester
SET STATISTICS IO, TIME ON
SELECT
*
FROm
#tester
WHERE
SomeDate = '20001231'
AND ( SomeCharacter IS NULL OR SomeCharacter = '')
SELECT
*
FROm
#tester
WHERE
SomeDate = '20001231'
AND ISNULL( SomeCharacter, '') = ''
SET STATISTICS IO, TIME OFF
Well, in that case, we now have significant differences, and the selection of multiple runs (to avoid cache concerns and compile times) I took below is common:
Table '#tester_____________________________________________________________________________________________________________000000000008'. Scan count 2, logical reads 2196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 111 ms.
(711 row(s) affected)
Table '#tester_____________________________________________________________________________________________________________000000000008'. Scan count 1, logical reads 2193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 131 ms.
In both cases here, the execution plans are slightly more complex, as they're forcing a keylookup after seeking the noncluster based on the SomeDate field because we want to return a field on the leaf level (tid). However, proving that we can't trust cost, the OR query is now the most expensive one, but it's definately the fastest in multiple tests.
What does this have to do with a hill of beans? Not much except to show that while SARGability is best practice, it's not necessarily a place you need to build a bunker on a hill about to get equivalent performance. If it's the leading edge(s) of the indexes it matters a lot more than in sub-columns. It won't matter at all if you skip a column past the leading edge (ie: SomeDate, tid, SomeCharacter).
And yes, this is in 2k5, the worst and least forgiving of the current optimization engines.
Edit coming to fix the img links once they settle into the post.EDIT: The next followup question is "Why did Craig do all that to prove that point out?" The answer is not in a simple ISNULL vs. OR discussion, but an overall viewpoint of going to rediculous lengths of code to SARGable a column that really doesn't always need it. All it does is overcomplicate a simple concept that a function wrapped around the initial column can make easy to read.
- Craig FarrellNever 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 -
Evil Kraig F (12/20/2013)
ScottPletcher (12/20/2013)
There could be great harm in it if either (1) SQL could have done seeks on both terms and produced the final output more efficiently than for only one seek;Which it can't. The WHERE clause contains a non-equal search. As I'm sure you're well aware and are ignoring to prove a point, once you hit a range search in an index, the rest is ignored.
So, even with an index of (StartDate, EndDate), the StartDate range precludes any usage of additional columns. Since that's a uniquely described predicate after the initial seek for further filtering, you could put dancing monkeys in there for all it'd affect the seek efficiency.
I wasn't ignoring anything. Me real point was that indexes could be modified in the future in unknown ways, obviously w/o rewriting all existing code. In such cases, it's possible two separate indexes could then exist, one for each column. Then the optimizer might be able to do seeks on each separate index to determine a final list ... unless, as in the situation being examined, one column was excluded from a seek because it contained ISNULL().
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".
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply