The T-SQL Paradigm

Question

The T-SQL Paradigm

Viewing 15 posts - 106 through 120 (of 266 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Order of columns in Group By doesn't matter. Presense or absense, yes; order, no.

- 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

brownph99 SSC Enthusiast Points: 119 More actions · Answer 2

David Walker (4/3/2009)
Timothy (4/3/2009)
Are table aliases really that hard to decipher? Even if you had a query with 6 joined tables and every one of them using a single character table alias, all that's required to know which table is being used is to look in the FROM clause.
Right. And pair up the aliases with the real table names. In our head. While we're also trying to keep track of which columns are joined, and what the overall statement is doing. All of which is easier if the table names are not aliased; it's one less mental step.
Why put anyone through the extra effort to decipher, even if it's small? Why are so many people wedded to table aliases? The From statement might be "TableA Inner Join TableB on condition1 and condition2 Inner Join TableC On Condition 3 and Condition4 Left Outer Join TableD on Condition5" broken up on several lines, of course. The From statement can be a long statement.
They are unnecessary and they make the code much harder, or slightly harder, to read. Either way.
David

This. Table Aliases have been a crusade of mine since I first started maintaining other people's code. I've had to maintain way too much legacy code that looked like FROM table1 A, table2 B, table3 C. This type of code looks ok in books, and for little two table querys, but in the real world just makes life miserable.

wldhrs SSC Eights! Points: 921 More actions · Answer 3

brownph99 (4/6/2009)This type of code looks ok in books, and for little two table querys, but in the real world just makes life miserable.

ROFL

Completely agree.

For simple code I use

SELECT [Description].[Id]

FROM [tempdb].[dbo].[Description] AS [Description]

Yeah, all you minimalist keystroke jockeys wont like that, but I do a lot of programmatic generation of code, and I've got no fundamental issue with intellisense or context sensitive assistance, and it's accurate and readable.

So there.

For more complex structures I like

SELECT [Des].[Id]

FROM [tempdb].[dbo].[Description] AS [Des]

etc

Yep, first three chars of the table name.

This assumes you've had a hand in the design of the naming conventions so they are lovely and unique and hierarchical etc.

No?

Well then, synonyms.

Don't knock 'em till you use 'em, they can impose some serious order onto ontologically parlous history.

For really complex stuff I go for six chars. Not a good exampl, but this is on the fly, and its aerodynamics are suffering, perhaps I should be using a dragonfly instead.

SELECT [TraAuc].[Id]

FROM [tempdb].[dbo].[TransactionAuckland] AS [TraAuc]

[tempdb].[dbo].[TransactionWellington] AS [TraWel]

etc.

The basis of this is: Stop complaining about the syntax and go and learn it thoroughly for every language you need to use, then write code that a lot of people can follow.

But not idiot-proof code please, make it idiot-proof and only an idiot will want to use it. :Whistling:

I think I've forgotten more languages than I can write in now, and my approach now is USE THE RULES OF THUMB, the compiler, or intellisense or whatever, will correct me when I muddle the syntax between languages, but a good toolkit of rules of thumb, applied as appropriate will ensure that regardless of my saying

How are you?

or

Comment allez vous?

or

I think, ??????

then the people (and the compilers) who have learned the syntax will give me a reasonable answer.

Anyone want to start a new thread on naming? Always a good one for approaching "Son of Thread" dimensions.

Peter Edmunds ex-Geek

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Heh... brackets... they sure did take a shortcut there. It would be nice if the automatic code generators didn't put those in unless actually required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Steve Jones - Editor (4/6/2009)
Jeff Moden (4/6/2009)
Heh... no, I get it. But there are still rules of thumb that absolutely must be followed.
There are, but unfortunately we don't have enough good examples, and people spreading the word. That's why we need the Mr Moden's RBAR Avoidance Manual written!

Heh... thanks for the vote of confidence, Steve. Actually, the Intro, Chapter 1, and Chapter 2 have been submitted. Starting work on Chapter 3 tomorrow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wldhrs SSC Eights! Points: 921 More actions · Answer 6

Jeff Moden (4/6/2009)
Heh... brackets... they sure did take a shortcut there. It would be nice if the automatic code generators didn't put those in unless actually required.

I disagree with you on that Jeff, I'd rather have 'em be there and not be needed than write a bunch of cross-product rules that occasionally missed the times they were needed.

Comes back to what I said about not having much time for minimalists, {{{ versus BEGIN BEGIN BEGIN if you will.

Any language we write in, is an extension-subset of a natural language, see my post about half way through this thread. Quibbling about the syntax, if it's aiding clarity is akin to saying it's not sporting to ...oh... say.. shoot a deer with your eyes open.

From the tenor of all your posts that I've browsed so far, I'd be rather surprised at you disagreeing with this.

Peter Edmunds ex-Geek

wldhrs SSC Eights! Points: 921 More actions · Answer 7

Just been fiddling with my profile, changed the nickname from "peter" to "wldhrs" which is the one I usually use, and put my name in as the signature.

Added an avatar too.

Apologies if that confused things mid-thread.

Peter Edmunds ex-Geek

Jason Miller-476791 SSCommitted Points: 1540 More actions · Answer 8

Michael Valentine Jones (4/6/2009)
YSLGuru (4/6/2009)
Jason Miller (4/6/2009)
GSquared (4/3/2009)
For example, why doesn't this work:
select Col1, count(*)
from dbo.MyTable;
And the follow-up where,
-SNIP-
I don't see how the example you gave would give you different rows.
Actually the 2 Queries may not prodcue the same results. If one of the 2 columns is a subset or superset of the other then chanaging the order of the Group By will change the both the number of rows and the values listed in each row even though the total for all rows in each column (with an aggregate) will be the same for both queries. For example in an apartment complex you may have several types of units. If you wanted a count of these and you grouped by Unit Type then Unit you would get a different row level result then if you grouped by Unit then Unit type.
Other than the order of the result set, and assuming you are not using CUBE, ROLLUP, or outer joins, can you provide an example with data where they would be different?

GSquared (4/6/2009)
Order of columns in Group By doesn't matter. Presense or absense, yes; order, no.

If I'm not mistaken, GSquared and Mr. Jones are agreeing with me that the order of the items in our grouping isn't relevant to the end result, sans ORDER BY, CUBE, ROLLUP.... Which I was not considering for this simple idea.

Thank-you, I just couldn't get past the idea that the order of the grouping items would make different result sets. I appreciate your time and effort at such a seemingly trivial exercise.

Honor Super Omnia-
Jason Miller

YSLGuru SSC-Insane Points: 21661 More actions · Answer 9

Jason Miller (4/7/2009)
Michael Valentine Jones (4/6/2009)
YSLGuru (4/6/2009)
Jason Miller (4/6/2009)
GSquared (4/3/2009)
For example, why doesn't this work:
select Col1, count(*)
from dbo.MyTable;
And the follow-up where,
-SNIP-
I don't see how the example you gave would give you different rows.
Actually the 2 Queries may not prodcue the same results. If one of the 2 columns is a subset or superset of the other then chanaging the order of the Group By will change the both the number of rows and the values listed in each row even though the total for all rows in each column (with an aggregate) will be the same for both queries. For example in an apartment complex you may have several types of units. If you wanted a count of these and you grouped by Unit Type then Unit you would get a different row level result then if you grouped by Unit then Unit type.
Other than the order of the result set, and assuming you are not using CUBE, ROLLUP, or outer joins, can you provide an example with data where they would be different?
GSquared (4/6/2009)
Order of columns in Group By doesn't matter. Presense or absense, yes; order, no.
If I'm not mistaken, GSquared and Mr. Jones are agreeing with me that the order of the items in our grouping isn't relevant to the end result, sans ORDER BY, CUBE, ROLLUP.... Which I was not considering for this simple idea.
Thank-you, I just couldn't get past the idea that the order of the grouping items would make different result sets. I appreciate your time and effort at such a seemingly trivial exercise.

I'm going from memeory and it has been a while back but we ran into a problem where the order of the grouping did matter and it very well may have included ROLLUp or CUBE, I just don;t recall. I do remember that it ended up being one of those things that you couldn;t see the forest for the trees and we wasted a lot of time on it. If the consenses is that the order doesn't matter when not using ROLLUP or CUBE then I will admit to being wrong and stand corrected. The Unit & Unit Type was just a quiock example I threw in from memory because they were part of the problem we dealt with. I will also add that we were using T-SQL with a reporting application and it may be that the Grouping Order I recall was more to do with how that reporting tool showed the data then how T-SQL works.

Kindest Regards,

Just say No to Facebook!

Jason Miller-476791 SSCommitted Points: 1540 More actions · Answer 10

YSLGuru (4/7/2009)
I'm going from memeory and it has been a while back but we ran into a problem where the order of the grouping did matter and it very well may have included ROLLUp or CUBE
I will also add that we were using T-SQL with a reporting application and it may be that the Grouping Order I recall was more to do with how that reporting tool showed the data then how T-SQL works.

I wasn't trying to make a federal case out of this. Trust me, I'm sure I'll post a lot more crap with mistakes in it than your average bear...

BTW- I would tend to agree with the reporting tool aspect. We've had some issues with CE providing proper results.

Honor Super Omnia-
Jason Miller

Mike Brockington SSC Eights! Points: 874 More actions · Answer 11

While some languages are so terse that they look like a hex dump, SQL is actually one of the more verbose, yet it still manages to be one of the very hardest to read. In my opinion. Particularly once you get into the realm of joins and embedded queries.

Throw away your pocket calculators; visit www.calcResult.com

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

wldhrs (4/6/2009)
Jeff Moden (4/6/2009)
Heh... brackets... they sure did take a shortcut there. It would be nice if the automatic code generators didn't put those in unless actually required.
I disagree with you on that Jeff, I'd rather have 'em be there and not be needed than write a bunch of cross-product rules that occasionally missed the times they were needed.
Comes back to what I said about not having much time for minimalists, {{{ versus BEGIN BEGIN BEGIN if you will.
Any language we write in, is an extension-subset of a natural language, see my post about half way through this thread. Quibbling about the syntax, if it's aiding clarity is akin to saying it's not sporting to ...oh... say.. shoot a deer with your eyes open.
From the tenor of all your posts that I've browsed so far, I'd be rather surprised at you disagreeing with this.

That's all part of what I'm talking about, actually. If you follow best practices for naming conventions, you won't ever need the brackets. I don't consider myself to be a "minimalist" especially since I advocate NOT following ANSI standards for large database batch processing... I just don't like what I believe to be unneccessary clutter. Other people love the darned things and that's fine for them... just don't want anyone forcing such rules on me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 13

mike brockington (4/7/2009)
While some languages are so terse that they look like a hex dump, SQL is actually one of the more verbose, yet it still manages to be one of the very hardest to read. In my opinion. Particularly once you get into the realm of joins and embedded queries.

Whereas, for me, I read SQL very easily. Often have to clean up the format to make it the way I like it, but once I've done that, I can often read it easily enough to performance tune queries without even having the tables, etc., to test it on. Not always, but quite often.

Different folks, different results. Life is a nondeterministic process.

- 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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 14

I think T-SQL is relatively easy to read as well, once it's formatted the way I see it.

At least as easy to read as some of the C++ I've seen,

Jason Miller-476791 SSCommitted Points: 1540 More actions · Answer 15

Jeff Moden (4/7/2009)
Other people love the darned things and that's fine for them... just don't want anyone forcing such rules on me.

I'm stepping out on a limb here....

Didn't you earlier state that there are some rules of thumb that you force others to work with / through?

Honor Super Omnia-
Jason Miller