January 20, 2023 at 4:19 pm
At my last place of work the DBA used a code "beautifier" to format his SQL and he insisted that the rest of us comply. I don't have any problem with a software house mandating a house style as it makes maintenance easier once you get used to the style. That said, he did have some weird ideas about tabs.
Anyway, my question is to do with square brackets and including schema names.
HIS STYLE
SELECT
[Staff].[LeaveRequests].[Date]
FROM
[Staff].[LeaveRequests]
WHERE
[Staff].[LeaveRequests].[Id] = @StaffId
MY STYLE
SELECT
Date
FROM
Staff.LeaveRequests
WHERE
Id = @StaffId
My inclination is to leave off as much stuff as possible (he also used to alias every table which drove me nuts)
My main question is to do with the square brackets - are they superfluous, harmless, harmful, or sometimes necessary (in my preferred style I would enclose a column name that used a reserved word (something else I don't like to see) - e.g. Staff.[Name]
January 20, 2023 at 4:26 pm
3+ part naming for columns is (due to be) deprecated, so get out of the habit of their style. Instead, alias the object(s) in your FROM and then qualify the column with the alias of the object:
SELECT
LR.Date
FROM
Staff.LeaveRequests LR
WHERE
LR.Id = @StaffId;
In regards to always delimit identifying, I'm personally not a fan. If you must delimit identify an object do, but you should be avoiding (reserved) keywords for object names and aliases anyway.
Honestly, formatting is more about preference. The important thing is readability, consistency, and that what the query is doing is clear. I, personally, don't like overuse of line breaks (like yours above), but that's just my opinion; others prefer it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2023 at 4:33 pm
Thanks Thom. I disagree about aliasing because I don't see the point except when a table is JOINed more than once in the same statement. The reason I raised the issue is because it seems that our development teams are finding LINQ over EF doesn't cut it as well as SQL (to which I raise my alcohol-free glass and shout 'yay'). But formatting LINQ is really controlled by Visual Studio whereas SQL is just text, so I'm trying to come up with a very basic set of guidelines so that our code is more or less readable regardless of who wrote it!
January 20, 2023 at 4:39 pm
I disagree about aliasing because I don't see the point except when a table is JOINed more than once in the same statement.
I used to be on this side, but quickly found early into my career that when writing a query that references one table, you end up going back at a later date and do need to add a JOIN, or an EXISTS, or something. Then you have to go and qualify all your columns in the existing query and then JOIN; otherwise you might end up with several "ambiguous column" errors. It's just honestly easier to qualify from the start. SQL statements that only qualify some columns (the ambiguous ones) are honestly awful to read, as you have no idea what object a column comes from when you're not familiar with the query/objects (and sometimes even when you are).
The reason I raised the issue is because it seems that our development teams are finding LINQ over EF doesn't cut it as well as SQL (to which I raise my alcohol-free glass and shout 'yay'). But formatting LINQ is really controlled by Visual Studio whereas SQL is just text, so I'm trying to come up with a very basic set of guidelines so that our code is more or less readable regardless of who wrote it!
Some companies do have documentation on what style should be used when writing. I wrote the one we use at the company I work at, but it's not enforced (but I will tell someone to format the SQL "better" if it's an unreadable mess). The format I provided in the document is, however, the format people get if they use SQL Prompt and use the "Format SQL" command, as I share the style settings.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2023 at 7:07 pm
First of all, there have been religious wars over code formatting. Even the original post contains a possible declaration of such a war where the OP works.
I'm NOT trying to start one of those. I have methods that I use that make things really easy mostly for ME to read. It turns out that folks at work like the readability aspect and it's what most of us have followed at work for nearly a decade now.
To commence... (remember, this is what I do... no malice is intended here... unless you come to work for me someday 😀 ).
Bracketed names make certain searches very easy but I hate them, won't use them, and wrote the company spec to avoid them. To me, they create horrible screen clutter and increase the chances of having to scroll to the right, which I also hate.
I also strongly believe in using the 2 part naming convention and will usually use it even if only one object is present in a query just because I really like consistency and have also been burned when someone later modifies the code, like Thom posted. Solid 2 part naming also makes the code a lot more portable when someone moves to new databases and so I heavily enforce it even for things like the system databases in all but the rarest of cases. It's a whole lot easier to change synonyms than it is to change 3 and 4 part naming in any code. There's also that scrolling thing I previously mentioned.
Also, to support Thom on his claim that 3 and 4 part naming has been deprecated, take a look at the following article written by a really smart fellow who cites the actual Microsoft documentation on the subject.
https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/
As for the "key words on a separate line" format, I have a strong personal dislike for it because I hate scrolling left/right or up/down to read what should be short SELECTs, etc. At work, I have people break lines at character 120 (119 characters plus the end of line character fits in a 10 CPI monospaced font printed in landscape with 1/2 inch margins, which is also perfect for readability on most screens nowadays without scrolling.
There's a lot more. I have capitalization specs for different parts of queries and names. I prefer the ColAlias = expression format rather than the expression AS ColAlias format to make it much easier to find such things. While I don't enforce it for the company, I also self-enforce a strongly adhered to vertical alignment, a "river" format between keywords and other code, and right align most keywords on to the "shore of the river". Rumor has it that SQL Prompt also has such a format but I don't use such tools because they're not available on every machine that I have to touch.
I also "saw the light" (more burning involved) many years ago and converted to leading commas. It matches with the idea of me putting AND/OR etc in a leading rather than trailing position.
I also have a profound hatred for columns that have the 2 letter name of "ID" or "Id" or "id" and we follow a standard at work that covers what such columns must be named. With rare exceptions, they must be TableNameID. Yep... seems like overkill in properly aliased queries but it has saved lives in more ways than 1.
Almost everyone I tell this to thinks I'm slightly insane and the certain I suffer from massive OCD but, oh my, when they read the code I write the first time, they normally end up telling me that it's the easiest code to read that they've ever seen. The really funny part is, I don't actually write the code that way for other people... I write it that way so I can read my own stuff in the future. That's also why I comment the code the way I do... every SELECT (even in CTEs and Subqueries) has a purpose and I try to add a comment to each one to identify that purpose so that I don't have to read the code to find the code I'm looking for during modifications or cut'n'paste.
And to go a little on the Holy-War of proper naming conventions, I won't sign off on peer reviews, even if everything else is absolutely perfect, if column names are things like "DATE", "ID", "IsNotSomething", has characters that require brackets (except for certain reporting procs), is all lower or upper case, and I usually discourage underscores (there are certain good reasons to use the but not as a general replacement for spaces).
I also have a spec on what needs to go into the flower box, how to separate major sections of code, etc, and we have a template to make most of it easy.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2023 at 8:10 pm
Anyway, my question is to do with square brackets and including schema names. ... My inclination is to leave off as much stuff as possible (he also used to alias every table which drove me nuts)
My main question is to do with the square brackets - are they superfluous, harmless, harmful, or sometimes necessary (in my preferred style I would enclose a column name that used a reserved word (something else I don't like to see) - e.g. Staff.[Name]
Always include schema name; it's an integral part of an object name, and thus SQL will have to figure it out if you don't specify it.
I don't like brackets for standard object names but don't object too much if someone wants to use them. Exceptions:
I do strongly object to using them around data types, [int], since that just clutters up the code.
I do always use them around filegroup names, such as "ON [PRIMARY]".
Btw, "name" is not a reserved word (and thus does not have to bracketed). Not every word that gets colorized is a "reserved" word.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 20, 2023 at 8:22 pm
Thank you Jeff and Scott for your excellent posts. Every day is a school day, even for a soon-to-be-66 year-old programmer who, it appears, only dabbles in SQL (my main day job is C# and javascript).
The deprecation of three part columns came as a surprise to me, and I think I might have to embrace the ALIAS to ensure that I can do what I have to do. I will drop the surplus [] but I don't think I'll ever be converted to leading commas - I think that's at least partly because I'm an English language pedant and believe that a comma belongs after an expression, not before it.
I actually agree about tableId rather than Id (and I have also rejected Pull Requests where devs have tried to name a column Date) - and have in the past argued for it (for one thing, it helps to users to model referential relations) but I'm now working on a mature product where this kind of thing is scattered through the schema, and there is Buckley's chance of refactoring the hundreds of tables, procedures, views and functions, never mind where the entites are modelled inside the C#.
Thanks again.
January 20, 2023 at 8:48 pm
...but I don't think I'll ever be converted to leading commas - I think that's at least partly because I'm an English language pedant and believe that a comma belongs after an expression, not before it..
That's what used to stop me from using leading commas. You'll have an appreciation for my "Saw the Light" comment if you use a lot of trailing comments or have long, complicated expressions. Since I also follow rather rigorous vertical alignment conventions and do the ColAlias = expression thing (like that done in the SET operator in UPDATE), I accidentally stumbled across the great ease in which I can copy a column list and paste it into another query. It's saved me a huge amount of time and I never make the mistake of leaving out a comma anymore. I can't and won't force anyone to use leading commas (there's that holy-war thing about formatting) but, do like I did... try it in some of your stored procs or scripts in the near future (especially if you need multiple copies of the columns) and experience the joy that I did.
And, yeah... even us old dogs can change. 😀 I "saw the light" in my 62nd year and have loved it for the last 8+ years.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2023 at 8:42 pm
edwardwill wrote:...but I don't think I'll ever be converted to leading commas - I think that's at least partly because I'm an English language pedant and believe that a comma belongs after an expression, not before it..
That's what used to stop me from using leading commas. You'll have an appreciation for my "Saw the Light" comment if you use a lot of trailing comments or have long, complicated expressions. Since I also follow rather rigorous vertical alignment conventions and do the ColAlias = expression thing (like that done in the SET operator in UPDATE), I accidentally stumbled across the great ease in which I can copy a column list and paste it into another query. It's saved me a huge amount of time and I never make the mistake of leaving out a comma anymore. I can't and won't force anyone to use leading commas (there's that holy-war thing about formatting) but, do like I did... try it in some of your stored procs or scripts in the near future (especially if you need multiple copies of the columns) and experience the joy that I did.
And, yeah... even us old dogs can change. 😀 I "saw the light" in my 62nd year and have loved it for the last 8+ years.
Nope, never leading commas for me. I hate having to go to the next line to see if the current line has ended or not, and for every line of code you read. It's just not worth it. Besides, yes, it is also terribly unintuitive, especially for us English readers/writers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 22, 2023 at 9:41 pm
I wrote an entire book on this topic, based on the work I did at AIRMICS. Look at ISO-11179 and the Metadata Committee stuff. We found that with good formatting, you could get 10-12% improvement in maintaining code. The US Army and the newspapers had a lot of work on readability in WWII.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 22, 2023 at 9:41 pm
I wrote an entire book on this topic, based on the work I did at AIRMICS. Look at ISO-11179 and the Metadata Committee stuff. We found that with good formatting, you could get 10-12% improvement in maintaining code. The US Army and the newspapers had a lot of work on readability in WWII.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 23, 2023 at 9:01 am
Also, to support Thom on his claim that 3 and 4 part naming has been deprecated, take a look at the following article written by a really smart fellow who cites the actual Microsoft documentation on the subject.
https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/
I'd forgotten I've written that. Almost 3 years ago to the day. I'll have to remember to cite it in the future. Thanks for the reminder Jeff. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 23, 2023 at 5:32 pm
Jeff Moden wrote:Also, to support Thom on his claim that 3 and 4 part naming has been deprecated, take a look at the following article written by a really smart fellow who cites the actual Microsoft documentation on the subject.
https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/
I'd forgotten I've written that. Almost 3 years ago to the day. I'll have to remember to cite it in the future. Thanks for the reminder Jeff. 🙂
Heh... I was wondering why you didn't cite your own work. 😀 And, you're not alone... I have people at work say "Remember when you wrote....". My answer is generally, "No... what's the ticket number? I need to go find it in SVN". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2023 at 7:18 pm
I wrote an entire book on this topic, based on the work I did at AIRMICS. Look at ISO-11179 and the Metadata Committee stuff. We found that with good formatting, you could get 10-12% improvement in maintaining code. The US Army and the newspapers had a lot of work on readability in WWII.
That and useful comments. Thanks for the weigh-in, Joe. You might want to let folks know the USBN for the book you wrote.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2023 at 8:09 pm
SQL Programing Style
ISBN 0-12-088797-5
Amazon has all of my stuff
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply