A Fine Slice Of SQL Server
I was looking over the CHOOSE() function while editing an article at SQL Server Central. I realized there isn’t a documentation note on how many parameters can be included. I decided to test.This post looks at the tests and results.
Click through to see how many parameters Steve was able to cram in before the whole thing fell apart. This is definitely a place where Swart’s 10% Rule comes into play.
Leave a CommentLouis Davidson works with three amigos:
To get the total, there are a few ways, which I will demonstrate in the next sections on the aggregation functions,
ROLLUP,CUBE, and thenGROUPING SETS. Grouping sets are the most flexible, in that they let you do what the other two can do, but with more control. But knowing the others is important because it is how most ad hoc queries will be written.
It’s uncommon that I use GROUPING SETS() in my code, but every time I do, I’m thankful that it exists.
Jared Westover explains the difference:
After creating a SQL script and a pull request, a savvy developer left a comment on the PR because they were confused about why I was using both XACT_ABORT and TRY…CATCH. They thought that including both in one script was redundant. That got me thinking: is it true that setting XACT_ABORT ON makes TRY…CATCH redundant?
The most interesting part of this post is the cases in which you would use both in the same script.
Leave a CommentRadim Marek provides an explanation:
At least Postgres avoids flushing its own cache by reading large tables through a small ring buffer. Nevertheless the disk still perform full read. On a primary server handling production traffic is shared with everything else. When all you need is exploratory confirmation “roughly how many” or “are there any representative samples” reading the whole terabyte is too expensive.
SQL Standard comes with
TABLESAMPLEto help with that. It goes in theFROMclause right after the table name, and the rest of the query runs against the subset of pages it picks.
Read on to learn more.
Leave a CommentLouis Davidson gets back to basics:
For a while now, I really have wanted to learn the Windowing Functions in great detail. I know them well enough to make it through a lot of needs, but there are a lot of intricacies that are hard to remember. As I noted in my very first Blogging for Programmers post, writing for your own future needs is some of the best and easiest writing you will do.
When I wrote my “SQL Techniques you should know” presentation, the topic that took the longest was Window Functions. Because of their similarity to
GROUP BY, I figured this was the best place to start.
Read on as Louis works through the concept.
Comments closedDualcore DBA demonstrates how you can turn an outer join into an inner join on accident:
This post is a quick public service announcement to serve as a reminder to be careful with joins in SQL, particularly in queries with a large number of INNER and LEFT JOINs.
An INNER JOIN onto a LEFT JOIN will effectively make that LEFT JOIN an INNER JOIN.
This is a bit different from the other major case: a LEFT OUTER JOIN whose column you use in the WHERE or HAVING clauses.
Comments closedJerry Nixon shares some options:
Application developers already know what happens when one method does everything: it becomes difficult to read, test, reason over, and safely change. We use patterns like decomposition, encapsulation, and explicit dependencies because they solve those problems.
T-SQL does not give us classes, inheritance, interfaces, or polymorphism in the same way C# does, but that does not mean good software practices stop applying when logic moves into the database.
Decomposition is a good example. Breaking complex database logic into sensible, well-defined components can reduce complexity, improve readability and maintainability, and make individual pieces easier to test. These are established, respected, and proven techniques for building great software, whether the code runs in an application or inside the database.
My problem is, there are performance costs to T-SQL decomposition. Unfortunately, Jerry doesn’t cover that at all in his post, but attempts at decomposing in SQL Server often fail for exactly that reason.
Comments closedErik Darling does some date math covering multiple tables:
So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.
Click through for a clever use of an indexed view and a non-clustered columnstore index. Which, incidentally, marks one of the few times in which I’ve seen actual value in non-clustered columnstore indexes.
Comments closedAndy Brownsword gives Spark a try:
I’ve spent years shaping data with SQL Server, however after pulling at the threads of Fabric I’m opening notebooks and finding PySpark.
At first glance the difference is stark, but it’s not quite the dramatic shift it appears. If you’re not familiar, let’s look at what’s very similar, and where the true differences are.
There’s plenty of nuance in the syntax differences and behavioral differences between the platforms, but Spark SQL is just as ANSI compliant at this state as pretty much any other platform, and PySpark feels a lot like a chained quasi-functional approach to SQL because of Spark’s Scala heritage.
Comments closedReitse Eskens troubleshoots an error:
I’ve declared a variable and assigned it a value. Now, I want to check if my variable matches the regular pattern of Dutch postal codes. These codes are four numbers followed by two letters. Using a regular expression helps check validity.
My expectation was that this query would return either 1 or TRUE. In any case, a result telling me that the postal code is valid. Instead, it throws an ‘Incorrect syntax error near the keyword ‘REGEXP_LIKE’.
Click through for the screenshot and the explanation.
Comments closed