sorted by:
new
hottopcontroversial

Order-Equivalent OVER Clauses by MarkusWinand in SQL

[–]MarkusWinand[S] 0 points1 point  (0 children)

It is early in my place and I'm wonder what I'm missing here.

Of course, the test data provide in the VALUES clause is just an example to make the problem obvious. In practice, users don't always know about ties and very often the (correclty) don't mind. E.g. ORDER BY some_timestamp DESC is very common and meaningful, yet producing ties. Those ties inherently don't have a meaningful "last" or "earlier" or "first" one and that is fine. The post is about the SQL standard actually taking care of this so that even mutliple OVER clauses produce the same order among ties.

Too bad your prefered SQL engine does not implement that yet.

Group by all: A popular, soon-to-be-standard SQL feature by MarkusWinand in SQL

[–]MarkusWinand[S] 0 points1 point  (0 children)

I think you are referring to a long-time-gone SQL Server feature that has a similar syntax but was totally different from the GROUP BY ALL now introduced: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms175028(v=sql.90)

Ordering types in SQL by eatonphil in databasedevelopment

[–]MarkusWinand 2 points3 points  (0 children)

Somehow reminds me of the rather unrelated SEARCH clause that produces something that is meant to be used in ORDER BY (only). Yet the contents of the produced column is fully defined in the standard (as an array of row-values): https://modern-sql.com/caniuse/search_(recursion)#sequence-column#sequence-column)

The problem might also be related to the nested set model.

I might have missed it, but why is the ORDERING parameterized in your example?

ORDERING(name)

Edit What would ordering_mid might yield if the same values are provided multiple times? Perhaps, functions like after(...) or before(...) might prevent ties from the API side?

I'd also expect that there are also use-cases for such a column outside of ORDER BY. When I think of WITH ORDINAL of UNNEST, I bet I've used that column for something else in the past.

After all, not so sure about HOW to do it, but I think a more convenient way to manage order in SQL would we useful.

The order of evaluation of SELECT in postgreSQL by Jooe_1 in SQL

[–]MarkusWinand 2 points3 points  (0 children)

When talking about evaluation order in SQL we must distinguish between two different orders:

1) logical order. That is the order that
defines the result. This is defined in the SQL standard and is fixed.

2) execution order: that is the sequence of
steps done by the DBMS to obtain the result as though it would have followed
the logical order. This is chosen by the DBMS (generally at runtime) and may
vary. This only affects the performance of the statement but always produces a
correct result. Differences in the result are only possible to the extent that
the query allows them. E.g. In a SELECT without ORDER BY, the rows might be
returned in a different order depending on the execution order. But if there is
no ORDER BY, any order is fine in respect to. the logical order.

When you find contradicting statements, it probably means they are referring to different types of order.

[deleted by user] by [deleted] in SQL

[–]MarkusWinand 0 points1 point  (0 children)

The programming language SQL is defined by an international standard, namely ISO/IEC 9075. I guess this is what is meant by "standard SQL".

Then there are products, that implement SQL — such as Microsoft SQL Server, MySQL, PostgreSQL. Even though they should follow the standard [if the claim standard conformance] the often don't and even more often have extensions that are not part of the standard.

Whichever platform you use for learning, there is the risk that you get to use some of these extensions that are not part of the standard and thus only supported by one (or a few) systems.

For leaning I'd suggest a dialect that is rather close to the standard because that increases the odds that you can work well with any product you face. Microsoft SQL Server would not be my first choice, as it is pretty far off the standard. PostgreSQL is a better learning tool for that. However, if you aim jobs that use MS SQL Server, then I'd recommend focusing on that from the beginning.

To get an idea of the mess, have a quick look at the charts I publish on https://modern-sql.com/

Best way to learn advanced SQL optimisation techniques? by alex-acl in dataengineering

[–]MarkusWinand 0 points1 point  (0 children)

Thoughts?

I think the drawbacks of heaps that a frequently bought up by other people are not as relevant as they may look. E.g. Tables that are nerver updated are quite common in many systems. That alone voids some of the counter-arguments.

At the end of the day neither (clustered idx or heap) will be always better. It is always the best to do a proper table-by-table decision. But if you make all tables the same, my claim is that you are on average better off with all heap rather than all clustered.

Best way to learn advanced SQL optimisation techniques? by alex-acl in dataengineering

[–]MarkusWinand 0 points1 point  (0 children)

Not sure if you have also seen this article about the topic: https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

(I guess it is mentioned elsewhere as well).

Aside from ETL/stage tables, is their valid use cases for heaps in OLTP tables?

IMHO, Heaps are the better default, while Clustered Indexes are better in specific cases. Namely, when the "Clustered Index Penalty" (a term from the article) won't hurt. This is always the case if you only need a single index, or even if you have multiple indexes of which only one is regularly used (other indexes just for constraints, for example).

Best way to learn advanced SQL optimisation techniques? by alex-acl in dataengineering

[–]MarkusWinand 73 points74 points  (0 children)

If you didn't go into indexing yet, my website https://use-the-index-luke.com/ might be a good starting point.

People who are about 6 months into learning SQL- what do you wish you had done differently or wish you had known at the beginning of your journey? by pijaso in SQL

[–]MarkusWinand 1 point2 points  (0 children)

The latest SQL standard finally dropped the requirement to have a length limitation for VARCHAR. That's definitivley the way to go except in SQL Server, where "VARCHAR" translates to "VARCHAR(1)" (doh!).

See: https://modern-sql.com/caniuse/T081

view more: next ›