Just curious, what are your SQL pet peeves ?

Question

Just curious, what are your SQL pet peeves ?

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

joe.eager (6/2/2014)
ORM's
Uncommented code

+10

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL is delicious SSC Eights! Points: 987 More actions · Answer 2

*Rubbing hands together with glee*

SSIS pet peeves:

SSIS packages where tasks/containers/etc. have names like Foreach Loop Container1, Foreach Loop Container2, Data Flow Task, OLE DB Command 1, Execute SQL Task, etc. Lazy. Give them meaningful names, please.
Variables anywhere named var1, var2, var3, etc. Stop that.
Silly descriptions like "Awesome" or "Bob is the best" in SSIS objects. Just please don't.
No annotations in SSIS packages.

...and many more, though I don't want to bore anyone. These are just the ones that popped into my head immediately.

T-SQL pet peeves:

Using LEFT and RIGHT joins in the same query. I don't know why this bothers me, but it does.
Using scalar UDFs in WHERE clauses.
Using NOLOCK.
Cursors and WHILE loops with no comments explaining why that approach was used.
Failing to check for the existence of an object prior to attempting to create it.
Failing to drop a temp table after you're finished using it.
Using a global temp table when it's completely unnecessary.
Spaces in column names.
Misspelled column names. (I'm REALLY picky about spelling.)
Inconsistency in formatting. I don't care if you use leading or trailing commas, but pick one style and stick with it.
SELECT *.
INSERT statements with no column list.
Columns and tables aliased thusly: SELECT columnA AliasA instead of SELECT columnA AS AliasA. I know a lot of people consider AS to be syntactical sugar, but it makes it a lot easier for people to debug your code when it's there.
Code with joined tables where the columns are not aliased.
Code with unnecessary ORDER BY statements.
Views that consist of SELECT * with no WHERE clause. What's the point?
JOIN instead of INNER JOIN. (Personal preference.)
Nested layer after nested layer of hellish subqueries.
No comments! Please comment your code, and put your name on it.
Code where keywords are not in all caps.
If your shop has written coding standards, please follow them. I don't always agree with them, but if those are the rules...those are the rules. You don't get to ignore them just because you don't like them.

That's all I have for now.

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

Many good items listed in the posts above, but I missed one that is a real pet peeve for me:

- leading ; when defining a CTE. Sorry, but semicolons are statement terminators, not statement beginanators.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

A great list discussion - my 3 cents:

- table variables need to be depracated with extreme prejudice

- SSMS generated code (IMAX screen width anybody ?)

- UpperLowerCase as opposed to Upper_Lower_Case (ya got 128 characters, use 'em for clarity)

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

x SSC-Insane Points: 23660 More actions · Answer 5

Rudyx - the Doctor (6/3/2014)
A great list discussion - my 3 cents:
- table variables need to be depracated with extreme prejudice
- SSMS generated code (IMAX screen width anybody ?)
- UpperLowerCase as opposed to Upper_Lower_Case (ya got 128 characters, use 'em for clarity)

Alright, whats the matter with table variables? And additionally, for me they're the go to for saving info during a transaction that might roll back, so what would be your alternative strategy here?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

patrickmcginnis59 10839 (6/3/2014)
Rudyx - the Doctor (6/3/2014)
A great list discussion - my 3 cents:
- table variables need to be depracated with extreme prejudice
- SSMS generated code (IMAX screen width anybody ?)
- UpperLowerCase as opposed to Upper_Lower_Case (ya got 128 characters, use 'em for clarity)
Alright, whats the matter with table variables? And additionally, for me they're the go to for saving info during a transaction that might roll back, so what would be your alternative strategy here?

I agree, table variables have their use. They shouldn't be deprecated but they shouldn't be abused either.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

What is wrong with table variables ?

hmmm ...

table variables are not capable of having indexes (however they support constraints and primary keys)

no indexes mean:

- no index statistics for the optimizer

- no indexes or index statistics which translates to mean a table scan for all access

- no indexes also mean that inserts with a constraint cause a table scan for every insert (the more rows the longer the scan)

Oh, and the initial fallacy that they are memory resident (HAH) - try:

select name

from tempdb..sysobjects

where name like '#%'

as for holding data in a roll-back scenarirollbackis wrong about using regular temp tables ?

anyone else ? did I miss something ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Rudyx - the Doctor (6/3/2014)
What is wrong with table variables ?
hmmm ...
table variables are not capable of having indexes (however they support constraints and primary keys)
no indexes mean:
- no index statistics for the optimizer
- no indexes or index statistics which translates to mean a table scan for all access
- no indexes also mean that inserts with a constraint cause a table scan for every insert (the more rows the longer the scan)
Oh, and the initial fallacy that they are memory resident (HAH) - try:
select name
from tempdb..sysobjects
where name like '#%'
as for holding data in a roll-back scenarirollbackis wrong about using regular temp tables ?
anyone else ? did I miss something ?

There are some issue with table variables. For small amounts of data they are fine.

The point of a rollback is that if you populate a temp table inside a transaction and then rollback you also rollback your temp table. A table variable on the other hand will still contain whatever data it had previously.

There are times when a table variable is the appropriate and preferred mechanism for storing data in a temporary location. Much like cursors, they have a valid usage but get overused/abused by people who don't understand what they do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

My greatest pet peeve of all is what some people claim to be "best practices".

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

below86 SSChampion Points: 11513 More actions · Answer 10

Jeff Moden (6/3/2014)
My greatest pet peeve of all is what some people claim to be "best practices".

That's funny you would say that, we have been told we need to start following 'Best Practices'. So I say "How do you define that?" and "What version?".

Another coding style I dislike is having extra spaces between parts of the SQL. Or not following the same format for all joins.

SELECT field1, field2

-- No need for blank line here

INTO #whatever

-- No need for blank line here

FROM table_name l inner join other_table r ON -- follow the same formatting you do below!!

l.field_id = r.field_id

-- No need for blank line here

LEFT JOIN new_table nt ON

l.field_id = nt.field_id

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 11

Jeff Moden (6/3/2014)
My greatest pet peeve of all is what some people claim to be "best practices".

The plural makes it sound like polygamy

😎

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

SQL is delicious (6/2/2014)
*Rubbing hands together with glee*
SSIS pet peeves:
SSIS packages where tasks/containers/etc. have names like Foreach Loop Container1, Foreach Loop Container2, Data Flow Task, OLE DB Command 1, Execute SQL Task, etc. Lazy. Give them meaningful names, please.
Variables anywhere named var1, var2, var3, etc. Stop that.
Silly descriptions like "Awesome" or "Bob is the best" in SSIS objects. Just please don't.
No annotations in SSIS packages.
...and many more, though I don't want to bore anyone. These are just the ones that popped into my head immediately.

+1

I'll add to that:

* no respect for layout whatsoever, which leads to the SSIS package looking like a spider web

* use of OLE DB commands

* use of the SCD wizard

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 13

Lynn Pettis (6/2/2014)
Many good items listed in the posts above, but I missed one that is a real pet peeve for me:
- leading ; when defining a CTE. Sorry, but semicolons are statement terminators, not statement beginanators.

+ 1000

That is annoying 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Sean Lange (6/3/2014)
Rudyx - the Doctor (6/3/2014)
What is wrong with table variables ?
hmmm ...
table variables are not capable of having indexes (however they support constraints and primary keys)
no indexes mean:
- no index statistics for the optimizer
- no indexes or index statistics which translates to mean a table scan for all access
- no indexes also mean that inserts with a constraint cause a table scan for every insert (the more rows the longer the scan)
Oh, and the initial fallacy that they are memory resident (HAH) - try:
select name
from tempdb..sysobjects
where name like '#%'
as for holding data in a roll-back scenarirollbackis wrong about using regular temp tables ?
anyone else ? did I miss something ?
There are some issue with table variables. For small amounts of data they are fine.
The point of a rollback is that if you populate a temp table inside a transaction and then rollback you also rollback your temp table. A table variable on the other hand will still contain whatever data it had previously.
There are times when a table variable is the appropriate and preferred mechanism for storing data in a temporary location. Much like cursors, they have a valid usage but get overused/abused by people who don't understand what they do.

I have to agree with Sean here, use the right tool for the job.

As for an alternative, you could go with Oracles autonomous transactions could be a possibility.

x SSC-Insane Points: 23660 More actions · Answer 15

Jeff Moden (6/3/2014)
My greatest pet peeve of all is what some people claim to be "best practices".

"Best practices" is pretty much a key phrase that helps gather recommendations together regarding technologies and I'm not really getting why anybody would object to it. Sure, if there are objectively bad recommendations listed under a title "best practices" then I can get that, but otherwise, "best practices" is really just another name for "recommendations" that is probably more google friendly.

So whats the beef with "best practices"?