January 25, 2014 at 11:45 am
Comments posted to this topic are about the item SQL Best practices, policies and code-smells.
Best wishes,
Phil Factor
January 25, 2014 at 12:59 pm
I do agree with what you say about 'best practices' and hard-and-fast rules...
I am no SQL expert by any means---but, I will say that after so many years of doing procedural programming that when I had to face SQL (small / large queries) to convert reports, I can see that trying to put an envelop around SQL and say that 'this' or 'these' are the 'correct' ways in all cases is something that seems not possible....
January 25, 2014 at 4:04 pm
Good day,
I disagree with the notion of what is being presented as "best practices" and I strongly disagree with the presumption that "we are different" is an argument for not adopting best practices. We are all different, our solutions are all different, factor out what is common and you will be well informed by best practices for that discipline.
Software design (an activity within software development) and information design are two separate disciplines so contemplating best practices from one and applying them to the other constitutes a "straw man" argument. The SDLC best practices around code management, structured design, documentation, requirements gathering, testing patterns are all applicable to the management of design and artifacts for database architecture and SQL coding. There is very little in common once you get into the nuts and bolts of information design (entities, attributes, relations) vs. the structure of UI, business and data access software objects.
Best practices with regards to implementation via patterns are proven to work for specific problems and should be applied when that is the problem, most of our data design is not unique, most of our business rules have been encountered and solved before, there are unique challenges that occur and they may cause us to deviate from direct implementation of a best practices pattern. That is the developers art and we are entitled to it via experience and education.
As for code tidiness and policies, these need to be considered in regards to the overall project because poorly written (messy, undocumented, obfuscated...) code is technical debt that will accrue interest over time. If the project is large or long term then there is very little to be gained by cobbling together something that "works" but isn't pretty. We all understand urgency and timelines so let's not spend time rehashing that.
One of your core best practices should be to review your current best practices with regards to the evolution of your technology.
Complacency and Fools are a problem unto themselves irrespective of the cause they are proposing, we could accrue their malignancy to any aspect of our jobs so it's not justifiable to bring them into this discussion.
I see best practices as guidelines not absolutes, perhaps that's where our positions on this subject differ.
Respectfully,
Derek.
January 27, 2014 at 3:41 am
One of your core best practices should be to review your current best practices with regards to the evolution of your technology
Derek's post is full of sound advice, and what I have quoted is possibly the best. The only constant in our business is change.
We should avoid linking the idiocy that says best practice are absolute rules with the idiocy of throwing out the concept of best practice because our industry contains some PHB-like idiots.
Best practice is there to give guidelines, to demonstrate to decision-makers, auditors, and implementers that some thought has gone in to how to take the best business advantage out of a component of technology.
Best practice operates at an intermediate level in the software stack. The term 'best practice' is being mis-used both when it is used to direct strategic decisions about which platform to purchase, and when it is used to impose micro-management of code syntax. It can (IMHO) rightly be said that it is best practice to not use the term best practice in these areas.
Best practice also changes over time. A few years ago it was best practice to develop all new Windows applications using .Net v1. It is now very much a best practice to refactor any remaining .Net v1 applications to at least .Net v4. This did not happen because .Net v1 suddenly turned to dust, but because a new technology that provided greater business benefits appeared.
SQL FineBuild, which I have authored since 2008, seeks to install and configure SQL Server according to best practice. Some of what SQL FineBuild does has remained constant over the past 6 years, but other items have been added and some removed as the understanding of what is needed to take the best business advantage out of SQL Server has changed. I am confident that the new owners will continue to change SQL FineBuild so that it keeps up with future changes to best practice.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 27, 2014 at 5:44 am
I'm not sure why the industry adopted the term best practices. I much prefer calling them recommended practices, and should be called that only when you can cite how the recommendation applies to what you are doing.
January 27, 2014 at 6:59 am
Robert.Sterbal (1/27/2014)
I'm not sure why the industry adopted the term best practices. I much prefer calling them recommended practices, and should be called that only when you can cite how the recommendation applies to what you are doing.
I like the term "best practices" because it more easily gathers recommendations together when related to particular technologies, especially in the age of google, like folks wanting to get up to speed on a tech can google "best practies <technology>" and see whats up.
January 29, 2014 at 6:03 am
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?
February 1, 2014 at 7:39 pm
I could rant for a couple of hours on the term "Best Practices" but I won't put anyone through that. Suffice it to say that I've become more than a little annoyed with how often the term is abused and some of the reasons why people use it.
Nice article, Phil. I appreciate the time you took to write it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2014 at 7:40 pm
David Atkinson (1/29/2014)
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?
WHOSE "Best Practices"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2014 at 5:41 am
Jeff Moden (2/1/2014)
David Atkinson (1/29/2014)
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?WHOSE "Best Practices"?
I guess I meant one's own best practices rather than "absolutes", which I'm aware that the term implies. Phil Factor's preference in using "code smells" is understandable, although I have a personal dislike for this terminology due to the association with bad odours! Having said that, I'm not sure what I'd use instead as a more appropriate term.
I'd be interested to know if anyone found tools like SQL Cop to be helpful?
February 3, 2014 at 7:41 am
David Atkinson (2/3/2014)
Jeff Moden (2/1/2014)
David Atkinson (1/29/2014)
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?WHOSE "Best Practices"?
I guess I meant one's own best practices rather than "absolutes", which I'm aware that the term implies. Phil Factor's preference in using "code smells" is understandable, although I have a personal dislike for this terminology due to the association with bad odours! Having said that, I'm not sure what I'd use instead as a more appropriate term.
I'd be interested to know if anyone found tools like SQL Cop to be helpful?
Heh... I'm right there with you. I hate the term "code smells". They should call it what it really is... crap code. 😀
On the tooling to help with T-SQL "best practices", thanks for the clarification of what you meant by "best practices". I read about many of the tools, although I don't remember their names simply because I thought they were kinda low level especially when applied to legacy code where I was able to determine what various problems with performance and accuracy were. The "tools" didn't find those things. With that thought in mind, I've taken to monitoring what the slowest and/or most resource intensive code is for the day and deciding what I'm going to fix in the near future. I've also been training the Devs and DAs by turning 100% peer reviews into mentoring opportunities and doing separate training in the form of "Lunch'n'Learns". Yeah... it takes a lot of my time but not as much as most people would think and the value of doing such things has been great. Peer reviews have become much shorter because the developers have learned from the other peer reviews and the quality of code has gone way up. It used to be that every piece of code submitted for promotion to prod was a performance disaster just waiting to happen. The tide has reversed and the developers are actually fixing legacy code when they come across it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2014 at 8:54 am
I've had a go at compiling a list of tools/add-ins that attempt to identify potential T-SQL code issues.
This tool from George Mastros[/url] is reviewed on Simple Talk[/url].
SQL Enlight for SSMS (14-day trial, $195/user)
A sophisticated tool that can be extended with user-defined checks, and is reviewed on Simple Talk.[/url]
I couldn't find any reviews, but it does have a blog[/url]. It integrates into SSMS and is very straightforward to use.
TSQL Code Smells Finder (open source project)[/url]
By Dave Ballantyne[/url], and available in Visual Studio[/url].
February 4, 2014 at 1:59 pm
I must say, this is the most moderate and reasonable discussion on SQL Code Smells that I've found online. Usually, a blog about SQL Code Smells (or worse, SQL Coding Guidelines) attracts internet trolls like blood in the water attracts sharks - except that sharks are more civilized. :laugh:
I actually rather like the term "Code Smell". It conveys a sense of vagueness. It always puts me in mind of "smelling" something a bit odd in the garage. It could be a dead rat in the wall (or something the cat dragged in) or it could be nothing at all. Either way, it is worth checking out - just in case.
March 12, 2014 at 1:59 am
We've similarly avoided the use of the term "best practises" because there's always more than one way to skin a cat. Instead we've developed a SQL checklist. Before code goes into production, the author needs to go through the checklist. It's not about making sure you have a tick against every item. It's about reminding the coder of common pitfalls. For example in some circumstances it's fine to use SELECT DISTINCT, but because it's been so badly abused at our workplace, if you are going to use it, put in a comment and justify it. Same with cursors, UNION (instead of UNION ALL) and so on and so on. Basically there are reasons to do some "smelly" things from time to time, but what you're trying to achieve better be clear to the poor sod who inherits your code.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
December 1, 2014 at 6:29 am
Jeff Moden (2/1/2014)
I could rant for a couple of hours on the term "Best Practices" but I won't put anyone through that. Suffice it to say that I've become more than a little annoyed with how often the term is abused and some of the reasons why people use it.Nice article, Phil. I appreciate the time you took to write it.
Putting it bluntly, it seems mostly to be a fallback phrase used by charlatans from third party suppliers, who can barely spell SQL Server, to sound more authoritative when they're either guessing or talking total ****.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply