A Fine Slice Of SQL Server
I received a great collection of blog posts in response to my T-SQL Tuesday 151 – asking people to write on T-SQL Coding Standards.
Read on for the full set of responses, including one brand new blogger in Gerard Jaryczewski, who has already won favor with me with a GK Chesterton reference.
Leave a CommentProbably the one I hate most. And one that is stubbornly persistent. Object name prefixing.
Or, to be more precise, the standard that enforces that all table names need to start with a prefix that designates them as a table, and all view names with a different prefix to clearly mark them as a view. Typically tbl_ and vw_ are used, though I have also seen just the letters t and v, and I have seen them with or without underscores.
I hate this coding standard (or rather, naming standard) with a vengeance. For a few reasons. The perceived benefit is in fact not a benefit at all. It is detrimental to a quick understanding of what I see on the screen. But my biggest objection is that it negates one of the greatest benefits of views.
Read on to understand why this is a bad idea. I completely agree with Hugo on this.
Leave a CommentKenneth Fisher points out the downside of coding standards:
I’m betting you can start to see the problem right? Joe is supporting application A. His team wrote it, and they wrote it using a strict set of coding standards. Jane supports application C. Her team didn’t write it, it was transferred over during the reorg last March. Her team has a set of coding standards they enforce, unfortunately, application C wasn’t written with them. All of their new code is however because their manager wants strict enforcement of their coding standards. Oh, and Joe and Jane are both being moved to be part of a new team next week. They’ll be supporting some older code, that, you guessed it, is using a whole different set of coding standards, if any.
Starting from the Coasean notion of the firm as a means of internalizing externalities and achieving economies of scale + scope, this is just about where you hit the margin for added productivity… Rephrasing this not to be in economist jibber-jabber, this kind of thing is a big part of why really large companies essentially spin off mini-companies and act nearly-independently under the parent company’s umbrella. It’s essentially impossible to create and enforce a meaningful set of standards once you hit a certain threshold of developers, especially when it comes to the more opinion-heavy standards.
Leave a CommentDeb Melkin stands in front of the gate:
When I think of what my coding standards are, I tend to think of it as how would I review scripts for a pull request (PR). I think my past coworkers can attest that I can get quite picky when I look at code. So if I were your reviewer, what sort of things would I comment on?
Click through for a checklist.
Leave a CommentTom Zika writes out a list of coding standards:
No magic constants
What is
4? Just set a variable (constant) from a lookup table. Or write a comment with an explanation.
It’s the least you can do.
Read on for a set of things I generally agree with. This one’s a little bit of a tough case, as in extremely high-performance systems where the cost of a lookup is just too expensive, I’d prefer to use keys rather than values and save the compute cost and possible worse query plan. That said, even in that case, yes, definitely write a comment explaining what 4 is. That said, had I picked any of the other dozen or so recommendations, I wouldn’t have had anything interesting to say other than “go read this.”
2 CommentsKevin Chant values consistency:
Over the years I have worked a lot with automating T-SQL updates across multiple databases. During that time, it has become very clear that both consistency and comments are essential to make the process smooth.
Naming conventions of objects created using T-SQL should be consistent. Along with comments in your code for colleagues or anybody else who picks up your code.
Especially if you are working in a team who want to work in a DevOps related way.
I value consistency a lot but I’ve found I’m too inconsistent to be consistent.
Leave a CommentKenneth Fisher finishes a roundup:
This months subject for T-SQL Tuesday was Your first technical job. I have to say I really really enjoyed reading everyone’s stories and it in a lot of ways made me feel closer to all of you. Thanks for participating!
And of course, if I forgot someone please let me know so I can get them added!
Click through to see everybody’s entries.
Comments closedCamila Henrique summarizes the most recent T-SQL Tuesday:
I had a lot of fun reading your different takes on it! Here are the posts of everyone who contributed (and dutifully linked it back to me).
Click through for the list of entries.
Comments closedWhen doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.
You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.
Read on for the why. This is excellent advice.
Comments closedChad Callihan has some advice:
A quick T-SQL/SSMS trick that I know and love is the ability to add text to multiple lines at a time. I tend use macros in Notepad++ to accomplish these types of repetitive actions but this type of work can be completed in SSMS.
Annoyingly, this doesn’t work quite the same way in VS Code or Azure Data Studio. Alt + Down moves a line down one, Alt + Shift + Down copies the current line. To replicate this behavior in VS Code / Azure Data Studio, hold down Alt + Shift and use the mouse to select the lines you want.
Comments closed