SQL Design Patterns (2010)
vadimtropashko.wordpress.com
SQL Design Patterns (2010)
1–10 of 35 posts
Re: SQL Design Patterns (2010)
#2Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.
Re: SQL Design Patterns (2010)
#3Of course, trying out the first example doesn't work on SQL Server..
Re: SQL Design Patterns (2010)
#4Of course, trying out the first example doesn't work on SQL Server..
"We use Oracle syntax and write instead of ANSI SQL AS . Ditto for table expressions"
Footnote on page 3.
Re: SQL Design Patterns (2010)
#5Of course, trying out the first example doesn't work on SQL Server..
"We use Oracle syntax and write instead of ANSI SQL AS . Ditto for table expressions" Footnote on page 3.
T-SQL can handle that alias expr just fine, but the seqNum returned is 4,8,12,16,20 instead of the 1,2,3... I tried on MySQL and it works fine. I'm not sure how SQL Server is handling those cartesian joins differently
Re: SQL Design Patterns (2010)
#6Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.
distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities
Re: SQL Design Patterns (2010)
#7I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL.
You can always use Google to look up the SQL, or now ChatGPT to generate it for you.
SQL is a not-that-great language and it intentionally hides what's going on.
It is also different enough between databases that you need to pay attention.
So learning to design/think in terms of SQL is probably not worth doing.
The set of data structures that you use to model and index a dataset is worth understanding, and designing in that space is a skill worth learning.
Re: SQL Design Patterns (2010)
#8[deleted]
Re: SQL Design Patterns (2010)
#9This is rather old and there are better ways of doing most of these things now. For instance the counting example would usually be much more efficient performed using the ROW_NUMBER() window function instead of a Cartesian product. When you can remove a cross product from your process it is almost always beneficial to do so. That will often involve introducing a CTE⁰¹ which might put off some beginners²³, but it shouldn't as this sort of example is pretty simple (you aren't worrying about any recursive case).
----
[0] Because you want the ordinal of the row in the input table/view, not your output.
[1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO.
[2] In databases, like sports, CTEs can result in headaches!
[3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).
Re: SQL Design Patterns (2010)
#10SQL is beautiful in its own way. Definitely not easy to master but beautiful in how much business logic you can implement in not many lines of code.
And with SQL macros becoming a thing it is now easily possible to store patterns as reausable functions with parameters.