A Fine Slice Of SQL Server
Joe 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.
Leave a CommentErik 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.
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.
Leave a CommentSebastiao 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.
Leave a CommentDualcore 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.
Leave a CommentHugo 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 closedNOLOCK is bad and you probably shouldn’t use it, but every time I mention that publicly, the pushback just keeps coming. I don’t know why people so firmly believe that their situation couldn’t possibly be affected by bad/random data from NOLOCK.
Today’s misconception comes from a LinkedIn commenter telling me it’s safe to use if you’re doing index seeks. Hoo boy.
Click through for the proof.
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 closedSebastiao Pereira implements a formula:
Runge-Kutta method it is an effective numerical interactive technique used to solve ordinary differential equations, used in physics, engineering, aerospace, ballistics, circuit simulation, epidemiology, chemical kinetics, games, and others. Is it possible to create in SQL Server without the use of external tools?
Click through for the solution.
Comments closed