Common Mistakes in T-SQL

Question

Common Mistakes in T-SQL

Viewing 15 posts - 16 through 30 (of 73 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Hmm, I thought that Jeff already had written the Dates article...?

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

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

RBarryYoung (12/31/2008)
Hmm, I thought that Jeff already had written the Dates article...?

I was talking about a more general common mistakes article series.

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

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.

Heh... how very true... and that's the way it works in many RDBMS's... just not SQL Server. :crazy: We can only wish they will spend some time on it in the near future instead of crap tasks like incorporating a DATE and TIME datatype or going through the trouble of deprecating things like SELECT alias = columnname.

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

How about stupid date queries like this:

where Convert(varchar, CreationDate, 101) between Convert(datetime,@FromDate, 101) And Convert(datetime, @ToDate, 101)

which can amount to something like this:

where '12/30/2008' between '12/25/2008' And '01/05/2009'

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

Ninja's_RGR'us (12/31/2008)
Hey Jeff, that's a great topic for a couple of articles... can't wait to read those!

Actually, that's not a bad idea, Remi... there's a lot of people, especially new ones, that have no clue about how dates actually work. I'm sure there's several articles on SQL Server on it, already, but it may be like the Tally table... even though it's a really old technique, lot's of folks simply had no idea that such a thing even existed.

RBarryYoung (12/31/2008)
Hmm, I thought that Jeff already had written the Dates article...?

Nope... haven't written one on dates, yet. Wrote one on how to do dynamic crosstabs using dates, but not one just on dates.

--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: 1004339 More actions · Answer 6

Michael Valentine Jones (12/31/2008)
How about stupid date queries like this:
where Convert(varchar, CreationDate, 101) between Convert(datetime,@FromDate, 101) And Convert(datetime, @ToDate, 101)
which can amount to something like this:
where '12/30/2008' between '12/25/2008' And '01/05/2009'

BWAA-HAAA!!! Exactly! Even if they insist on building in performance problems like that, they should at least use a style code of 112 for correct sortability...

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

Huh. and here I always use 121.

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

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 8

Jeff Moden
Nope... haven't written one on dates, yet. Wrote one on how to do dynamic crosstabs using dates, but not one just on dates.

Robyn Page wrote a date/time workbench article over on Simple-Talk http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/. I know it's a competing site but she is soooo much easier on the eyes than Jeff :D.

--Paul Hunter

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

RBarryYoung (12/31/2008)
Huh. and here I always use 121.

But not for trying to find a whole date...

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

paulhunter (12/31/2008)
Jeff Moden
Nope... haven't written one on dates, yet. Wrote one on how to do dynamic crosstabs using dates, but not one just on dates.
Robyn Page wrote a date/time workbench article over on Simple-Talk http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/. I know it's a competing site but she is soooo much easier on the eyes than Jeff :D.

Man, do I agree with that! 😛

That's one of the articles I was going to cite... another rather well written article is ...

[font="Arial Black"]

How to Search for Date and Time Values

[/font][/url]

... Bryan Syverson did a really nice job on that one. Anything that I could add between his and the Simple-Talk workbench on the subject, would be pretty much superfluous.

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

Sure:

Select Convert(Varchar(10), Getdate(), 121)

121 covers so many different things that I need/use from dates that I almost always use it, just because it's less things that I have to remember.

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

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

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)

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

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 13

Very Cool. Now if they would let us write aggregates in SQL, life would be perfect!

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

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 14

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

One that has bitten me on the ankle and points north on more than one occasion is mis-handling variables. If you reuse a variable and populate it using SELECT statements you have to be very careful to NULL the variable first or you can get errors or introduce data corruption.

For example:

DECLARE @var1 as varchar(10);

SELECT @var1 = customername from sometable where customerid = x;

SELECT @var1 = customername from sometable where customerid = y;

If the second query returns no value for customerid y then @var1 retains the customername from the first query. When you try to use it the results can range from a failure (if you are lucky) to an update that works but goes against the wrong stuff and you don't know it. Yikes!!!

The smart thing to do, if you plan to reuse a variable, is to run SET @var1 = NULL; each time you finish with it. Of course, that could open a different can of worms, depending on how the variable is used after that. The smartest thing to do is probably not to reuse the variable at all.

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

Jeff Moden (12/31/2008)
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.
Heh... how very true... and that's the way it works in many RDBMS's... just not SQL Server. :crazy: We can only wish they will spend some time on it in the near future instead of crap tasks like incorporating a DATE and TIME datatype or going through the trouble of deprecating things like SELECT alias = columnname.

Don't hold your breath on that one since the dev team seems to be taking its marching orders from the ANSI standards committee. You know how our academic friends like to clean up our labeling (like, really - how does that one EVER make the top of the list for things to discuss).

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