June 4, 2014 at 6:37 am
I've slowly grown to favor leading commas in queries for two reasons:
1. It's easy to comment out a column for debugging or to explicitly show that there is a column that you are NOT using
2. I find commas at the end of the line or (worse, much, much worse!) in the middle hard to track down. I've had to look at too many things like:
SELECT a,b,c,REPLACE(REPLACE(d,'a','b'),'e','f') g,e,
f,h
My pet peeve is the deep nesting (> 1 levels) of subqueries where CTEs could be used instead.
June 4, 2014 at 6:49 am
gbritton1 (6/4/2014)
I've slowly grown to favor leading commas in queries
+1
I always use leading commas. It looks cleaner in my opinion and it's easier to align everything.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2014 at 8:04 am
Evil Kraig F (6/3/2014)
Sorry, ;WITH is ingrained into my head at this point. You're stuck with it if you chase me in my code.I agree with a decent amount of what's been said here. I personally trail my commas.
Personal pet peeves not already stated:
not using AS when you define your field names/table aliases. Drives me NUTS because now I'm looking at forumulas not aliasing.
Indent your damned CASE trees/IF trees/whatevers intelligently and consistently. I expect to be able to page down a few times and see, clearly, where that cursor from hell you built ends. I don't expect to have to dig through 6 ENDs to figure out which one is the trailer for the thing when I'm prepping to gut it.
INDEX (0). Particularly without comment(s).
SSIS: Using Row Redirection on bad rows without creating an error trap table to deal with them.
SSIS: Not understanding what a configuration is. How to use it I can train, not knowing it's there is on you.
SSIS: Crappy naming methods for your data sources. You're connecting this thing to a database and you're going to push it through environments. It doesn't need to be named "Martha's stuff in a table". Name it after the database. And for the love of god if I see one more 'Textfile 1', 'Textfile 2' I may shoot someone. At the very LEAST: Inbound CSV from Client, Outbound Flatfile to GL. Or something.
SSIS: Trying to create some form of alternate error logging and delivery system other than what's built in. Because yes, everyone should learn YOUR bloody mess of a system instead of the two that's documented and already included (SSIS logging and step logging in the job).
SSIS: Scoping variables at very low levels, and then re-using the name elsewhere. I get scope. I get why it's used in PROGRAMMING. I don't expect to have to click on EVERY object on the screen to figure out where the hell you scoped something. Global them.
OMG YES YES YES to all of this.
*raises a glass*
June 4, 2014 at 8:08 am
Koen Verbeeck (6/4/2014)
gbritton1 (6/4/2014)
I've slowly grown to favor leading commas in queries+1
I always use leading commas. It looks cleaner in my opinion and it's easier to align everything.
I'm "bi" when it comes to leading/trailing commas.
If I'm writing quick queries to grab data for someone or if I'm developing something and need to comment out columns, I use leading commas. Since most places I've ever worked either explicitly require trailing commas or have traditionally always used them (without written standards), I switch them to trailing once I'm settled on my design. Doesn't take long.
I don't have a strong preference either way, though I agree that leading commas are easier when you're developing code because they're easier to comment out. 🙂
June 4, 2014 at 8:16 am
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 ?
They fixed some of the index issues with table variables in 2014. But table variables could always have an index. You just couldn't create indexes on the table variable after the fact.
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
June 4, 2014 at 8:19 am
GilaMonster (6/3/2014)
patrickmcginnis59 10839 (6/3/2014)
So whats the beef with "best practices"?Over-priced 'consultants' who justify their non-standard settings and unconventional configuration options with 'It's best practice' with an undertone of 'and lesser individuals such as yourself wouldn't understand'
Any attempt to disagree, discuss or suggest alternatives get met with 'Our recommendations are according to best practices' or 'your recommendations are against best practices'
I would add software vendors to that statement. I have had them tell me their settings are BP numerous times with those same undertones.
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
June 4, 2014 at 8:26 am
Evil Kraig F (6/3/2014)
SSIS: Crappy naming methods for your data sources. You're connecting this thing to a database and you're going to push it through environments. It doesn't need to be named "Martha's stuff in a table". Name it after the database.
This made me think of another really bad pet peeve.
Naming the database as _Prod, _UAT, _QA, _Dev in order to match the different environments. Not only does it screw with code releases, it screws with SSIS packages (and yes configurations help a bit with that, but then do you leave the DS named after Dev where you built the package in the first place?).
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
June 4, 2014 at 2:24 pm
SQLRNNR (6/4/2014)
Evil Kraig F (6/3/2014)
SSIS: Crappy naming methods for your data sources. You're connecting this thing to a database and you're going to push it through environments. It doesn't need to be named "Martha's stuff in a table". Name it after the database.This made me think of another really bad pet peeve.
Naming the database as _Prod, _UAT, _QA, _Dev in order to match the different environments. Not only does it screw with code releases, it screws with SSIS packages (and yes configurations help a bit with that, but then do you leave the DS named after Dev where you built the package in the first place?).
It only makes sense if all those databases are on the same instances. (sounds like a lovely idea)
You could suffix the databases names if the databases are in source control and you need to branch.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2014 at 3:37 pm
Koen Verbeeck (6/4/2014)
SQLRNNR (6/4/2014)
Evil Kraig F (6/3/2014)
SSIS: Crappy naming methods for your data sources. You're connecting this thing to a database and you're going to push it through environments. It doesn't need to be named "Martha's stuff in a table". Name it after the database.This made me think of another really bad pet peeve.
Naming the database as _Prod, _UAT, _QA, _Dev in order to match the different environments. Not only does it screw with code releases, it screws with SSIS packages (and yes configurations help a bit with that, but then do you leave the DS named after Dev where you built the package in the first place?).
It only makes sense if all those databases are on the same instances. (sounds like a lovely idea)
You could suffix the databases names if the databases are in source control and you need to branch.
On the other hand, it has saved the lives of multiple DBAs that I know (myself included on one instance) because they thought they were in a dev envirionment when they were really in a prod environment. 99.9% of all code written can be setup to correctly use synonyms which also removes the need for 3 and 4 part naming requirements (which I never allow in code reviews).
As far as SSIS packages go, thanks... you've just given me yet another reason to avoid SSIS whenever possible. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2014 at 3:44 pm
Jeff Moden (6/4/2014)
Koen Verbeeck (6/4/2014)
SQLRNNR (6/4/2014)
Evil Kraig F (6/3/2014)
SSIS: Crappy naming methods for your data sources. You're connecting this thing to a database and you're going to push it through environments. It doesn't need to be named "Martha's stuff in a table". Name it after the database.This made me think of another really bad pet peeve.
Naming the database as _Prod, _UAT, _QA, _Dev in order to match the different environments. Not only does it screw with code releases, it screws with SSIS packages (and yes configurations help a bit with that, but then do you leave the DS named after Dev where you built the package in the first place?).
It only makes sense if all those databases are on the same instances. (sounds like a lovely idea)
You could suffix the databases names if the databases are in source control and you need to branch.
...
As far as SSIS packages go, thanks... you've just given me yet another reason to avoid SSIS whenever possible. 😛
Where was SSIS mentioned to give you that conclusion?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2014 at 7:52 pm
Great thread gentlemen this helps a new guy like myself out. Knowing what irritates the more experienced guys/gals will help alot.:blush:
I will say its crazy some of the stuff you guys describe. I just thought trying to write clear and concise SQL code was natural to all.:hehe:
As far as commas go I find myself typing with trailing and wishing I had done leading. Same reasons about commenting out.
I agree with all except one of the complaints about using extra white space and readability.
I find this very readable.
SELECT
,t1.id
,t1.Column1
,t1.Column2
,t2.Column3
FROM 1_Table t1
INNER JOIN 2_Table t2 ON t2.id = t1.id
WHERE t1.Column = 1
Maybe I'm weird and not yet mature enough in my code yet, but I like the separation.
***SQL born on date Spring 2013:-)
June 5, 2014 at 12:10 am
Following some standard formatting is important, especially if it it used by all developers for an application
I prefer this style with key words in lower case, but that comes from working on a system where all object and column names were in uppercase.
select
t1.id,
t1.Column1,
t1.Column2,
t2.Column3
from
Table1 t1
inner join
Table2 t2
ont2.id = t1.id
where
t1.Column1 = 1
order by
t1.id
I have just seen too much code that seems to be randomly formatted:
select t1.id,t1.Column1,
t1.Column2,t2.Column3 from
Table1 t1 join Table2 t2 on t2.id=t1.id where
t1.Column1=1 order by t1.id
Something I really hate is a system where there is no thought to error handling. We use try/catch with a standard header at the beginning of the proc, and a standard error handler at the end. When a proc errors out in production with a formatted error message telling you exactly where the error occured you will be glad you did.
Another thing I have really come to hate is when a procedure inserts into and/or updates multiple tables and does not use a transaction (assuming that is not done by the front end).
I do a lot of cutting and pasting from a model stored procedure that has a lot of the error handling, try/catch, input parameter checks, and begin/commit transactions built into it.
How about using standard naming conventions for procedure parameters? If you have an Orders table and the primary key column is named OrdersID, use @OrdersID if that is what you need to pass to the procedure, instead of @OrdID, @Ord_ID, @Order.
Use the same convention for foreign key columns. It the OrderItems table has a foreign key reference to the Orders.OrderID column, then call it OrderID, not OrdID, etc.
I think that following some standards for object and column naming, procedure layout, error handling, and coding would eliminate a tremendous amount of effort over time as the system is tested and enhanced.
June 5, 2014 at 12:42 am
I think this thread sums up the general feeling towards code formatting displayed in this thread.
http://www.sqlservercentral.com/Forums/Topic1577630-391-1.aspx
There is soap behind the counter to wash your eyes with...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 3:34 am
Lynn Pettis (6/3/2014)
TomThomson (6/3/2014)
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?
Good point.
And what's this 128 character restriction, anyway? Are you suggesting people should use the characters from char(0) to char(31) and also char(127), so that you get 128 characters instead of just the 95 that aren't control characters (layout control or device control) so that it's actually feasible to use them for visible text, but none of the 123 usable characte in the bottom half of the Extended Ascii tables in the top half. The sensible limit is 218 characters (the space character plus the 217 that make a mark that occupies one character position on the screen or on the paper) although some of the layout control characters will be used too, to control layout. If you want to stay in the bottom half of the table, which is valid if, for example, you want to print your code on a fifty year old barrel printer, you have 95 but your vintage printer may support a bit fewer than that.
Maximum length of an object name as they are defined as sysname which is a defined as nvarchar(128).
Arrgh! Head-desk!! I though he meant 128different characters as in that "_" he used as seperator. Must learn to think harder before responding.
Tom
June 5, 2014 at 3:43 am
thomashohner (6/4/2014)
Great thread gentlemen this helps a new guy like myself out. Knowing what irritates the more experienced guys/gals will help alot.:blush:I will say its crazy some of the stuff you guys describe. I just thought trying to write clear and concise SQL code was natural to all.:hehe:
As far as commas go I find myself typing with trailing and wishing I had done leading. Same reasons about commenting out.
I agree with all except one of the complaints about using extra white space and readability.
I find this very readable.
SELECT
,t1.id
,t1.Column1
,t1.Column2
,t2.Column3
FROM 1_Table t1
INNER JOIN 2_Table t2 ON t2.id = t1.id
WHERE t1.Column = 1
Maybe I'm weird and not yet mature enough in my code yet, but I like the separation.
I suspect that when you start trying to cope with fairly complex queries you will find yourself cursing because they spread over half a dozen screens instead of fitting onto one.
Tom
Viewing 15 posts - 61 through 75 (of 271 total)
You must be logged in to reply to this topic. Login to reply