Common Mistakes in T-SQL

Question

Common Mistakes in T-SQL

Viewing 15 posts - 46 through 60 (of 73 total)

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

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

RBarryYoung (1/15/2009)
Can you use those Plan Guide thingys? Or do you need something more stable for that?

That's exactly what we're looking at.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

GilaMonster (1/15/2009)
Jeff Moden (1/15/2009)
I've been fortunate, recently... I've convinced folks to mostly NOT buy 3rd party software and, when we do, I get to grill both the software and the people at the 3rd party company. Better than that, they also gave me veto power. Like I said, I'm pretty lucky.
Well, this is one of my clients, not the company I work for, so they're paying for us to try and fix this. It's just frustrating that there's so little I can actually do.

Heh... understood and appreciated... it's like taking your car to the mechanic for engine repair work and then telling the mechanic that they can't open the bloody hood.

--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)

mazzz SSCertifiable Points: 6139 More actions · Answer 3

RBarryYoung (1/15/2009)
Jeff Moden (1/15/2009)
That's another good one. And, I've never seen it where they need to return an out of scope identity produced by @@IDENTITY... anyone ever actually do that? What was the reason for it?
IIRC, @@IDENTITY came first. Once it was released, customer's began to point out it's incorrectness wrt triggers. Microsoft probably decided not to change @@IDENTITY's behavior, lest someone was actually using it correctly with triggers, and made a whole new function (SCOPE_IDENTITY()) instead.

Thanks for the info, RBarryYoung - I too was curious.

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

Forgetting that the order that AND clauses are evaluated in at run-time has nothing to do with the order that they are written.

I thought SQL Server did always evaluate clauses (on the same precedence) level from left to right.

I know that DB2 often does not; DB2 will sometimes even "rewrite" the conditions to something logically equivalent but faster.

But it was my understanding that SQL Server did not do that, at least thru SQL 2005.

Is there evidence to the contrary?

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".

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

ScottPletcher (1/15/2009)
Forgetting that the order that AND clauses are evaluated in at run-time has nothing to do with the order that they are written.
I thought SQL Server did always evaluate clauses (on the same precedence) level from left to right.

That's compile-time evaluation. The optimizer can reorder that for the execution plan as long as it does not change the meaning of the expressions.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 6

ScottPletcher (1/15/2009)
Forgetting that the order that AND clauses are evaluated in at run-time has nothing to do with the order that they are written.
I thought SQL Server did always evaluate clauses (on the same precedence) level from left to right.
I know that DB2 often does not; DB2 will sometimes even "rewrite" the conditions to something logically equivalent but faster.
But it was my understanding that SQL Server did not do that, at least thru SQL 2005.
Is there evidence to the contrary?

Try this:

create table #T (

ID int identity primary key,

Col1 varchar(100));

insert into #T (Col1)

select '1/1/1' union all

select 'George' union all

select '12/1/25000'

select *

from #T

where isdate(col1) = 1

and datepart(year, col1) = 1

You'll get a conversion error, even though the first part of the Where clause says it has to be a date, which should, by "left to right" logic, preclude the rows that would generate the error.

So, nope, it doesn't do left to right in Where clauses. At least, not in the actual execution.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ScottPletcher SSC Guru Points: 101097 More actions · Answer 7

As I said, it was my understanding that the SQL optimizer did not do that for simple (non-query) expressions connected by boolean operators, that it evaluated left-to-right.

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".

Garadin One Orange Chip Points: 29613 More actions · Answer 8

It is my understanding that the only thing that it will hold true 100% of the time as far as that goes is a case statement. This issue has come up quite a bit in the past couple months with tally table out of range errors and such.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ScottPletcher SSC Guru Points: 101097 More actions · Answer 9

The sample temp table code posted doesn't prove what order SQL evaluated the expressions in. With "and" specified, it always has to evaluate both expressions.

Note that this code does work:

select isdate(col1), *

from #t

where isdate(col1) = 0

or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"

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".

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

select *
from #T
where isdate(col1) = 1
and datepart(year, col1) = 1

If SQL Server evaluated left to right, in the above query, it wouldn't have to evaluate the second condition if the first was false (short circuit) because it would not matter if the second condition was true or false, the entire condition is already false.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 11

If SQL Server evaluated left to right, in the above query, it wouldn't have to evaluate the second condition if the first was false (short circuit) because it would not matter if the second condition was true or false, the entire condition is already false.

In theory, yes, but just because SQL didn't find a given short-circuit doesn't mean that it evaluated the second expression before the first one.

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".

GSquared SSC Guru Points: 260824 More actions · Answer 12

ScottPletcher (1/15/2009)
If SQL Server evaluated left to right, in the above query, it wouldn't have to evaluate the second condition if the first was false (short circuit) because it would not matter if the second condition was true or false, the entire condition is already false.
In theory, yes, but just because SQL didn't find a given short-circuit doesn't mean that it evaluated the second expression before the first one.

The point of the test is that it doesn't evaluate left to right. If it did, it would come up false on the first test, and never have to perform the second test. It doesn't say which sequence, if any, it does evaluate the conditions in. It just proves that it doesn't do a left-to-right-till-it-fails test.

It's a common enough mistake to think that the above test would work, as opposed to being completely useless.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

I built the temp table and ran the following queries:

select *

from #T

where isdate(col1) = 1

and datepart(year, col1) = 1

select *

from #T

where datepart(year, col1) = 1

and isdate(col1) = 1

Both failed for the same reason. This tells me one thing all conditions are evaluated, period. I haven't tried a condition with a different precedence, but I would assume that it really won't make much of a difference.

GSquared SSC Guru Points: 260824 More actions · Answer 14

Yep. If you need them done in a particular sequence, you pretty much need to start staging stuff into temp tables.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ScottPletcher SSC Guru Points: 101097 More actions · Answer 15

Again, this code does work:

select isdate(col1), *

from #t

where isdate(col1) = 0

or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"

SQL will definitely short circuit when it knows it can.

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".