December 10, 2004 at 7:55 am
Steve, Thanks for the great article.
One thing you did not mention (in Part 1) is the common practice of using only singular names for table names. This has helped me many times--or should I say that not following this practice has caused many headaches. My advice is that when naming a table pretend it has only one record in it.
I like your idea of leading commas and also putting the join statements on the beginning of a line. Most format tools put the join command on the end of the previous line which causes much confusion for me. I usually end up rearranging them so I can read the statement. When will the visual tools begin allow us to dictate the format they use.
Dave Catherman
December 17, 2004 at 10:31 am
Bravo, Steve! A superb description of formatting standards, as well as the justification for using each element!
I have used almost exactly the same standards since shortly after I started working with SQL Server 6.0, many eons ago. My only difference is that I set my tab stops to 4 spaces instead of 3 - this gives me the option to indent something just half a tab stop if I feel it needs it. And I do just that with BEGIN/END pairs - it makes matching up BEGIN/ENDs much easier when they are nested inside the control-flow statement with which they are used, and the inner code is then indented to the next full tab stop.
One other thing I like to do is to code SQL reserved words in ALL CAPS. I know this is a little redundant when working in Query Analyzer, thanks to the color-coding, but it makes queries and scripts that much easier to read when you export them to another tool, say Notepad, or Outlook to e-mail them to a colleague.
Kindest Regards,
Matt Gauch
December 17, 2004 at 10:56 am
Excellent article. I've had my share of headaches trying to decrypt poorly formatted code. I've also noticed that many of the people I've helped, located the problem themselves while watching over my shoulder as I formatted it.
I have to disagree about the practice of putting the commas before the column name in the select list, though. I find it terribly distracting, while not adding significantly to the debug process, for this simple reason: If you put the commas first, you can comment out any line you like except the first one. It doesn't have a comma, so line #2 will break. If you put the commas afterwards, you can comment out any line you like except the last one, for the same reason. So I always put them last to make it more readable.
December 17, 2004 at 1:17 pm
Kindest Regards,
Just say No to Facebook!December 17, 2004 at 1:18 pm
Kindest Regards,
Just say No to Facebook!December 17, 2004 at 3:43 pm
Agree with your standards. Never thought of indenting the FROM, WHERE etc by only two spaces. That would make "folding" in a text editor easier to use.
I would also suggest that all INSERTS include the field names, i.e., INSERT INTO ##t_temp1 (col1, col2, col3) SELECT a,b,c from xxx. I find it easier to understand what the prior person is trying to do if the code has.
December 20, 2004 at 9:50 am
Wow, SQLServerCentral.com now does mind-reading!
I was ready to add my 2 cents to this thread, when I noticed that my post to a similar thread from August 2002 was already there!!
Best regards,
SteveR
December 20, 2004 at 4:32 pm
A most excellent article. Two more cents more than two years late, but who's counting?
Glad to see that I'm not the only person using "leading comma" format. One reason to do this not yet mentioned: this style makes it very hard to forget a comman. (Ever burn five minutes debugging a query only to find you overlooked a comma in the midst of your case-statement jungle?)
Cent number 2: tab key yes, tab character no. I set tabs to 4, but have them insert spaces. This way your text always lines up in non-proporitonaly typefaces, regardless of interface (Query Analyzer, Notepad, Word, Excel, email-of-choice, etc.) An example: Steve's article says he sets tabs to three characters, but in the article's code samples the tabs throw stuff a bunch of picas [picas? in software code? Why?] across the screen, blowing away his format.
Philip
April 22, 2005 at 1:20 am
Liked/agreed with pretty much everything but the prefix v on the views. I use the suffix "_v" and it gets the point acrosss without getting in the way.
Same with stored procs -- I use [group_]object_action[_qualifier]. Where:
As a sample this yields
--Paul Hunter
April 22, 2005 at 1:36 am
Yo are correct -- it's a crutch. There are many things Query Builder can't handle -- like tha case statement or return multiple results or receive xml for the import parameter or test. The list gets longer and make QB a poor choise for editing many of the sql objects that exists in most databases.
--Paul Hunter
May 26, 2005 at 1:06 am
About formatting: please do write neat statements. In my job as a DBA I do a lot of DB & app optimizing and it's nice to have good formatted statements. If you don't have good formatted statements, check this:
I found this http://www.sqlinform.com/ on the internet. I use it all the time whenever I am performance tuning that badly written Database application we use at our company.
This one application just writes down a select statement on 1 (!!!) line and it consists of selects, joins, subqueries, cases, casting, convert, sum, having, groub by, more subqueries and more.... I just grab it in profiler, paste the code on sqlinform and voila, I can read the SQL statement. Unfortunately the code still contains all keywords known to mankind... 🙁
JP
May 26, 2005 at 6:10 am
Thats a nice littel utilty there. I personally would love to see something that can add to this the ability to add table & field aliases using a best pratices approach meaning that all TABLE names and aliases are captalized and the field aliases are done in the form of
AS 'Alias Name'
Obvously the field alias would need some pattern to use and so I'd be fine with the field alias being the form of
'Table Alias_Field Name'
Even though that may not be as descriptive a field alias as woudl be est used it would still be better then a query that has no field alias at all.
I am curious about one thing when it comes to well formed SQL code. I don't understand why so many believe that placing each field within the SELECT clause on a new line looks best. To me it seems like such a serious waste of space just like those who use 8 spaces for a tab (how many spaces does one need to see something is tabbed?) I personally dislike the new line for each field approach because in lengthy queries it requires constant scrolling up and down of teh screen just to read the thing. I find that when fields are aliased as they should be and done in the form of
AS 'Field Alias'
WHere the keyword AS is used and the field alias is surounded in single quotes that it is very easy to spot each field individually in a single line. This allows me to add as many field items to a singel line as will fit within one width of my screens resoution whcih I keep at a modest 1280 x 1024 so that chances are that anyone who reads my quries will also see the entire line in a single screen width. I personally find that when properly aliasing field names like this and placing more then one field on a single line I am often able to see the entire query in a single screen and that is a serious plus. Being able to see the query in it's entirety at once makes troubleshooting and eidting a heck of a lot easier.
So my query is why is the new field on a new line so popular with people?
and..
Why do so few use the AS with single quotes when using field aliases? Even though the langauge doesn't require that fields be aliased in this manner it still makes the query easier to read and besides there are several things that are done in queries that aren't required but are done for ease of readability.
Thanks.
Kindest Regards,
Just say No to Facebook!May 26, 2005 at 6:19 am
Placing a column on a single line is easier when you're developing new queries. You can easily place a remark BEFORE the field or AFTER the field (esp. if you use the sortcutkeys CTRL+SHIFT+C / R):
SELECT
ID
, Firstname
-- ,Lastname
, Street -- just the name of the street
, City
FROM
tblPersons
WHERE
lastname = 'smith'
-- AND FirstName = 'Tom'
JP
May 26, 2005 at 7:14 pm
You hit the nail on the head and is what I've been evangelizing about for years. I also detest the aliasing format of SomeColumn As SomeOtherName and prefer the style of SomeOtherName = SomeColumn. Additionally and block and line up the equal sign. I'd only alias a column when it's absolutely necessary.
There again I fall back on my old standard of using the standard that's present even if it's really bad. After all I just write the code -- I don't own it.
--Paul Hunter
May 27, 2005 at 7:06 am
Well if you try working with a query using some interface that doesn't support syntax highlighting and you compare this
SELECT T.Field As 'Alilas'
FROM TABLE T
verses this
SELECT T.Field Alias
FROM TABLE T
You'll see very quickly that the first instance that uses complete syntax is far easier to read and therefore use. If you look at these two in something that does show syntax highlighting like Query Analyzer you'll see that the first is still easier to read because the use of the keyword AS and the single quotes around the alias make it quick and easy to locate field and their alias. You might want to change your take on field aliases because their useage is starting to become required in more and more areas. For example Crystal Reports as of version 9 requires all fields to be properly aliased. It really is better to alias fields regardless of the syntax or methdo you use for field aliases. As SQL Server progresses the product is demanding users more fully qualify their statements. For example in SQL Server 2005 you can get away with out fully qualifying your query meaning you can leave out schema however your query performs faster when you fully qualify it.
Besides what's the downside to fully qualifying a query aside from the fact that it adds a few extra secs to the query construction? I'm not making any accusations about why you or anyone else has a preference for query constrcution that excludes fully qualifying the query but what I can state for fact is that at my company most of our development pepole had this no field alias unless necessary attitude and when our product moved form supporting Crystal 8 to Crystal 9 every single report they had written and to be redone because they did not use field aliases. Leaving out the field aliases saved them maybe a few minutes at best when first creating the reports however they paid a heavy price for that when it came time to edit the reports to work with the newer version of Crystal. And they couldn't balme Crystal for the change because even though they could leave out field aliases in Crystal 8, the products best practices docs recomended always using field aliases.
Whats the point here? Fully qualifying queries are far less likely to require changes down the road but taking shortcuts and trimming your quries down to the bare minimum can come back to haunt you.
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply