Don''t blame SQL please....
-
I see many people (in my working environment and also here in this forum) ask "strange" questions about how to perform a funny tasks using SQL.
Sometimes blame the "language" stating that is difficult to execute a relatevly easy task... etc etc.
I feel obligated to say that the most important thing is the correct database design (1st,2nd, and at least 3nd normal form must be followed,... but 4th and 5th is requird for my opinion) in order to get correct and easy results.
SQL is a language, designed to query data in a relational form and not data in a flat or hierachical architecture.
You can get the best of the language following (in the design phase) the simple but strong rules that Relational Algebra is based on (stated by Cod).
According to my experience, the meanning of SET must be clear understud in order to get the full power of the language.
I dont like cursors (its a bad approach - but sometimes required... indeed) and i try to avoid them.
More than 90% of the cases, when SQL cannot return what I expect is because of a bad design of mine than of a bad design of the SQL.
These, are just a few things that have in my mind and I would like to share them with you.
Any diassagreement is welcome.
Thank you very much for your attention.
------------
When you 've got a hammer, everything starts to look like a nail... -
I think you're preaching to the choir here...
I have run across some tasks that I think /should/ be easier in SQL. Cross-products being one of the big ones. And, there are some areas where I think SQL could stand to be a little more intuitive to write (why do we use AS for column aliases, but not for table aliases?).
But, overall, I think you're entirely correct. In my experience, most problems with SQL stem from one of four issues:
* Poor database design. Garbage in, garbage out.
* Attempting to write complex queries with only a rudimentary understanding of the language. Just like any other language, you need to become familiar with it before you can really judge its power.
* Attempting to perform row-based instead of set-based logic. I especially see this from programmers, who see queries as looping through the results.
* Attempting to put too much formatting into the results. SQL is not a reporting tool. It can do a heck of a lot of really nice things. Page numbers are not one of those things.
-
Agree with everything said - just curious about one statement..."why do we use AS for column aliases...."..far as I know it's only for readability that we use this - we can omit it from either (column & table) aliases as well as throw it in in either instance..why the general practice is to use it for columns and not for tables is definitely matter for speculation..
**ASCII stupid question, get a stupid ANSI !!!** -
Huh. I could've sworn I've gotten errors before trying to do table aliasing with the AS clause. But BOL backs you up on that. However, you do have to use AS when doing column aliasing (though I also just discovered that you can use the format column_alias = column_name in T-SQL).
-
"have to use AS for column aliasing..."...not true...do you actually get an error if you don't use "AS"?!?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Not sure what you mean...
if it is "select col1 NewCol1, col2 NewCol2 into #temp from myTable" then again there is no error on omitting the "AS" clause (say fast 10 times..
)
**ASCII stupid question, get a stupid ANSI !!!** -
I meant that you HAVE to use column aliasing, whether you use the AS keyword, or not. The AS keyword is optional - but you do have to provide a name for the column so that it can be named in target table- whether that is a temp table or a permanent table.
-
sorry - I am in a disagreeable mood today..
select au_id, au_lname, au_fname into #AS_Discussion from dbo.authors select * from #AS_Discussion drop table #AS_Discussion
..unless there's still something I haven't understood ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
The devil is in the details...
If it's a calculated column, you have to use aliasing.
So...
select au_id, au_lname, au_fname
into #AS_Discussion
from dbo.authors
select * from #AS_Discussion
drop table #AS_Discussion
works
BUT
select au_id,
au_lname + ', ' + au_fname
into #AS_Discussion
from dbo.authors
select * from #AS_Discussion
drop table #AS_Discussion
doesn't.
-
Sure..but I was addressing CK's.."you HAVE to use column aliasing, whether you use the AS keyword, or not. The AS keyword is optional - but you do have to provide a name for the column so that it can be named in target table.."..specially the "HAVE TO" part..
In a "regular" select, you have to provide aliases only if you want the target table to have different column names than the source table...
**ASCII stupid question, get a stupid ANSI !!!** -
You use AS for the same reason you should capitlize key words like SELECT, FROM, LIKE, LEN(), GETDATE(), WHERE, etc.!!!!
I am a major stickler on that! A-Type! I will change code if I see it missing because if bugs me soooooo much...
(I think SQL Server 2000 should cause your SP to error if you do not do this formatting... :laugh I wasn't born stupid - I had to study.
-
Oh I agree..I'm obsessive compulsive to the point of sickness too..I had a co-worker who (to his credit tried really hard) couldn't for the life of him be consistent with small letters and caps...so his queries would look like this..
"SelECT COl1 fROm...." - I kid you not..I would spend hours just correcting all his stuff because it would make me physically ill to look at it...
I too use "AS" as much as I use "FROM" (eg: "DELETE #AS_Discussion would work just as well as DELETE FROM...) or any other optional keywords - for readability..
However all this is not (that) pertinent to the "discussion" since what's being debated is whether "AS" is mandatory or optional...
**ASCII stupid question, get a stupid ANSI !!!** -
It is pertitent... I say it is mandatory whether that is the case or not! And SQL Server 2000 should put out a patch MAKING it Mandatory!
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply