A Fine Slice Of SQL Server
When you put NOLOCK in your query, SQL Server will:
– Read rows twice
– Skip rows altogether
– Show you changes that never actually got committed
– Let your query fail with an error
This is not a bug. This is by design.
There are reasons why you might want to use NOLOCK, but start with no and you’ll be in better shape.
Also, remember that NOLOCK really means “No, lock!”
I continue a series on window functions:
Offset functions are another class of window function in SQL Server thanks to being part of the ANSI SQL standard. Think of a window of data, stretching over some number of rows. What we want to do is take the current row and then look back (or “lag” the data) or forward (“lead”).
There are four interesting offset window functions:
LAG(),LEAD(),FIRST_VALUE(), andLAST_VALUE(). All four of these offset functions require anORDER BYclause because of the nature of these functions.LAG()andLEAD()take two parameters, one of which is required. We need to know the value to lag/lead, so that’s a mandatory parameter. In addition, we have an optional parameter which indicates how many steps forward or backward we want to look. Let’s see this in action with a query:
Click through for that query, as well as a few more and plenty of explanation.
Comments closedI continue a series on window functions in SQL Server:
The whole concept of ranking window functions is to assign some numeric ordering to a dataset. There are four ranking functions in SQL Server. Three of them are very similar to one another:
ROW_NUMBER(),RANK(),DENSE_RANK(). The fourth one,NTILE(), is the odd cousin of the family.Unlike aggregate window functions, all ranking window functions must have at least an
ORDER BYclause in theOVER()operator. The reason is that you are attempting to bring order to the chaos of your data by assigning a number based on the order in which you join.
Watch me ramble on about monotonicity and quietly admit that I learned what it was from economics, where the naming feels utterly backward (“strongly monotonic” is the “greater than or equal to” of monotonicity, whereas “weakly monotonic” is the “greater than” of monotonicity). Also, I structured this entire post so that I could get that video from The Prisoner (the good one, not the garbage one) in it.
Comments closedI have a series on window functions:
Here, we get the sum of
LineProfitbyCustomerID. BecauseSUM()is an aggregate function, we need aGROUP BYclause for all non-aggregated columns. This is an aggregate function. The full set of them in T-SQL is available here, but you’ll probably be most familiar withMIN(),MAX(),SUM(),AVG(), andCOUNT().To turn this into a window function, we slap on an
OVER()and boom! Note: “boom!” only works on SQL Server 2012 and later, so if you’re still on 2008 R2, it’s more of a fizzle than a boom.
Read on for several examples of this nature.
Comments closedErik Darling reminds us that even good things can go bad:
Look, I really like EXISTS and NOT EXISTS. I do. They solve a lot of problems.
This post isn’t a criticism of them at all, nor do I want you to stop using them. I would encourage you to use them more, probably.
But there’s some stuff you need to be aware of when you use them, whether it’s in control-flow logic, or in queries.
But do read on to see a specific type of issue you can run into with a left semi join.
Comments closedItzik Ben-Gan continues a series on table expressions:
This is the thirteenth and last installment in a series about table expressions. This month I continue the discussion I started last month about inline table-valued functions (iTVFs).
Last month I explained that when SQL Server inlines iTVFs that are queried with constants as inputs, it applies parameter embedding optimization by default. Parameter embedding means that SQL Server replaces parameter references in the query with the literal constant values from the current execution, and then the code with the constants gets optimized. This process enables simplifications that can result in more optimal query plans. This month I elaborate on the topic, covering specific cases for such simplifications such as constant folding and dynamic filtering and ordering. If you need a refresher on parameter embedding optimization, go over last month’s article as well as Paul White’s excellent article Parameter Sniffing, Embedding, and the RECOMPILE Options.
This was a really good series. If you haven’t seen the entries, set aside some time and check it out.
Comments closedErik Darling answers the long-standing question “Who is active?” with “You is active!”:
– New parameter,
@get_memory_info, that exposes memory grant information, both in two top-level scalar columns and a new XML-basedmemory_infocolumn.– Better handling of the newer CX* parallelism wait types that have been added post-2016
– A top-level implicit_transaction identifier, available in
@get_transaction_info = 1mode– Added
context_infoandoriginal_login_nametoadditional_infocollection– A number of small bug fixes
– Transition code to use spaces rather than tabs
Spaces rather than tabs? SQL should have tabs! But functional programming languages are great and they use spaces! I’m so conflicted!
Comments closedI was a bit surprised by this, because I thought the whole point of these new functions was to avoid errors like this.
This was a surprising result, for sure. Apparently the function returns an error when a cast is explicitly not permitted. But like Erik writes, that seems to go against the spirit of the function.
Comments closedKevin Wilkie deals with not-so-datey dates:
Because of the “not-so-dateness” of the data, you cannot convert non-date items into a date, at least not with the CONVERT function.
“Not-so-datey” is, of course, a technical term. Read on to see what you can use as an alternative to CONVERT().
Aaron Bertrand splits and cares about sort order:
Last year, I wrote about replacing all your CLR or custom string splitting functions with native calls to
STRING_SPLIT. As I work on a project migrating several Microsoft SQL Server instances to Linux, I am encountering one of the roadblocks I mentioned last time: the need to provide an element in the output to indicate the order of the elements in the input string. This meansSTRING_SPLITin its current form is out, because 1) it offers no such column; and, 2) the results are not guaranteed to be returned in any specific order. Are there other ways to achieve this functionality at scale and without CLR?
As Koen mentions in the comments, you can now get STRING_SPLIT with a sort parameter, but Aaron’s response is also valid: not everybody will have access to that today, so it still makes sense to understand the options.
Comments closed