Common Mistakes in T-SQL

Question

Common Mistakes in T-SQL

Viewing 15 posts - 31 through 45 (of 73 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

GilaMonster (1/1/2009)
RBarryYoung (12/31/2008)
The irony of course is that if SUM(..) OVER(..) allowed ORDER BY, 2/3rds of the uses for it (the Update pseudo-cursor) would disappear.
Apparently (and this is not confirmed), that's planned for the 'next major version of SQL Server'
Not only the Order by in the over, but also the range option, allowing things like this.
SUM(SomeValue) OVER (ORDER BY Col1 partition by col2 rows between unbounded below and current row) -- a running total
Avg(someValue) Over (Order By Col1 partition by col2 rows between current row -2 and current row +2)
Just remember to all do the rain dance that they also fix the performance problem with the Windowed aggregates as well. New functionality is VERY cool, but if it's like sucking cement through a straw - it's going to get tossed aside....
Apparently Itzik has a whole bunch of connect items filed for these. I'll see if I can dig up the links so that we can add more votes

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 2

hbpank (12/30/2008)
what are the Common Mistakes in T-SQL ?
For example: @@Error gives correct value in next line only. after that it Value gets Reset.
Do we have any compilation of that kind? Is there any book on that?
Thanks in Advance...

Good question. My 0.02:

Errors due to implicit type conversions

Poor performance due to poorly-chosen clustered indexes

Misuse and abuse of IDENTITY columns

And my personal no.1 bête noire :

Data lost or destroyed due to the execrable "UPDATE... FROM" syntax

(I mean the bug-feature that causes random undefined results without error or warning when the join criteria is non-unique. Why oh why hasn't this been fixed, deprecated or removed by now?)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

No mention yet of the common trigger mistake:

SELECT @Variable = SomeValue

FROM inserted

The becoming mystified why only 1 row was processed in the trigger.

I still see this in a lot of code.

Todd Fifield

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I see that mistake alot, as well... especially by Oracle programmers because you can't write an Oracle trigger using what looks like set based code. You write the trigger using RBAR and then give it the instruction FOR EACH ROW.

--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)
Intro to Tally Tables and Functions

mazzz SSCertifiable Points: 6139 More actions · Answer 5

the one I've seen a lot is the use of @@IDENTITY rather than SCOPE_IDENTITY(), when retrieving the id of a newly inserted row

Not that @@IDENTITY would necessarily be wrong, but it does leave you open to errors

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

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

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?

--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)
Intro to Tally Tables and Functions

mazzz SSCertifiable Points: 6139 More actions · Answer 7

I have never had to do this, and can't for the life of me see an occasion where I would want to.

Which begs the question, why didn't they make the variable that everyone would be most likely to use return the in-scope identity, and have a slightly obscure out-of-scope identity function for the few who might need it, rather than the other way round?

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 8

I would say that biggest source of the problems that I've seen over the years are design problems. In many cases I run into developers that put the code together -- including the queries -- before the data is ever modeled. The database is then constrained to their code rather than the business needs with a frequent response of something along the line of "We will address problems later on if problems are identified."

As far as actual coding problems I normally identify filtering by date as the most chronic problem I see; something like:

-- The date format varies, but the theme is the same

WHERE convert(varchar(10), someDateField, 101) = '01/15/2009'

Fortunately, this problem is usually trivial to fix.

I would say that through the years fixing cursors has been the biggest time drain. Unfortunately, while fixing date problems is trivial, cursor problems frequently are not.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Kent Waldrop (1/15/2009)
I would say that biggest source of the problems that I've seen over the years are design problems.

Agreed. I'm in the middle of a wonderful one now.

Rather than using stored procs, this bright vendor decided to store query fragments somewhere in the client app, assemble a composite query, and then use sp_executesql to execute that, passing parameters.

I have a 64 processor server with 128 GB of memory on its knees because of parameter sniffing, poor exec plans and generally poor code. And there's virtually nothing I can do about it, because the vendor has 6 month change cycles and I can't touch the queries.

Incoming network traffic to SQL (just the queries) amounts to about 2 GB/min

Oh, and this is an OLTP system.

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: 1000539 More actions · Answer 10

GilaMonster (1/15/2009)
Agreed. I'm in the middle of a wonderful one now.

That's just nasty... went through that in my last company. Some high level genius at the company bought a major software package with similar problems.

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.

--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)
Intro to Tally Tables and Functions

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 11

One of the conversations that I have had a number of times was to ask what kind of teeth I was going to have as a dba -- was I going to have any teeth or be only a "paper lion". I've always strangely found that databases work better when a dba has some teeth. I've also found that you even if you don't start out with teeth you can start to grow teeth when features that I have objected to go wrong a number of times. And veto power can stop many problems before they ever happen.

Nice, Jeff.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

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.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

Kent Waldrop (1/15/2009)
And veto power can stop many problems before they ever happen.

I officially had veto rights at my last company. I say officially, because the CIO tended to overrule me.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

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.

Can you use those Plan Guide thingys? Or do you need something more stable for that?

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

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.

[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]