Why does the stuff I like cease being made

[Umfriend]

1, You aren't alone in feeling this way but guaranteed ordering without an ORDER BY defies foundational principles of database theory. Why is it annoying to add an ORDER BY? Then you are just saying what you really want.

I disagree. As *any* order is fine, why not allow for a certain ordering by default? It is annoying because of the extra line of code.

2. It won't let you use column numbers in an ORDER BY? I thought that was in the SQL standard. It is certainly a feature of most DB engines.

Ooops! I guess I tried it once and the query failed for another reason. It does indeed...

I've never heard of being able to GROUP BY column numbers. That sounds like it would make reading a query that wasn't utterly simple very difficult.

Maybe, just maybe, my memory of DB2 is wrong.

Other things I would love to see:
1. On aggregating functions, inherit the column name of the column aggregated if one column only (and that would I guess defy principles but given that you can alias it as such....
2. For SSMS to show numeric values formatted (especially MONEY). I understand the results pane is not a presentation tool but it just makes it so much easier to interpret the output while developing/testing. Sure, I can FORMAT fields but that is (a) extra code and (b) output is then text (varchar(max) perhaps?).

[cjolley]

I disagree. As *any* order is fine, why not allow for a certain ordering by default? It is annoying because of the extra line of code.

Other things I would love to see:
1. On aggregating functions, inherit the column name of the column aggregated if one column only (and that would I guess defy principles but given that you can alias it as such....
2. For SSMS to show numeric values formatted (especially MONEY). I understand the results pane is not a presentation tool but it just makes it so much easier to interpret the output while developing/testing. Sure, I can FORMAT fields but that is (a) extra code and (b) output is then text (varchar(max) perhaps?).

a, The definition of the result of a query that lacks an ORDER BY clause is an unordered set. This is not only the sql standard, it is also a reflection of the basic set theories that lead to the creation of relational databases. Getting a correct result by accident is just not good policy.

1, This could be confusing in views, but since it is pretty common to alias that way anyway I don't have much problem with it.

2, You can set the default display format for numbers and dates in the database, but it applies to ALL numbers and dates, so it wouldn't work to use a currency format for numbers unless all the number columns in the database were currency.

All database engines have their quirks. Oracle, for example, has no columnar Boolean or date data type. Dates are all datetime.
Their reasons make sense, but it does cause fairly frequent programming annoyances.

[Umfriend]

My point is that an ordered set could just be one of the possible unordered sets. If the order of the results of a query were truly random then once in a while you would expect a result set that happens to be ordered exactly as you would want it to be ordered.

It's funny that you have less of an issue with aggregate-column-column-name-inheritance as that, I would think, is really contrary to set theory.

Would you happen to know how I can set default display formats in SSMS? That'd be a great help.

Oh yeah, I am really happy with the DATE data type in SQL Server 2012 (and 2008). One quirck here though is that you can assign 0 to a datetime but not to a date field...

[cjolley]

My point is that an ordered set could just be one of the possible unordered sets. If the order of the results of a query were truly random then once in a while you would expect a result set that happens to be ordered exactly as you would want it to be ordered.

Umm wut?

It's funny that you have less of an issue with aggregate-column-column-name-inheritance as that, I would think, is really contrary to set theory.

Would you happen to know how I can set default display formats in SSMS? That'd be a great help.

Oh yeah, I am really happy with the DATE data type in SQL Server 2012 (and 2008). One quirck here though is that you can assign 0 to a datetime but not to a date field...

1, I don't think set theory addresses column naming at all.

2, I know how to do it in an Oracle db, no clue in mssql. eg alter system set nls_date_format='<whatever>';

3, Probably the date type is just a datetime with the time masked off and the 0 messes up the masking code. (total guesswork)


PS, On ordering. Oracle makes no guarantee whatsoever about ordering a group by without an order by. But you do frequently get a set that's "in order" from group by queries. When you do it's just a lucky accident caused by the plan the db engine used to get the result and the accidental order of the data on disk.
That is as it should be. It's unwise to count on it.

[Marshmallowman]

I think there was a time the order you got was the order of last index/order you did on the dataset.
Not really as relevant with modern relationatal databases.

[Umfriend]

Ah, it seems set theory does not addres column (or property) naming at all indeed, my bad. My point was that an aggregate of a property is not the same as the property aggregated (i.e. the sum of the ages of the ppl in my house is not an age in any meaningful sense). Whatever.

Wrt the first point, would you agree that a result sets' order (without an ORDER directive) is random?

[cjolley]

Wrt the first point, would you agree that a result sets' order (without an ORDER directive) is random?

The order the rows come out is caused by the plan the engine generates and the order the data comes out of the drive controller(s)
I'm not sure I would call anything with a specific cause "random".
But without an order by the order you get is certainly accidental and could accidentally be "in order."

[VJ]

My point is that an ordered set could just be one of the possible unordered sets. If the order of the results of a query were truly random then once in a while you would expect a result set that happens to be ordered exactly as you would want it to be ordered.

Often does not matter in which order the results are presented (e.g. when using IN + subquery, or other constructions). So why bother sorting the result? It adds unnecessary processing steps. And on which field would you sort? Primary key? What if it is not in the resultset?

Wrt the first point, would you agree that a result sets' order (without an ORDER directive) is random?

I would not call it random, but rather undefined. Random seems to imply to me that the order will change when running the same query over and over, in a non-deterministic way. In general, as cjolley put, the order tends to be a result of how the query is processed. So the order is in way deterministically determined, but not defined.

[cjolley]

The reason group bys caused a sorted result was that db engines used to have to sort the rows to roll up the aggregates.
They don't have to do that now.
Someone probably got their math PhD figuring out how to leave out the sorting step.


PS VJ, undefined is a good way to put it.

[Umfriend]

Often does not matter in which order the results are presented (e.g. when using IN + subquery, or other constructions). So why bother sorting the result? It adds unnecessary processing steps. And on which field would you sort? Primary key? What if it is not in the resultset?

Well, it was in the context of a GROUP BY clause so that's how you know what fields to sort on. AFAIK, a field used to sort need not be in the result set BTW.

Processing is an issue of course but I'm quite sure an optimiser would be able to find out whether the GROUP BY was part of a subquery.

*undefined* is indeed a better word.

Maybe it's time for a new clause, the GRORD BY clause

[cjolley]

GRORD BY clause

That just sounds so wrong

[Umfriend]

2, I know how to do it in an Oracle db, no clue in mssql. eg alter system set nls_date_format='<whatever>';

Yeah, that I can do in mssql as well. However, only for dateformat and as such it, AFAIK, affect input format as well, i.e., the meaning of SET @datefield = '7/9/2013' means different things based on the date format set for the server. What I would like is a way to have MONEY outputted by default as #,##0.00.

[Umfriend]

The reason group bys caused a sorted result was that db engines used to have to sort the rows to roll up the aggregates.
They don't have to do that now.
Someone probably got their math PhD figuring out how to leave out the sorting step.

Perhaps they found that it was more efficient, on average, to build temporary indexes that are sorted instead of ordering the entire result set or somesuch and chose not to output according to the index but to the order-undefined nonsorted result set.

[cjolley]

Can you create your own types in mssql? Something like "create type currency number(15, 2)" then set the nls_number_format for that type?

[Umfriend]

I don't think so. dateformat at least implies to a sort of syntax thing as it affects inputs/string translation. Number formats that I look for are pure presentation an many feel SQL has nothing to do with that (but SSMS imo does presentation in the results pane). I'll look how it would deal with e.g. postal codes and phone numbers, maybe there is something there. I don't think "nls_*" is anything in mssql?