August 29, 2011 at 10:20 am
Hey Guys-
This post is purely out of curiosity... Over the years I've definitely come-up with a SQL coding style which works for me and keeps my code uniform, but I've found some styles used by others have become almost pet peeves... so just curious what styles of coding do you use and what do others use which might make you cringe? For example do you use brackets around everything or only when needed? What about table aliases?
I know everyone has their standard way of coding, like where they put their commas, and's, or join's, so what's your preference?
Just from my point of view here are some of the guides I use when coding:
- If query has one table I rarely if ever use aliases, but for two or more tables I generally do, especially if tables have long names or are temp tables, table variables, or in a different database or linked server.
- I like to keep all elements of a line of logic together, so I never move a comma, 'and', or 'join' statement to the end of the prior line or the beginning of the next line.
- I generally do like to use brackets around my table and column names because for me it just looks better plus it doesn't blue-out the names which might be reserved words in SSMS, but I don't always follow this. I do try to either use brackets across the entire query or none of the query.
- As for alignment I try to use tabs to keep similar logic in line, and I loath queries justified to the left on all lines.
- None of the databases I've written use a case sensitive Collation, but I do try to keep the case in my queries on par with the table or column names. I don't care for queries written in all lower case or upper case.
With all this I'm not saying any particular way of coding is correct or wrong, I'm just noting what works for me and am curious what works for others and why. And likewise what techniques do others use which make code harder for you to follow?
Sam
August 29, 2011 at 10:45 am
I tend to use brackets where needed. I use table aliases and column aliases where they are needed or where they help to read the query, like if I have to reference the same table twice. I'll use column aliases for readability when I'm doing Insert...Select, to name the selected columns the same as their target columns. Makes it easier to debug, especially if the list of columns is more than four or five long.
On the actual formatting, I use one of the SQL Prompt (Red Gate product) layouts, modified mainly to eliminate a bunch of extra space-padding. Queries end up looking like:
SELECT MyTable1.Col1,
MyTable1.Col2,
MyTable1.Col3 AS MT1_Col3,
MyTable2.[Complex Column Name] AS MT2_ComplexColumnName
FROM dbo.MyTable1
INNER JOIN [My Linked Server w/ Non-compliant Name].MyLinkedDB.dbo.MyTable2
AS MyTable2
ON MyTable1.ID = MyTable2.MyTable1ID
WHERE MyTable1.InsertedDate >= DATEADD(week, -1, GETDATE())
ORDER BY MyTable1.Col1 ;
I used to use a different format, but found out it worked less well than I thought on different screen sizes, so I went to this one.
And, yes, for readability and future-proofing, I do try to end all queries/commands with a semicolon.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2011 at 9:47 am
One trick I learned a while ago is to modify SSMS to change tabs to spaces and to change the default tab size to 8 characters instead of the default 4 characters.
With this set - whenever someone else opens one of my scripts or procedures, they get the same spacing I used in the first place.
Other people like to change the fonts - which can have a huge impact on how the code looks on another system regardless of your style.
I use SQL Assistant for both intellisense and formatting. SQL Assistant allows for a wide range of customization for formatting your SQL. It also works on multiple database engines which is just a nice bonus if you work on multiple database products.
Over time, I've changed how I format my code - and I expect that I will continue to change it as I find/see something else I like.
For any scripts or procedures that I will be saving (which is pretty much everything):
o I always use a table alias (note: SQL Assistant automatically generates this for me too)
o I always terminate SQL statements with a semi-colon
o Only use brackets when I have to - for me, they make the code harder to read
o Using Intellisense - all my objects are case-sensitive, even though I use a case-insensitive collation
o I use leading commas, some people loath that - I prefer it...
o I only put one column per line in Select statements...I will use more than one line when needed, for example - case expressions, sub-queries, etc... But, I will never have multiple columns on a single line. For me, this is very hard to read and identify what columns exist on that line. Especially if there are any functions being called.
o I don't use the AS keyword when aliasing tables, but I do use it when aliasing columns. For the formats I use the AS keyword here just doesn't look right (to me).
o I use Initcaps for keywords (Select, From, Where, Order By, etc...). Others prefer uppercase, still others prefer lowercase and I can work with either and will adjust my formatting to the original authors formatting in most cases.
When I edit someone elses code - I try to figure out their formatting rules and adjust my changes to their formatting. If that cannot be done, then I will try to get as close as possible and at least make my changes fit in...
If I am taking ownership of the code and will be responsible for it - I modify the formatting to my style completely.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 4, 2011 at 5:01 pm
GSquared (8/29/2011)
I tend to use brackets where needed. I use table aliases and column aliases where they are needed or where they help to read the query, like if I have to reference the same table twice. I'll use column aliases for readability when I'm doing Insert...Select, to name the selected columns the same as their target columns. Makes it easier to debug, especially if the list of columns is more than four or five long.On the actual formatting, I use one of the SQL Prompt (Red Gate product) layouts, modified mainly to eliminate a bunch of extra space-padding. Queries end up looking like:
SELECT MyTable1.Col1,MyTable1.Col2,
MyTable1.Col3 AS MT1_Col3,
MyTable2.[Complex Column Name] AS MT2_ComplexColumnName
FROM dbo.MyTable1
INNER JOIN [My Linked Server w/ Non-compliant Name].MyLinkedDB.dbo.MyTable2
AS MyTable2
ON MyTable1.ID = MyTable2.MyTable1ID
WHERE MyTable1.InsertedDate >= DATEADD(week, -1, GETDATE())
ORDER BY MyTable1.Col1 ;
I used to use a different format, but found out it worked less well than I thought on different screen sizes, so I went to this one.
And, yes, for readability and future-proofing, I do try to end all queries/commands with a semicolon.
You do realize that using table names as part of the 2 part naming convention in the SELECT list (and other simlar places) has been deprecated, right?
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply