Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Best practices, policies and code-smells. Expand / Collapse
Author
Message
Posted Saturday, January 25, 2014 11:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 587, Visits: 2,528
Comments posted to this topic are about the item SQL Best practices, policies and code-smells.


Best wishes,

Phil Factor
Simple Talk
Post #1534734
Posted Saturday, January 25, 2014 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:06 AM
Points: 1, Visits: 2
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....

Post #1534739
Posted Saturday, January 25, 2014 4:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 9:45 AM
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.

Post #1534755
Posted Monday, January 27, 2014 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,879, Visits: 3,226
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1534918
Posted Monday, January 27, 2014 5:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 158, Visits: 1,800
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.
Post #1534950
Posted Monday, January 27, 2014 6:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 415, Visits: 2,439
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.
Post #1534992
Posted Wednesday, January 29, 2014 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:34 AM
Points: 31, Visits: 404
Is anyone actively using any tooling (free or commercial) to help with T-SQL best practices?
Post #1535859
Posted Saturday, February 1, 2014 7:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1537116
Posted Saturday, February 1, 2014 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1537117
Posted Monday, February 3, 2014 5:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:34 AM
Points: 31, Visits: 404
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?


Post #1537267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse