June 3, 2014 at 2:41 pm
patrickmcginnis59 10839 (6/3/2014)
So whats the beef with "best practices"?
I'm sure the problem is with the:
objectively bad recommendations listed under a title "best practices"
June 3, 2014 at 2:55 pm
Luis Cazares (6/3/2014)
patrickmcginnis59 10839 (6/3/2014)
So whats the beef with "best practices"?
I'm sure the problem is with the:
objectively bad recommendations listed under a title "best practices"
That I can agree with. One thing I've held on to is that I can always learn something, I can't reinvent or do original research about everything, and if I can google or otherwise search for "best practices" <insert technology here>, I don't see the downside. As always, you can't go ask random joe on the street for expert advice when you don't know his credentials but if Microsoft posts "best practices" concerning their technologies, I'm going to take advantage.
June 3, 2014 at 2:58 pm
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'
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
June 3, 2014 at 3:03 pm
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.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 3, 2014 at 3:09 pm
Evil Kraig F (6/3/2014)
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.
Couldn't agree more. I feel your pain, and sense the anger.:-)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 3, 2014 at 3:28 pm
Evil Kraig F (6/3/2014)
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.
God I hate scoped variables. How many times did I have to go through the entire package tree to find a hidden variable...
Regarding the alternate error logging: before SSIS 2012, the standard error logging was inadequate in my opinion. It was hard to retrieve the number of rows transferred, sometimes it truncates really long error messages. There are many resources (books by Microsoft people and MVPs) that talk about creating your own custom logging.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2014 at 3:42 pm
Koen Verbeeck (6/3/2014)
Regarding the alternate error logging: before SSIS 2012, the standard error logging was inadequate in my opinion. It was hard to retrieve the number of rows transferred, sometimes it truncates really long error messages. There are many resources (books by Microsoft people and MVPs) that talk about creating your own custom logging.
I've yet to have the pleasure of 2012, still working against 05 and 08. I've never needed anything but the step log from the job. Copy/pasting from the execution results tab into notepad so I can see the error is annoying during design time but I've just gotten used to it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 3, 2014 at 4:12 pm
patrickmcginnis59 10839 (6/3/2014)
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"?
See Gail's post above. Same reasons. Lot's of people couch their own ideas as "best practices" (note the quotes now and before). Of course, there are some real "best practices" that have become crap because of people's misinterpretations of them.
--Jeff Moden
June 3, 2014 at 6:51 pm
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.
Tom
June 3, 2014 at 7:09 pm
patrickmcginnis59 10839 (6/3/2014)
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"?
There's nothing wrong with best practices. However, as Jeff said there's plenty wrong with what some people claim to be best practices. For example I've seen claims it is best practice to avoid any use of table variables no matter what. I've also seen claims that it is best practice never to join more than two tables, and always for example do a four-way join as three two-way joins two of which generate intermediate results in temp tables. And it's unbelievable how many people claim that it's always best practice to eliminate non-elementary functional dependencies in the schema if it is necesary to do that to obtain something that looks like BCNF - best practice in other words to give a schema that doesn't prevent all known functional-dependency related update, insert and delete anomalies preference over one which does.
Tom
June 3, 2014 at 7:29 pm
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).
June 3, 2014 at 11:53 pm
Evil Kraig F (6/3/2014)
Koen Verbeeck (6/3/2014)
Regarding the alternate error logging: before SSIS 2012, the standard error logging was inadequate in my opinion. It was hard to retrieve the number of rows transferred, sometimes it truncates really long error messages. There are many resources (books by Microsoft people and MVPs) that talk about creating your own custom logging.I've yet to have the pleasure of 2012, still working against 05 and 08. I've never needed anything but the step log from the job. Copy/pasting from the execution results tab into notepad so I can see the error is annoying during design time but I've just gotten used to it.
That is fine during design time, but I'd like the full error at runtime. I've had cases where the actual error was cropped off because the error message started by printing the entire SQL statement. Pretty annoying.
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 2:36 am
Koen Verbeeck (6/3/2014)
That is fine during design time, but I'd like the full error at runtime. I've had cases where the actual error was cropped off because the error message started by printing the entire SQL statement. Pretty annoying.
I've never had that happen. We're talking about the same thing, right? Open up the job step, and there's a place to log results to a text file. I've never had anything in there truncate, and it's the full output of the package.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 4, 2014 at 2:44 am
Evil Kraig F (6/4/2014)
Koen Verbeeck (6/3/2014)
That is fine during design time, but I'd like the full error at runtime. I've had cases where the actual error was cropped off because the error message started by printing the entire SQL statement. Pretty annoying.I've never had that happen. We're talking about the same thing, right? Open up the job step, and there's a place to log results to a text file. I've never had anything in there truncate, and it's the full output of the package.
I'm talking about the built-in SSIS log, which logs to a table.
I don't think the same issue exists when you log to a file, since there is no column with a restricted length.
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 6:21 am
I have to echo the hatred of leading semicolons, commas at the beginning of lines and poorly formatted code. Nested views have a special place of loathing in my heart.
Add using the wrong data type to the list. Dates should be stored as dates. Integers should be stored as integers, not nvarchars.
Another thing I can't stand is not even trying to make a query perform well. The mantra of "well, it takes as long as it takes" is crazy. Eventually, the ever-popular complaint about locks comes up because their query is blocking data from being written to the table while it takes 20 minutes to run and then they try to blame someone else. "Well, my code works" is thrown out, but the word "slowly" is never added to it. To echo what Jeff said, this then becomes a departmental "best practice" for the next similar job because "it just works" and the problem spreads. These are the same people who will then ask "Why is SQL so slow?" :w00t:
Viewing 15 posts - 46 through 60 (of 271 total)
You must be logged in to reply to this topic. Login to reply