A Fine Slice Of SQL Server
This post hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement.
This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse.
You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns.
Read on for the answer. If only MERGE weren’t so riddled with problems.
Leave a CommentKathi Kellenberger takes us through the PERCENTILE_CONT window function:
I was recently playing with the analytical group of windowing functions, and I wanted to understand how they worked “under the covers.” I ran into a little logic puzzle with
PERCENTILE_CONTby trying to write a query that returned the same results using pre-2012 functionality.Given a list of ranked values, you can use the
PERCENTILE_CONTfunction to find the value at a specific percentile. For example, if you have the grades of 100 students, you can usePERCENTILE_CONTto locate the score in the middle of the list, the median, or at some other percent such as the grade at 90%. This doesn’t mean that the score was 90%; it means that the position of the score was at the 90th percentile. If there is not a value at the exact location,PERCENTILE_CONTinterpolates the answer.
I’m a bit disappointed with how poorly PERCENTILE_CONT performs against large data sets, especially if you need multiple percentiles. It’s bad enough that going into ML Services and getting percentiles with R is usually faster for me. But for datasets of less than 100K or so rows, it’s the easiest non-CLR method to get the median (with the easiest CLR method being SQL#).
The problem was that the plan was doing a table scan on the underlying table, and not using the indexes I had carefully crafted.
I had even created a second non-clustered index to try and make sure it was a proper covering index for the query.
Read on to see how Randolph solved the problem.
Comments closedItzik Ben-Gan covers some underutilized functions and function overloads in T-SQL:
TRIM is more than LTRIM(RTRIM())
SQL Server 2017 introduced support for the function TRIM. Many people, myself included, initially just assume that it’s no more than a simple shortcut to LTRIM(RTRIM(input)). However, if you check the documentation, you realize that it’s actually more powerful than that.
This article is an excellent argument in favor of reading the documentation, as all of it is in there but it’s easy to miss.
Comments closedDaniel Hutmacher shows how you can aggregate date and time data types in SQL Server:
Because the “Duration” column is a “time(3)” datatype, it doesn’t really aggregate into an average that easily.
Msg 8117, Level 16, State 1, Line 20 Operand data type time is invalid for avg operator.This may seem a little odd, as time is really a continuum just like any integer or floating-point value, right?
Read on to see how to do this and a warning from Daniel about overflowing. This sort of aggregation is a lot easier to do in R, but you can still do it in T-SQL.
Comments closedLet’s say we are interested in each column of each table: the ordinal position, the name, the data type, and whether it is nullable. In all currently supported versions of SQL Server (2012 and up), there is a handy function that allows you to get all the columns for a T-SQL query, sys.dm_exec_describe_first_result_set. This function is superior to querying the information from sys.columns, mainly because you don’t have to join to sys.types, or embed complicated logic to deal with translation; for example, CASE expressions that change -1 to max, eliminating types that have synonyms, or cutting nvarchar lengths in half – but only if they aren’t max. (You can see the type of query you end up in an earlier tip, “SQL Server Data Type Consistency.”)
This use of sys.dm_exec_describe_first_result_set() is new to me.
Slava Murygin gives us unique file names:
That is pretty common task to generate new files with a timestamp in their names.
It gives you ability to easily identify them, sort them and make them pretty unique.
However, if you have a very busy process it is possible that duplicate name will be produced and you might loose some data.
To avoid that situation I’ve came up with following solution.
It is difficult to envision this solution going wrong.
Comments closedBert Wagner reminds us that integer math is a thing:
To determine how much lumber I would need for building the new walls, I decided to write a SQL query to help with my framing calculations. I was building a 6 foot wall and wanted to put a stud every 16 inches. Easy enough to do the math on this:
SELECT (6*12)/16The output of the query above was 4, indicating the number of studs I would need for one wall section.
What’s interesting is that if we do this same equation in a calculator, we get a slightly different answer: 4.5.
Click through for Bert’s thoughts on the issue. Other languages and platforms do this as well, so it’s not unique to SQL Server, but if you’re not used to it, you might be in for a surprise.
Comments closedDaniel Hutmacher has a painful but realistic problem to solve:
Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult to injury, the banking interface won’t tell us which invoice the customer is paying for, so we’ll just decide that each payment goes towards the oldest outstanding invoice.
Our task is to calculate how many days have elapsed, for each invoice, from invoice date to payment in full.
Daniel has an excellent solution to the problem, so check it out.
Comments closedBert Wagner is on a quest to find out who moved his cheese:
Have you ever wondered who was the last person (or process) to modify a piece of data in your database?
SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. However, it doesn’t offer much information about who last modified your data.
There are a few workarounds, though they aren’t great.
Comments closed