A Fine Slice Of SQL Server
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 closedWe received some support cases when customers encounter the error below while trying to add an AAD security group to their Azure SQL Database or Azure SQL managed instance. In this blog article, we will be listing a few points to be checked to troubleshoot this error and can help you to identify the cause.
Msg 33130, Level 16, State 1, Line 1
Principal ‘XXXXXX’ could not be found or this principal type is not supported.
Read on for several reasons why things might be going wrong.
Comments closedI take a look back at the past and forward to the future:
PolyBase used to connect to Azure Blob Storage. Specifically, you could use the
wasbs://protocol and connect to Azure Blob Storage over WebHDFS. Here’s an example of an external data source which would work for SQL Server 2016 through 2019:
SQL Server 2022 changes its mechanisms around Azure Blob Storage a little bit, though I think the changes are sensible.
Comments closedEtienne Lopes takes us through cell-level encryption:
Securing data has always been important but as time goes by, more and more data is available all around us, some of it is considered sensitive data and it becomes a major concern to protect it somehow, in fact in certain cases it is legally mandatory to comply with certain regulations (like GDPR). SQL Server offers a few options regarding data protection (either by means of encryption or obfuscation), cell level encryption being one of them.
In this post I’ll explain what is cell level encryption along with its use cases and I’ll use a thorough demo to show how to implement it in a database and how it works
Despite Always Encrypted being available, I’m more apt to use column-level/cell-level encryption because it’s easier to implement. Well, that and the lack of Always Encrypted in Azure Synapse Analytics.
Comments closedPeriodic index analysis for SQL Server typically involves tasks such as checking for missing/unused/overlapping indexes, checking for heaps that maybe should have been designed with a clustered index, analyzing ROW/PAGE compression, etc. There are numerous DMVs that you can use as a starting point for those tasks. There’s also some good open source tools and scripts that members of the SQL Server community have created and shared. One task that I don’t recall ever seeing was an analysis of INCLUDE columns for nonclustered indexes. What I really wanted to do was to find INCLUDE columns that were never being used, and remove them from index definitions.
Click through to see what Dave learned, as well as the repo containing several useful scripts.
Comments closed