SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Best practices, policies and code-smells.


SQL Best practices, policies and code-smells.

Author
Message
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 2971
Comments posted to this topic are about the item SQL Best practices, policies and code-smells.


Best wishes,

Phil Factor
Simple Talk
jspencer2
jspencer2
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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....
Derek Tinney
Derek Tinney
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 39
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.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5794 Visits: 3866
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Robert.Sterbal
Robert.Sterbal
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 2000
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.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 Visits: 5510
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.

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
David Atkinson
David Atkinson
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 516
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85997 Visits: 41094
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85997 Visits: 41094
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Atkinson
David Atkinson
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 516
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search