A Fine Slice Of SQL Server
I 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 closedChris Webb wants to know the lore:
In the last post in this series I showed how to get the status of an individual Power BI dataset refresh. In this post I’ll show how to get the history of a dataset’s refreshes and save it to a file in OneDrive for Business – useful if you want to do some analysis on refresh performance or the causes of failures.
This is one of the most straightforward things to do with the Power BI Enhanced Refresh API , especially once you’ve already got a custom connector created in the way I’ve shown in the previous posts in this series. You just need to add a new Action to your custom connector, fill in the General section:
Click through to see what to do.
Comments closedChris Bailiss dives into some join problems in Power BI against Snowflake and SQL Server:
Power BI generates SQL that performs very poorly against Snowflake (and possibly other database platforms) when joining nullable columns, e.g. in the “Merge Queries” action in the Power BI query editor.
Specifically, the SQL generated prevents the use of the “hash join” physical join algorithm that has been one of the main algorithms used for decades by database engines when executing joins between large tables. This forces database engines to fall-back to using very inefficient physical joins (e.g. loop/cartesian join) that can perform orders of magnitude slower.
The reason why Power BI generates different SQL for non-nullable for nullable columns in a join is explained in the “Problem Part 2” section below.
Read the whole thing—and there’s a lot of good information in there.
Comments closedImke Feldmann starts a new series:
If you want to traverse organisational hierarchies, walk through nested folder structures or create nested Power BI metrics from an Excel table for example, you need some sort of recursive logic. In this article I will introduce you to the basic method of recursive flows in Power Automate. In upcoming articles I will share some of the flows for the above mentioned use cases.
Read on for an intuitive understanding of what recursion is as well as how you can implement it in a Power Automate flow.
Comments closed