June 14, 2011 at 6:33 am
rob.carter 79958 (6/14/2011)
Surely too few or too many parentheses should be in the list somewhere, or parentheses in the worong place bracketing off an innappropriate criteria.And I agree that SQL has only ever worked on the second time of asking after a transport layer error due to networking issues and server downtime.
Definitely agree about parentheses being one of the biggest sources of errors.
When I've trained newbies on T-SQL, the errors I see the most frequently are: wrong database, wrong sequence of clauses, ambiguous column name issues in joins, and complete lack of ability to get the idea of outer vs inner joins and the whole concept of joins and sub-queries entirely, and missing Where clauses. Parentheses and the boolean concepts of how Or and And work together are after that.
- 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
June 14, 2011 at 7:31 am
A comment, and a question:
Being a SQL newbie, I've (so far) made surprisingly few of these mistakes. I almost ran into the too many commas, because of copy-and-pasting some CSV data to insert into a table, but caught it before run time.
Now, the question:
On using the wrong database, why wouldn't you put in the
USE {database name}
GO
lines in every query? I wouldn't think it would impact the performance, and if someone isn't paying attention when they run the query, it could avoid potentially messy situations.
Were I work, we're responsible for updating a SQL application from another company, and (almost) none of their update queries include this. They automatically presume the correct DB will be selected before the query is run. I realize that the person running the query should take the time to make sure they're running it in the right place, but if you have to run 20-30-40 queries one-by-one to update a DB, it can be easy to slip.
Interesting article, thanks!
Jason
June 14, 2011 at 7:50 am
USE whatever at the top is my recommendation if you do not want an occassional accident.
In ORACLE I was always using SELECT * FROM GLOBAL_NAME; to see if I was where I though I was.
June 14, 2011 at 8:03 am
RE: Common SQL Error 3 - You're Using the Wrong Database
The way I get around this is to fully qualify all my table names with dbname.schema/owner.tablename
That way I can be explicitly sure I'm querying the right table in the right database when looking at line 1000 of a script and a USE statement isn't nearby.
Also it helps because I've been burned a few times running queries in the wrong db say after a copy paste to a new window, etc...
I like to be as explicit as possible!
June 14, 2011 at 8:18 am
Here's one I get all the time:
"Column 'col1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
because I ALWAYS forget to add cols to the GROUP BY clause after adding them to the SELECT clause
"When you hear hoof beats, think horses not zebras" - a good rule of thumb to follow as this List of 5 attests.
June 14, 2011 at 8:27 am
Nope, NEVER done any of those mistakes. Well...not in the last 30 seconds anyhow:-)
Good to know I'm not the only one though.
June 14, 2011 at 9:18 am
AaronTC05 (6/14/2011)
RE: Common SQL Error 3 - You're Using the Wrong DatabaseThe way I get around this is to fully qualify all my table names with dbname.schema/owner.tablename
...
Many organizations have multiple copies of the same database with different names to support ongoing development and maintenance. For example they might have copies of their production database that are used for development, testing, staging, and training all hosted on the same SQL Server instance with different database names. If change scripts have db names in table name qualifiers, they are much less portable because they need to be edited for each differently named database copy they are run against. In addition to being tedious and error-prone, editing the scripts after performing quality assurance testing invalidates that testing.
P.S. SSMS's scripting tool lets you configure whether or not "Use dbname" is added to scripted objects: Tools->Options...->SQL Server Object Explorer->Scripting->Script USE <database>
June 14, 2011 at 10:22 am
Good article considering it's for newbies.
It would be good to extend it to the top 20.
The 5th point is kind of reckless, maybe you can add some considerations or disclaimers.
I loved the mnemonics.
June 14, 2011 at 10:35 am
Andy DBA (6/14/2011)
AaronTC05 (6/14/2011)
RE: Common SQL Error 3 - You're Using the Wrong DatabaseThe way I get around this is to fully qualify all my table names with dbname.schema/owner.tablename
...
Many organizations have multiple copies of the same database with different names to support ongoing development and maintenance. For example they might have copies of their production database that are used for development, testing, staging, and training all hosted on the same SQL Server instance with different database names. If change scripts have db names in table name qualifiers, they are much less portable because they need to be edited for each differently named database copy they are run against. In addition to being tedious and error-prone, editing the scripts after performing quality assurance testing invalidates that testing.
P.S. SSMS's scripting tool lets you configure whether or not "Use dbname" is added to scripted objects: Tools->Options...->SQL Server Object Explorer->Scripting->Script USE <database>
Good point, I can see the advantage to using the USE statement in that scenario, hadn't thought of that. At my current organization we have the luxury of consistent database names in all our lower environments and PROD.
Thanks for the tip!
June 14, 2011 at 11:42 am
Can't say I've ever had a problem with #4 or #5 (but I love the mnemonic given in #4).
But those first three errors--I must have an indentation in my forehead from hitting it so many times due to one (or more!) of those.
#3 is especially maddening for us since our application deploys with two databases -- test and production (often on the same box), with duplicate schemas. Nothing more ire-raising than trying to track down a data-related error for several minutes only to realize you're looking in the wrong place.
It was worse in SQL2000 and earlier when the database dropdown in QueryAnalyzer was fixed width. If the test and prod database names shared the first 8 characters or so, there was no warning that you were hosed.
June 14, 2011 at 11:48 am
Matt Guthrie (6/14/2011)
Can't say I've ever had a problem with #4 or #5 (but I love the mnemonic given in #4).But those first three errors--I must have an indentation in my forehead from hitting it so many times due to one (or more!) of those.
#3 is especially maddening for us since our application deploys with two databases -- test and production (often on the same box), with duplicate schemas. Nothing more ire-raising than trying to track down a data-related error for several minutes only to realize you're looking in the wrong place.
It was worse in SQL2000 and earlier when the database dropdown in QueryAnalyzer was fixed width. If the test and prod database names shared the first 8 characters or so, there was no warning that you were hosed.
Cue, I should say, not warning.
June 14, 2011 at 12:11 pm
bjamri - yes, you can precede column names with commas, but it looks horrible!
Consider the following shopping list:
, cheese
, milk
, bread
I agree it's sensible and scientific, but I sitll don't like it (and don't teach it). However, I respect those who do ...
Ten Centuries - sincere apologies for problems with SQL skills assessment test. I'll look into this, and sympathise with your comment if that's what happened!
Andy is a director of Wise Owl[/url], a UK company providing training courses (and occasional consultancy) in SQL, Reporting Services, Integration Services and Analysis Services, as well as in many other Microsoft software applications. You can see more about Wise Owl's SQL Server training courses here[/url].
June 14, 2011 at 3:37 pm
AndyOwl (6/14/2011)
bjamri - yes, you can precede column names with commas, but it looks horrible!Consider the following shopping list:
, cheese
, milk
, bread
I agree it's sensible and scientific, but I sitll don't like it (and don't teach it). However, I respect those who do ...
Obviously this is a matter of personal choice and neither format is "wrong". While I agree that it looks horrible from an aesthetic standpoint, the preceding comma format is very handy when performing frequent copy/paste operations. A common scenario might be to add a join to a table in your FROM clause and then to add some columns from the new table to the end of your SELECT. With the trailing comma format, you might forget to add a trailing comma to the end of the last column in your original SELECT and then not notice that it's missing (especially if that column has a long name and you added many new columns). With the preceding comma format, you have to remember to add a preceding comma to the first new column, but it's a lot more visually obvious when one is missing. (I'm guessing that's why Microsoft uses the preceding comma format in SSMS's scripting tool.)
Consider the following two select statement fragments:
To me, the error in
SELECT eggs
, cheese
, milk
, bread
cookies
, salt
, flour
FROM
is more obvious than the error in
SELECT eggs,
cheese,
milk,
bread
cookies,
salt,
flour
FROM
For either format, you can remove columns from the middle of the SELECT statement simply by deleting the entire line. But when using the trailing comma format, if you're removing the last column you also have to remember to delete the trailing comma from the previous line.
Of course when using the preceding comma format, special treatment is required to delete the first column, but in practice I find that I'm much more likely to add/remove columns willy-nilly from the end of a SELECT than from the beginning.
June 14, 2011 at 5:15 pm
My coding method is more like the following:
SELECT
eggs
,cheese
,milk
,bread
,cookies
,salt
,flour
FROM
I like this the best as if I need to remove a column for debugging, I can just place a -- at the beginning of the line rather then worry about the comma and the end of the line:
SELECT
eggs
,cheese
,milk
,bread
-- ,cookies
,salt
,flour
FROM
June 15, 2011 at 1:53 am
If i was going to exclude anything from that select I'd exclude the salt... cookies are too good to exclude from anything 😉
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply