June 15, 2011 at 3:49 am
Great article sadly I am a guilty of all those errors 🙂 haha
Thanks for the article
June 15, 2011 at 7:23 am
jasona.work (6/14/2011)
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
I make it a habit to put USE at the top of every script. It's not something I see a lot of people doing.
- 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 15, 2011 at 7:33 am
david.rowland (6/14/2011)
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
And, of course, this absolutely won't work:
SELECT
eggs,
cheese,
milk,
--bread,
cookies,
salt,
flour
FROM
Oh wait! Yes it will! Never mind.
The trailing vs leading commas thing is specious. There is NO actual advantage to either one. They both have exactly the same problems and exactly the same solutions, just one has problems with the first column listed and the other has problems with the last column listed.
The reason Microsoft defaults to leading commas is because that was the style used in punchcard days, because it made it slightly easier to deal with resorting dropped stacks of punchcards. It has had absolutely no actual reason to exist since we stopped using punchcards.
So, use it if you like it, but don't pretend, even to yourself, that it's anything other than a purely aesthetic preference. Anything else is specious. (Look that up if you don't know what I mean.)
- 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 15, 2011 at 8:04 am
there is 1 reason to add commas to the front.
if I copy and paste a bunch of columns out of the 'modify table' screen into a query, I need to add commas to each column.
I do this by typing comma -> down -> left -> comma -> down -> left... n
this adds commas to the start of the line and allows for very fast typing.
if I add commas to the end of the line I have to type comma-> down-> end-> comma-> down-> end... n
this means the keys are further apart on the keybaord and I'm more likely to mis-type.
teeny tiny little benefit there but it's not one I'd like to do without considering I have been doing such typing quite a lot recently!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 15, 2011 at 9:00 am
@ GSquared: Thanks for keeping the cookies in your example!
"The trailing vs leading commas thing is specious. There is NO actual advantage to either one."
I agree the discussion is somewhat specious (Yes, I had to look it up ;-). There are much bigger fish to fry, so I wouldn't even comment on this during a code review.
That being said, I personally prefer leading commas
1) because it's more obvious when one is missing,
2) because I like to script SELECTs and INSERTs from SSMS and then paste chunks into existing queries, and
3) because I find myself making changes to the last column more often than the first.
I had no idea that that format was a throwback to punchcard days, though!
@ben: Have you checked out the scripting options available in SSMS? Sounds like you might save yourself a bunch of comma -> down -> left -> comma -> down -> left...s by using the scripting tool to generate column lists instead of table designer. Apologies if you know this already, but I usually right click on the table and then do "Script table as"->"Select to"->"Clipboard", paste at the end of my working query window, and then copy/paste needed columns into my query. This is also handy if you've inherited tables with spaces in the column names because the scripting tool wraps them in square brackets for you.
June 15, 2011 at 9:05 am
This may be the 5 most common T-SQL errors you'll encounter when typing into a SSMS query window, but I rarely see them in a production or QA envrironment, unless the SQL is embedded in strings and executed dynamically. Trying to debug remote queries, for example sending a pass-through SQL query to Oracle, can also be problematic.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 15, 2011 at 9:19 am
Andy,
can't really use script table (i dont think- please do correct me if im wrong! im all for anything that saves me effort!) as it adds the INT NULL, CHAR(45) NOT NULL etc etc.
what im doing is insert into table (
great
,long
,list
,of
,fields
)
select
great
,long
,list
,of
,fields
from othertable
so i'd need to remove all the INT NULL blah blah to use that output which takes longer.
and using the table designer to give me the list of fields as it saves typing some 60+ column names!
table designer also adds [] to the clipboard where needed.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 15, 2011 at 11:36 am
Ben,
Sounds like you might be doing a "Script table as"->"CREATE To"... Which, of course, gives you column defs and more.
You can also do a "Script table as"->"INSERT To"...
Let's say you want to insert into AdventureWorksDW.dbo.DimAccount. Scripting this as an INSERT gives you:
INSERT INTO [AdventureWorksDW].[dbo].[DimAccount]
([ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[CustomMembers]
,[ValueType]
,[CustomMemberOptions])
VALUES
(<ParentAccountKey, int,>
,<AccountCodeAlternateKey, int,>
,<ParentAccountCodeAlternateKey, int,>
,<AccountDescription, nvarchar(50),>
,<AccountType, nvarchar(50),>
,<Operator, nvarchar(50),>
,<CustomMembers, nvarchar(300),>
,<ValueType, nvarchar(50),>
,<CustomMemberOptions, nvarchar(200),>)
GO
If you want to insert into this from another table (instead of a value list), delete everything below and including "VALUES" and then script your source table as a SELECT. Of course you'd likely use a different table, but using the same example table, you get:
SELECT [AccountKey]
,[ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[CustomMembers]
,[ValueType]
,[CustomMemberOptions]
FROM [AdventureWorksDW].[dbo].[DimAccount]
GO
Just paste this where you chopped off the VALUES up above and add whatever criteria you need afterwards.
(SSMS omitted "AccountKey" from the INSERT script because it's an IDENTITY column so, in this case you'd need to either add it to the INSERT section and precede the query with a SET IDENTITY_INSERT, or remove it from the SELECT and let SQL Server populate it.)
Hope this helps!
Andy
June 15, 2011 at 4:20 pm
GSquared (6/15/2011)
And, of course, this absolutely won't work:
SELECT
eggs,
cheese,
milk,
--bread,
cookies,
salt,
flour
FROM
Oh wait! Yes it will! Never mind.
The trailing vs leading commas thing is specious. There is NO actual advantage to either one. They both have exactly the same problems and exactly the same solutions, just one has problems with the first column listed and the other has problems with the last column listed.
The reason Microsoft defaults to leading commas is because that was the style used in punchcard days, because it made it slightly easier to deal with resorting dropped stacks of punchcards. It has had absolutely no actual reason to exist since we stopped using punchcards.
So, use it if you like it, but don't pretend, even to yourself, that it's anything other than a purely aesthetic preference. Anything else is specious. (Look that up if you don't know what I mean.)
Gezz, I feel like a bit of an idiot now...
Seriously though, there is some thing that annoys me when coding and the commas are are the end of the columns which is close to what I was trying to get across. I think it is to do with complicated queries and debugging them.
Lets try this again.
Ignoring the fact that the query is a very simple one, sometimes I need t odebug code by removing all the columns and adding them back one at a time.
If commas are at the end of the sql, this involves more work:
SELECT
eggs,
cheese,
milk /*,
bread,
cookies,
salt,
flour*/
FROM ...
-- Moving the comment down a column, though this example looks simple, can be
-- a pain if you hae a complex query with large calculations in it.
SELECT
eggs,
cheese,
milk,
bread /*,
cookies,
salt,
flour*/
FROM ...
SELECT
eggs
,cheese
-- ,milk
-- ,bread
-- ,cookies
-- ,salt
-- ,flour
FROM ...
-- This example is easier debugging as it does not matter how long
-- or complicated the columns are
SELECT
eggs
,cheese
,milk
-- ,bread
-- ,cookies
-- ,salt
-- ,flour
FROM ...
Anyway, at the end of the day it is what ever you want to use. Commas at the beginning of the columns is my perferred method, others like to code different. I don't think this is one of those Mac/PC or Linux/Windows kind of problems.
June 15, 2011 at 10:52 pm
Nice one... Good list of errors which we do face regularly even knowing those.... 🙂
June 16, 2011 at 1:50 am
Thanks Andy. often wondered what the script as insert was for lol
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 16, 2011 at 7:03 am
Ben, the other thing you can do to get a list of the columns in a table is drag-and-drop the "Columns" header from the object explorer into the editor. Instead of clicking it to expand the list, just drag-and-drop it into the query.
- 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 16, 2011 at 7:22 am
Regarding how to get a formatted list of columns, you can also query them from the information_schema.columns view and add things like commas as needed. Just drop this into your Template Explorer.
select column_name+',' from information_schema.columns
where table_name = 'sysmail_event_log';
log_id,
event_type,
log_date,
description,
process_id,
mailitem_id,
account_id,
last_mod_date,
last_mod_user,
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 20, 2011 at 5:53 am
Eric M Russell (6/16/2011)
Regarding how to get a formatted list of columns, you can also query them from the information_schema.columns view and add things like commas as needed. Just drop this into your Template Explorer.
select column_name+',' from information_schema.columns
where table_name = 'sysmail_event_log';
log_id,event_type,
log_date,
description,
process_id,
mailitem_id,
account_id,
last_mod_date,
last_mod_user,
That will work, but it's significantly more work than dragging and dropping in the UI.
- 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 23, 2011 at 9:42 am
My bestr error ( I hve lots) was this.
SELECT
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply