A Fine Slice Of SQL Server
Michael J. Swart has a function:
Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.
Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.
Comments closedCathrine Wilhelmsen does some modulo division:
This week, my coworkers and I were given a fun challenge. Using any tool or language, solve FizzBuzz! Then present and explain the solution to the rest of the team. This was a fun challenge because our team is a mix of junior-to-senior developers and data professionals, working with everything from SQL to Python to C# to DAX to PowerShell. Those who had never solved FizzBuzz before got the chance to do so, while those who had already solved it got the chance to try again using a different tool or language.
Read on for Cathrine’s solution using a tally table. This classic variant of FizzBuzz is pretty easy to do in T-SQL; building the numbers table is the trickiest part.
Comments closedPaul White gets into the weeds:
This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much.
Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings.
There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. Neither of those assertions is true, but the details are interesting, as I’ll explain.
As always, Paul brings clarity to a difficult topic.
Comments closedRobert Cain reviews a rose by any other name:
In order to compact data, JSON is often used to store information within columns in databases. JSON is also useful when you want to store data, but the exact data is different on a row by row basis. Using JSON prevents the need to have a lot of extra columns that will be blank for many of the rows.
It becomes important then that a query language provides a simple method for unpacking that JSON data into useful columns. The Kusto Query Language provides that ability through the use of the
parse_jsonscalar function. In this post we’ll look at examples of how to use it to expand data stored in JSON format.
Read on to see how this relates to the todynamic function in KQL, as well as examples of how to use parse_json.
Gabi Lehner announces an upcoming bugfix:
This is a Breaking change when using window functions inside partition operator sub-query scope.
Window functions require a serialized/ordered input.
There is a bug in Azure Data Explorer logic that allows using window functions inside the partition operator when the ordering is performed outside the partition query scope like this:
Click through for what currently works as well as its future replacement.
Comments closedKenneth Fisher ran into an interesting issue:
One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).
This time around, it was creating a server role. Read on for Kenneth’s thoughts and how you can avoid accidentally creating logins for individual Active Directory users.
Comments closedThere was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.
From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.
Because the index might be large and the maintenance window small, I want to do that as fast as possible.
Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.
Comments closedTomaz Kastrun has a script for us:
Determining and checking who has access to a particular workspace in your organisation can be time consuming task. You can always retrieve the list of workspaces and access the list of all users with PowerShell cmdlet Get-PowerBIWorkspace.
The results of this cmdlet gives you a great way to get to valuable information easy and fast. And the following script can do just that.
Click through for the script, which also excludes personal workspaces. Tomaz then provides a bonus script creating a graph of how users and workspaces connect.
Comments closedJeffrey Wang wraps up a series on the EvaluateAndLog function:
This is the fourth and the last article on the EvaluateAndLog function. While DAX developers were excited about the prospect of printing the values of a portion of a DAX expression, many of the early adopters of the function were puzzled by a mysterious message they ran into from time to time: “EvaluateAndLog function is not executed due to optimization.” Another question on people’s mind is “While it’s great to be able to see the values of a sub-expression used in my calculation, is there a way for me to see the values of other expressions which are not needed to calculate the final result?” I will provide answers to both questions in this post.
Click through to understand why you might get this message and what it means.
Comments closedScenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.
Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.
Comments closed