A Fine Slice Of SQL Server
Steve Jones shows off a good use case for window functions:
Recently I was looking at some data and wanted to analyze it by month. I have a goal that is set for each day and then an actual value. I wanted to know how I was tracking against the goal, as a running total. If my goal is 10 a day, then I ought to actually get to 10 the first day, 20 for the second day (10 + 10), etc.
Read on to see how Steve solved the problem.
Comments closedMichael Bourgon notices a difference between Azure SQL DB and Managed Instance/box product:
I was trying to get some Xquery parsing working, and ran into a weird error:
"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"Weird. Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.
Read on for the solution.
Comments closedTomaz Kastrun explains how to combine data in two languages:
Merge statement in R language is a powerful, simple, straightforward method for joining data frames. Nevertheless, it also serves with some neat features that give R users fast data wrangling.
I will be comparing this feature with T-SQL language, to show the simplicity of the merge method.
Click through for the comparison.
Comments closedTom Zika and I are on opposite sides:
So I’ll take it one step further.
I’ll never use semicolons unless I have to.Tools like Redgate’s SQL Prompt can add semicolons automatically, but I still won’t do it.
My quick thoughts:
And at the end of the day, consistency and readability are the most important things…though I’ll fight for my aesthetics like I’m the third monkey in line for Noah’s Ark and brother, it’s starting to rain.
Comments closedItzik Ben-Gan has an interesting challenge for us:
The challenge is as follows:
Assuming partitioning based on the column grp and ordering based on the column ord, compute sequential row numbers starting with 1 within each consecutive group of rows with the same value in the val column. Following is the desired result for the given small set of sample data:
Click through for an example of this, a pair of “classic” solutions, and Itzik’s solution.
Comments closedWhen doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.
You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.
Read on for the why. This is excellent advice.
Comments closedAaron Bertrand has a two-parter. Part one sets up the problem:
We often build logging or other insert-only tables where we store large strings like URLs, host names, or error messages. It’s usually not until the table has terabytes of data that we realize there might have been a better way. If we are logging traffic or exceptions for our own application, it’s likely that we record the same URL, host name, or error message on millions of rows. What if we only had to write that URL or host name or message text once, the first time we saw it? In this tip, I want to share one idea for abstracting away recurring values, reducing storage, and making search queries faster (especially those with wildcards) without requiring immediate changes in the application layer.
Part two maximizes the savings:
In my previous tip, I showed how we can make a growing logging table leaner by moving large, repeating strings to their own dimension tables. The solution there involved an AFTER INSERT trigger and assumed that we could change the applications to recognize the new table structure in relatively short order.
Check out both posts for more details. If you’re confused about my calling this 6NF and Aaron mentioning dimension tables, the answer is that he’s talking about the end result and I’m describing the process.
Comments closedTom Zika digs into a query hint:
Like Dwarves of Moria, I delved too greedily and too deep. I found the answer in a Plan Caching and Recompilation in SQL Server 2012 whitepaper before trying it out on my own. Relevant excerpt:
KEEP PLAN
The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used.
But since it doesn’t show the proof, I decided to test it out anyway.
And it’s a good thing, too, as it turns out this isn’t quite how things work anymore.
Comments closedReitse Eskens looks at a couple of useful window operators:
I was teaching a class today on more advanced SQL queries and we were discussing if you could use preceding and following in a single windowing function.
Windowing Functions
If you’re not familiar with windowing functions, they’re really cool. You can perform all kinds of trickery and magic with them. Let’s start with a simple example.
The dataset I’m using has flight data from the early 2000’s.
Read on for the example and for the answer to Reitse’s question.
Comments closedChad Callihan looks at synonyms in SQL Server:
Are you familiar with synonyms as they relate to SQL Server? I haven’t seen them used too much out in the wild but understand they can have benefits. Let’s take a look at what synonyms are in SQL Server and some common reasons for implementing them.
My recollection is that synonyms have some limitations which make them not as useful as they’d first appear. But the bigger reason I think we don’t see synonyms used very often is that they obscure information and make it tougher to understand what’s really happening. In that respect, it’s a bit similar to a trigger: useful but sometimes painful to debug because it obscures relevant information.
Comments closed