Press "Enter" to skip to content

Curated SQL Posts

Including Database Name in Extended Events

Aaron Bertrand has a change of heart:

In my previous tip about SQL Server Extended Events, I discussed the trade-offs involved with choosing to store the database name instead of looking up the name via database_id later. At the time, we decided to use the latter approach since we are generally not concerned about the edge case where DDL is performed against a database that is then dropped before collection occurs.

Something else that has since come up: we want to filter out activity against a set of databases matching a specific name pattern since the DDL activity there is system-generated and does not need to be audited. We quickly learned that you have to be careful with how you use the database name in an Extended Events predicate.

Click through to understand the problem as well as the solution.

Comments closed

Saving Money on Backups to Azure Blob Storage

John McCormack has a few tips for saving some cash:

You have 5 databases on a SQL Server Instance. You take daily full backups of each database on your instance. You also take log backups every 15 minutes as each database is in full recovery mode. This means in 1 week, you will have 35 full backups and 3,360 transaction log backups. This multiplies to 1,820 full and 174,720 t-log backups over 52 weeks. Multiply this for 7 years or more and the costs can get very expensive.

Click through to see how you can save a considerable amount with a bit of planning.

Comments closed

Change Tracking with Temporal Tables

Erik Darling continues a line of thought:

Temporal Tables are different, because it will add new, and drop removed columns from the history table as they change in the base table. It’ll also push data type changes across. But the history table will not have the various permutations tracked. It just mirrors current state.

The one thing I do want to mention is that the column Erik calls JunkDate isn’t junk at all in its proper context—it’s the row end date for a Kimball-style dimension. I get that it isn’t useful in the “type 1” table, but it’s quite useful in the “type 2” table and the sorts of queries temporal tables were expected to solve use the row start date and the row end date together to give you an idea of whether the version of the row you’re seeing is the current version or not.

Comments closed

Elasticsearch and SSPL

Vicky Brasseur looks at an announcement:

In a play to convert users of their open source projects into paying customers, today Elastic announced that they are changing the license of both Elasticsearch and Kibana from the open source Apache v2 license to Server Side Public License (SSPL). If your organisation uses the open source versions of either Elasticsearch or Kibana in its products or projects, it is now at risk of being forced to release its intellectual property under terms dictated by another.

Click through to understand the details. I’d imagine that if Elastic goes through with this, people would fork the last pre-SSPL version of their product sets and create a community spin-off, similar to MariaDB spinning off from MySQL.

Comments closed

Calculations in Power BI Streaming Datasets

Reza Rad has a workaround for us:

If you use a streaming dataset in Power BI, you cannot download the Power BI file, and you cannot open it using Power BI Desktop. This means that you are limited not to use calculations in a streaming dataset. However, there is a small trick which you can use and can be helpful. I will show you that in this article and video.

Click through for the article, which includes the video.

Comments closed

Updates to AzureR

Hong Ooi has some updates for us:

This is an update on what’s been happening with the AzureR suite of packages. First, you may have noticed that just before the holiday season, the packages were updated on CRAN to change their maintainer email to a non-Microsoft address. This is because I’ve left Microsoft for a role at Westpac bank here in Australia; while I’m sad to be leaving, I do intend to continue maintaining and updating the packages.

To that end, here are the changes that have recently been submitted to CRAN, or will be shortly:

Read on for the changes. This includes a new package to work with Cosmos DB from R.

Comments closed

Answering NiFi Questions

Pierre Villard has a few answers to questions about Apache NiFi:

Over the last few weeks, I delivered four live NiFi demo sessions, showing how to use NiFi connectors and processors to connect to various systems, with 1000 attendees in different geographic regions. I want to thank you all for joining and attending these events! Interactive demo sessions and live Q&A are what we all need these days when working remotely from home is now a norm.  If you have not seen my live demo session, you can catch up by watching it here

I received hundreds of questions during these events, and my colleagues and I tried to answer as many as we could. As promised, here are my answers to some of the most frequently asked questions. 

Click through for the questions and answers.

Comments closed

Build Your Own Number Generator

Itzik Ben-Gan has part one of solutions to a challenge:

Last month I posted a challenge to create an efficient number series generator. The responses were overwhelming. There were many brilliant ideas and suggestions, with lots of applications well beyond this particular challenge. It made me realize how great it is to be part of a community, and that amazing things can be achieved when a group of smart people joins forces. Thanks Alan Burstein, Joe Obbish, Adam Machanic, Christopher Ford, Jeff Moden, Charlie, NoamGr, Kamil Kosno, Dave Mason and John Number2 for sharing your ideas and comments.

Initially I thought of writing just one article to summarize the ideas people submitted, but there were too many. So I’ll split the coverage to several articles. This month I’ll focus primarily on Charlie’s and Alan Burstein’s suggested improvements to the two original solutions that I posted last month in the form of the inline TVFs called dbo.GetNumsItzikBatch and dbo.GetNumsItzik. I’ll name the improved versions dbo.GetNumsAlanCharlieItzikBatch and dbo.GetNumsAlanCharlieItzik, respectively.

One of the best parts about this happening in public is that there is a real benefit to having multiple people look over a problem, and then waves of people refining those solutions over time.

Comments closed

Quick Refresh of Excel files in Power BI

Gilbert Quevauvilliers needs things to be faster:

I was recently getting an error when refreshing an Excel file that was stored in SharePoint online.

This got me thinking what the issue is and is there a way to improve it and YES there is as detailed below.

When I investigated this further, I found that my 17kb Excel file was using at least 616kb of data when being refreshed in Power BI Desktop. Whilst this is a relatively small amount this is 36x larger than the file size. This led me to believe that it is possibly being read multiple times, but some other things might be going on!

Spoilers: there were things going on.

Comments closed

Creating a Hyperlink in Excel using Powershell

Mikey Bronowski continues a series on generating Excel documents with Powershell:

Last week I have mentioned the new functions waiting to be included into the module. This week I would like to write about another set of functions:

Add-ExcelHyperlink
Get-ExcelHyperlink
Remove-ExcelHyperlink

Those are fresh too and were inspired by a query I have got from Garry Bargsley (blog|twitter) who needed adding hyperlinks to the existing cells without using HYPERLINK() Excel function.

Read on to see how.

Comments closed