Press "Enter" to skip to content

Author: Kevin Feasel

Inferring Data Types in SQL Server Tables

Published 2024-04-05 by Kevin Feasel

Emanuele Meazzo tidies up the database:

Do you know the feeling when someone gives you a flat file to import, without any schema information? You have to dump it in your database as-is, then spend precious time figuring out the data type for each column
Let me help you with that, as I’m helping myself too.

Click through for the scenario, as well as a link to the script Emanuele has created. It looks like the script doesn’t try to estimate string lengths, instead focusing on types alone.

Comments closed

A Review of Powerops

Published 2024-04-05 by Kevin Feasel

Nikola Ilic reviews a new tool:

In the last 2-3 months, I had an opportunity to try and test the new external tool, called Powerops. This is an external tool developed by Truviz, the company you might already know as the creators of various Power BI “goodies”.

Now, you are probably expecting “another Power BI external tool that solves a specific challenge”…I’ll tell you straight away – you can’t be more wrong! Powerops is a mix of various existing external tools – think of DAX Studio, Tabular Editor, ALM Toolkit, Measure Killer, etc. – but it’s also more than a simple sum of the features of the aforementioned tools.

Read on for the full review. Pricing is free for the first two months, and then either buy a license or you can upload up to two files per month and remain in the free tier.

Comments closed

Infrastructure as Code in GitHub

Published 2024-04-04 by Kevin Feasel

I have a new video:

In this video, we look at how to perform Infrastructure as Code in GitHub. We take a Bicep script and generate new Azure resources using it and GitHub Actions.

The video includes a very brief primer on Azure Resource Manager (ARM) and Bicep, and then gets into how you can use GitHub Actions to keep your Azure resources configured the way you expect.

Comments closed

Working with Query Store Hints

Published 2024-04-04 by Kevin Feasel

Etienne Lopes shares some thoughts around a new feature in SQL Server 2022:

Last December I got lucky since after upgrading from SQL Server 2012 to 2022, one of my clients had a process running in a particular database that went from around 10 minutes to around 10 hours!

You might be thinking: “It got 60 times worse, where’s the luck in that?”

Well, it presented me with a great opportunity to try a new feature in SQL Server 2022 called “Query Store Hints” (in a real production environment) and confirm the fabulous results that can be obtained by using it, with no effort at all. I thought it could be worth to share this experience and that’s what I’ll present in this post 

Click through for more details on the problem and solution.

Comments closed

Building a Powershell Transcript

Published 2024-04-04 by Kevin Feasel

Rod Edwards saves the transcript:

I have a love for Powershell, it makes my life as a SQL Server tech so much easier when I have to either treat my instances as a herd cattle, rather than separate bovine entities, or have them interact with other technologies as part of a larger task… ie… play with the other farm animals if you will.

Enough of the animal metaphors.

We write Powershell, we run powershell, we swear when our powershell does not do what we would like it to. To this end, we need to log the output somewhere, particularly when our scripts are not going to be run interactively so outputting to the screen becomes pointless.

Click through for a depiction (sans farm animals) of how transcripts can be useful and how you can create one.

Comments closed

Row-Level Security in Postgres

Published 2024-04-04 by Kevin Feasel

Craig Kerstiens implements row-level security:

Row-level security (RLS) in Postgres is a feature that allows you to control which rows a user is allowed to access in a particular table. It enables you to define security policies at the row level based on certain conditions, such as user roles or specific attributes in the data. Most commonly this is used to limit access based on the database user connecting, but it can also be handy to ensure data safety for multi-tenant applications.

Read on to see how you can implement it. It’s pretty similar to the way we do it in SQL Server, though SQL Server has a few parts of policy evaluation that are more explicit versus putting the execution function code itself in a USING clause.

Comments closed

Thoughts on Copilot in Fabric

Published 2024-04-04 by Kevin Feasel

Marc Lelijveld notes a pattern:

Lately it’s copilot before and after in all Microsoft communications and announcements. Also, during the Fabric Community Conference in Las Vegas where I was last week, it was hard to keep count of the number of times copilot was mentioned. Next to Microsoft communications, everyday conversations with customers and colleagues include copilots everywhere too. Which is fine, as long as we manage to keep things in perspective.

In this blog I will further elaborate on my experiences with copilot, where I experienced it as a useful addition to my day-to-day job and also where we should be careful. And no, this is not a rant on copilot, but just sharing my vision and everyday experience when talking about copilot to my customers.

I appreciate Marc’s grounding here. There’s a huge amount of hype around the various copilots, but after the initial demos, there’s a good bit less utility than meets the eye at present.

Comments closed

Imputing Future Values with Power BI

Published 2024-04-04 by Kevin Feasel

Joseph Yeates makes a forecast:

I recently had a requirement for a Power BI report to generate a forecast. The report data model consisted of daily amount information, which was aggregated and summarized in the report for monthly totals.

The data was current year to date, but for the remaining months in the year rather than displaying no data, the users wanted to impute the average for all of the previous months that did have data.

Granted, this doesn’t do anything fancy like factoring in seasonality or periodicity, but it is easy to put together.

Comments closed

Switching a Git Commit to another Branch

Published 2024-04-04 by Kevin Feasel

Olivier Van Steenlandt fixes an oopsie-doodle:

In the past couple of months, I’ve been doing a lot of different things at the same time. Because of that, it is no exception that I have quite some branches for my Database Projects that I’m working on simultaneously.

Because of this, another issue arises as well, it happens now and then that I’m committing my changes to the wrong branch. Luckily for me, I’m making this mistake by committing directly to my development branch, where the change needs to be done in the end anyway.

Nevertheless, I need to get my change into my feature branch as well. In this data recipe, I will be guiding you through the process.

Click through to see how. If you’re using a GUI like GitHub Desktop or Sourcetree, there are also ways of doing this within the UI.

Comments closed

Finding Last Access Dates for SQL Server

Published 2024-04-03 by Kevin Feasel

David Fowler checks the calendar:

Your boss walks up to you one morning and says, “Hey, I wanna list of all of our databases and when they were last accessed”.

If you’ve got some sort of auditing switched on or a trace or xevent catching this sort of info you might be ok, but I’m betting you don’t have any of that. That’s cool, it’s not something that I tend to monitor as standard either.

But if you’re not monitoring it, is there any way that you can get at that info?

Read on for one way to estimate it. Though I believe automated jobs would skew that result if the underlying question is, “When did a human last view that database?”

Comments closed