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 CommentSebastiao Pereira finds an equilibrium solution:
Game theory is the study of how people behave in social and economic interactions, and how they make decisions in these settings. Is it possible to create this logic in SQL Server without the use of external tools?
Click through for a modern spin on an ancient solution.
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 CommentJoe Obbish digs into some failing code:
I’m the kind of guy who logs all of the errors that occur within SQL Server, so it was easy to notice when some unusual code that we didn’t write was throwing errors. The code was executed under a client app name of “DMVCollector” which sounds kind of sinister if you ask me.
Read on for a code review of the snippet Joe found.
Comments closedErik Darling doesn’t know what time it is:
Or maybe you just started a new job where they cared about time zones and then your other jobs didn’t. So any, all kinds of crazy things can happen. But when you’re working with time zones, especially if you have dates that are not time zone oriented in any way, like for example, this one, this is just a date time too.
And getting used to the functions and how they work and behave can be a little tricky at first. It can be a little mind numbing. So if we run this query, what I want to show you is just we start off with a date time too that has no time zone affiliated with it.
In November, Erik will be right about Eastern time being UTC-5, as that’s Eastern Standard Time. Or maybe Erik is secretly in Indiana, in one of the counties that stays on Eastern Standard Time year-round. Hopefully I didn’t secretly dox him here.
Comments closedLouis 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 closedSebastiao Pereira uses Romberg’s method:
Romberg’s method is a powerful technique in numerical analysis to estimate the value of a definite integral, using the trapezoidal rule logical and applying the Richardson extrapolation to significantly improved accuracy. Is it possible to have this method in SQL Server without using external tools?
Click through for the fun.
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 closedHugo Kornelis digs into an execution plan:
The OVER clause, that can be added to aggregate functions to turn them into window aggregates, can come with or without a frame specification, in the form of an ORDER BY clause, plus an explicit or implied ROWS or RANGE clause. If there is no frame specification, then every row in a partition, or window, can see all other rows in the same partition or window for the purpose of the aggregation.
In plansplaining part 6, I looked at this specific form of window aggregation, and explained in detail all the steps that the execution plan takes to compute the aggregation result and add it to each of the rows, for every row in each window.
Click through for another look at the plan.
Comments closed