Common Mistakes in T-SQL

Question

Post reply

Common Mistakes in T-SQL

hbpank

SSC Eights!

Points: 828
More actions
December 30, 2008 at 2:29 pm

#126688

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...
Thanks a lot,
Hary

Viewing 15 posts - 1 through 15 (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

Not an exhaustive compilation. Just a couple that bug me.

http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

The most common one that I see is treating T-SQL as a procedural language. Cursors, loops, nested IF statements, all have their places, but overuse is the single most common error in SQL. Think in sets and you'll avoid those, think procedurally, and you'll make them.

To add to what Gail posted about error handling, a variation on that that I've seen is assigning a value to a variable, hitting an error later in the proc, rolling back transactions, then returning that value. For example:

begin try

declare @NewID int

insert into dbo.MyTable (columns)

values (values)

set @NewID = scope_identity()

insert into dbo.MyTable2 (columns, Table1ID)

values (values, @NewID)

end try

begin catch

rollback

exec dbo.MyErrorProc

end catch

return @NewID

I've actually seen code like that a few times in the last few years, and it ends up returning an ID value for the first insert, but it can roll back the insert, leaving nothing there for that ID. Does the most interesting things to front-end code that uses that ID value and gets no data.

Another common error I see is pulling the same data over and over again in the same proc. Select once, store locally, use as many times as needed.

But the most common one is definitely procedural code vs set-based code.

- 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

Jeff Moden SSC Guru Points: 1003935 More actions · Answer 3

Yep... I agree with all of that as being the biggest mistake. The easiest way to start thinking in the set based pardigm is to stop thinking about what you want to do to a row... think about what you want to do to a column, instead.

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

Thanks for the blog post Gail, I don't use error-checking and transactions as much as most, since I can't write to anything but Tempdb, but wasn't aware of these issues yet, so useful to me if to nobody else! 😎

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

Sure, why not.

Here's three:

Expecting a list to be directly assignable to a variable.

DECLARE @a varchar(50)

SET @a = '1,2,3,4,5'

SELECT *

FROM SomeTable

WHERE SomeField IN @a

Trying to select where something = NULL

SELECT *

FROM SomeTable

WHERE SomeField = NULL

Using a WHERE clause that eliminates your outer join (This is often a mistake of concept, there's nothing wrong with the code)

SELECT *

FROM SomeTable A

LEFT JOIN SomeOtherTable B ON A.Something = B.Something

WHERE B.SomeValue = 'Wrong'

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Oh without a doubt:

Believing that rows in a table have an order.

And it's even more pernicious corollary: Order(rows returned) = Order(rows inserted)

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

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

So:WHERE IsNumeric(StringColumn)

AND CAST(StringColumn As NUMERIC) > 35 will undoubtedly return an error sooner or later.

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

Another one is managing transaction the wrong way. Specifying begin tran and commit tran only thinking that if there will be an error, it will automatically roll back the transaction.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003935 More actions · Answer 9

RBarryYoung (12/31/2008)
Oh without a doubt:
Believing that rows in a table have an order.

As you know, there is one place where it is... UPDATE in the presence of a CLUSTERED INDEX SCAN. It's what makes the running total method possible.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

True, but that's not "SQL" so much as it is an implementation artifact of T-SQL and SQL Server. And it's got so many conditions on it that I frankly don't trust it on any table except a temporary table that is created solely to exploit it.

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

Jeff Moden SSC Guru Points: 1003935 More actions · Answer 11

I was just reading yet another series of droll and longwinded articles on temporal data types consisting mostly of useless history and a whole bunch of rhetorical crap not having anything to do with the problems at hand, but serve more for the authors to fluff their own feathers and look down their noses at the common developer... 😛

Anyway, it reminded me of a very serious common computational mistake made by even those who are supposedly experienced with SQL Server...

[font="Courier New"]WHERE SomeDate BETWEEN @StartDate AND @EndDate[/font]

... or...

[font="Courier New"]WHERE SomeDate BETWEEN @StartDate AND @EndDate + 1[/font]

Of course, there's the method that causes no computational error but does virtually guarantee performance problems ...

[font="Courier New"]WHERE somewholedatecalulation(SomeDate) BETWEEN @StartDate AND @EndDate[/font]

In most cases, the correct way to do this to account for the possible time element that may be included in a DateTime datatype and to give an INDEX SEEK a chance to occur is...

[font="Courier New"]WHERE SomeDate >= @StartDate

AND SomeDate < @EndDate + 1[/font]

--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 SSC Guru Points: 1003935 More actions · Answer 12

RBarryYoung (12/31/2008)
True, but that's not "SQL" so much as it is an implementation artifact of T-SQL and SQL Server. And it's got so many conditions on it that I frankly don't trust it on any table except a temporary table that is created solely to exploit it.

Oh yea of little faith... 😉 But, I do understand the misgivings.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Hey Jeff, that's a great topic for a couple of articles... can't wait to read those!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Jeff Moden (12/31/2008)
RBarryYoung (12/31/2008)
True, but that's not "SQL" so much as it is an implementation artifact of T-SQL and SQL Server. And it's got so many conditions on it that I frankly don't trust it on any table except a temporary table that is created solely to exploit it.
Oh yea of little faith... 😉 But, I do understand the misgivings.

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.

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