May 7, 2009 at 5:08 am
Jan Van der Eecken (5/7/2009)
Sorry to disagree, but I don't see what's wrong with this, unless your collation sequence is case-sensitive, which in most cases is a mistake in the first place. What I do have a gripe with is that I want my code to look consistent, which Intellisense prevents me from doing if somewhere in history some developer made one single table name CamelCase, but all the other ones are as per coding standards (whatever those may be).
Naming and case conventions should be consistant across the database, this includes all table, procedure, function, variable names and the code which is being written for the database
If some developer has named a table CamelCase instead of CAMELCASE then they need re-education on how the system at that organisation works and the object should be renamed.
May 7, 2009 at 5:21 am
YIKES (not yelling).
Two pages so far on leading commas, UPPER or lower case, the horror and amateurishness of lower cased key words...at least I do not have French-flavored SQL (in my mind I have Inspector Clouseau reading the query to me-- "aye sayd, odor bye beumb").
Next I suppose we will start a rant about GOTO.
May 7, 2009 at 5:33 am
scott mcnitt (5/7/2009)
YIKES (not yelling).Two pages so far on leading commas, UPPER or lower case, the horror and amateurishness of lower cased key words...at least I do not have French-flavored SQL (in my mind I have Inspector Clouseau reading the query to me-- "aye sayd, odor bye beumb").
Next I suppose we will start a rant about GOTO.
And here was me thinking that this was an article about readability...
May 7, 2009 at 5:41 am
Hi, Sylvia
I totally agree with you, many of the developers are using meaningless aliasing and that would be very tough to debug in future and waste of time. even i found some developers are not using proper indentation. It will be always good practice to provide proper indentation like below
SELECT Table1.Field1, Table1.Field2,
Table2.Field3, Table2.Field4,
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.Field1 = Table2.Field3
WHERE Table1.Field1 = CompareValue1
AND Table2.Field3 = CompareValue2
Also it is good practice to provide the syntax / reserved words in Upper case for better readability.
May 7, 2009 at 5:45 am
Do they have computers in France?
May 7, 2009 at 5:50 am
Nice job Sylvia,
When you kick up this much mud, you did well.
I do need to disagree with you though. Picking and choosing when to alias or when not to leads to inconsistent code, which reduces the readability.
Everyone that's worked up over their own little pet pieves, leading comma, trailing comma, capitalized key words.... lighten up. Honest people can disagree. It's mostly a question of habit. Your eye is drawn through certain patterns because you use them all the time.
Personally, I use Red Gate's SQL Prompt to do formatting for me so that I get a very consistent output. I swing both ways on the comma's, but I always put the key words in all caps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2009 at 5:51 am
[
pp.pragnesh (5/7/2009)
Hi, SylviaI totally agree with you, many of the developers are using meaningless aliasing and that would be very tough to debug in future and waste of time. even i found some developers are not using proper indentation. It will be always good practice to provide proper indentation like below
....
That's exactly the reason many firefighter dba's are using e.g. RedGate SQL Refactor.
It puts the layout like you want it.
Maybe that will be the next built in in SSMS :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2009 at 6:10 am
scott mcnitt (5/7/2009)
YIKES (not yelling).Two pages so far on leading commas, UPPER or lower case, the horror and amateurishness of lower cased key words...at least I do not have French-flavored SQL (in my mind I have Inspector Clouseau reading the query to me-- "aye sayd, odor bye beumb").
Next I suppose we will start a rant about GOTO.
Naming and case conventions should be consistant across the database, this includes all table, procedure, function, variable names and the code which is being written for the database
If some developer has named a table CamelCase instead of CAMELCASE then they need re-education on how the system at that organisation works and the object should be renamed.
Won't start a rant about GOTO, Scott, since GOTO has its root in procedural programming (or even spagghetti assembler programming for that matter), not relational thinking. It has its place in SQL if you want to debug a multiple-statement SP for instance and want to skip certain parts of it to see intermediate results, but I would never use it in production systems.
And apologies if I steered the thread away from the readability issue.
Samuel, I agree with you, but I have to live with the shortcomings that exist, so it would be nice if Intellisense wouldn't force me to accept its' decisions without me being able to override them (apart from switching it off in the first place).
May 7, 2009 at 6:25 am
Grant Fritchey (5/7/2009)
Nice job Sylvia,When you kick up this much mud, you did well.
I do need to disagree with you though. Picking and choosing when to alias or when not to leads to inconsistent code, which reduces the readability.
Everyone that's worked up over their own little pet pieves, leading comma, trailing comma, capitalized key words.... lighten up. Honest people can disagree. It's mostly a question of habit. Your eye is drawn through certain patterns because you use them all the time.
I agree, congrats to Sylvia, even if I have to agree with Grant as well. Either you use aliases consistently, or not at all and type the table names as well if (most likely) reuqired.
Grant, I'm not worked up about pet pieves, in fact, I don't really care about case and leading commas, as long as the coding style is consistent. I will however change it to what I feel more comfortable with if I take a piece of code over and have to modify it significantly.
May 7, 2009 at 6:30 am
Jan Van der Eecken (5/7/2009)
Samuel, I agree with you, but I have to live with the shortcomings that exist, so it would be nice if Intellisense wouldn't force me to accept its' decisions without me being able to override them (apart from switching it off in the first place).
Personally I leave the Intellisense switched off when working with 2008
I find some of its behaviour intrusive and it doesn't seem as good as the intellisense in Visual Studio
May 7, 2009 at 6:45 am
Hmm I been seeing more leading comma code in SQL and I thought it was more readable?
But another reason aliases are good is SQL Server 2008 Intellisense. Once you type the . after a table alias (or table name) you get all the columns.
May 7, 2009 at 6:47 am
I actually prefer a, b, and c when you have a classic two or three table join. If the parent table is "a" and the children are "b" "c" etc. It's not like you have to look very far to find out what "b" means.
SELECT vendor.vendor_id,
vendor.vendorname,
vendoraddress.address,
vendoraddress.city,
vendoraddress.state,
vendoraddress.zipcode
FROM vendor INNNER JOIN vendoraddress ON vendor.vendor_id = vendoraddress.vendor_id
Thhe following is much easier to read (for me!):
SELECT a.vendor_id,
a.vendorname,
b.address,
b.city,
b.state,
b.zipcode
FROM vendor a INNNER JOIN vendoraddress b ON a.vendor_id = b.vendor_id
Common sense will tell you when you have to use eponymous aliases, but the vast majority of the SQL statements I write are simple two or three table joins, for which the "a" "b" "c" syntax is, for me anyway, more elegant.
May 7, 2009 at 6:48 am
Intrusive it is indeed. I hate it when I have typed in something, pressed Enter and then it decides I want to use the selected item from the drop-down, when I really wanted a newline :w00t:
It will never be as good as the one in VS by the way, just because of the nature of SQL. It can't really predict what you are after when you start typing "SELECT something..." since it has no context to analyze. That's one of the reasons (probably the major one) why the syntax of LINQ is exactly the other way around. First say where you want something from, and then what you want. That's actually more natural. If you want to buy something you first go to the right shop, then ask the salesperson for the object, not the other way around.
And now I diverted from the general topic of the article again... Promise not to do it again.
May 7, 2009 at 6:57 am
karlsandfort (5/7/2009)
I actually prefer a, b, and c when you have a classic two or three table join. If the parent table is "a" and the children are "b" "c" etc. It's not like you have to look very far to find out what "b" means.SELECT vendor.vendor_id,
vendor.vendorname,
vendoraddress.address,
vendoraddress.city,
vendoraddress.state,
vendoraddress.zipcode
FROM vendor INNNER JOIN vendoraddress ON vendor.vendor_id = vendoraddress.vendor_id
Thhe following is much easier to read (for me!):
SELECT a.vendor_id,
a.vendorname,
b.address,
b.city,
b.state,
b.zipcode
FROM vendor a INNNER JOIN vendoraddress b ON a.vendor_id = b.vendor_id
Common sense will tell you when you have to use eponymous aliases, but the vast majority of the SQL statements I write are simple two or three table joins, for which the "a" "b" "c" syntax is, for me anyway, more elegant.
Yes, but V for Vendor and VA for VendorAddress would make it more meaningful and understandable (if you know the schema), wouldn't it?
May 7, 2009 at 7:06 am
By thinking "relations yield relations", SMV's comments are all the more important (far more so than bickering about case and commas).
When large numbers of "SQL Tables" are joined, good "aliases" help but often aren't enough that a read of the code makes clear what the ultimate predicate is.
What we're calling "derived tables" in SQL are (in a well designed relational database) simply relations themselves, and the end result is simply a relation.
In that case, to use a meaningless (or ordinal) alias is as silly as calling your tables "a, b, and c".
Also by thinking "relations from relations", you can start from tables that are not value relations, and transform them into relations on the fly so that when you get to the tricky joins, you are working with true relations, and have removed all need for outer joins, calls to isnull(r, v), or any other 3VL issues at the later stage. Most modern optimizers recognize that situation and can then apply much better optimization when it can reduce to ordinary 2VL truth tables.
(I have a good example, but I realize that's an article in itself). I like the article, SVL. It demonstrates a certain respect for so-called "subqueries" and "derived tables" that should be at the center of our practice.
Roger Reid
Roger L Reid
Viewing 15 posts - 16 through 30 (of 79 total)
You must be logged in to reply to this topic. Login to reply