Press "Enter" to skip to content

Curated SQL Posts

Automating Power BI Premium Dataset Backup

Published 2023-02-02 by Kevin Feasel

Gilbert Quevauvilliers shares the first part of a two-part series:

The first part in this 2-part series I am going to explain how configure the Azure Runbook so that you can then re-use it for multiple different Power BI datasets.

I am confident that most people have more than one dataset that needs to be backed up.

Before starting, please make sure that you have connected your Power Per User or Premium App Workspace to Azure Storage

Read on for a high-level overview of how to create a runbook in Powershell, as well as the runbook code.

Comments closed

Simplified Syntax for Scala 3

Published 2023-02-01 by Kevin Feasel

Anshika Agrawal shows a few examples of how Scala 3 is a bit easier to pick up than Scala 2:

Scala 3 is a remodel/refit for the scala language. It attracts developers due to its improved features such as simpler syntax, better type inference, improved error messages, and enhanced support for functional programming. In this article, we will compare the syntactical enhancement of Scala 2 & Scala 3. How indentation will help developers to write code efficiently and effortlessly.

Click through for some examples. On the whole, these are small but welcome changes in eliminating unnecessary code cruft.

Comments closed

SOUNDEX in Snowflake

Published 2023-02-01 by Kevin Feasel

Kevin Wilkie makes a noise:

In Snowflake, there is another function that is SOUNDEX-like that can give slightly different results – SOUNDEX_P123.

For those cases where the first and second letters of the string have the same SOUNDEX number, the SOUNDEX_P123 function will keep the number for the second letter. This variant is used in a few other database systems, for example, Teradata.

Click through for a demonstration of the two SOUNDEX() variants and how results can differ.

Comments closed

Reviewing Database Usage Trends

Published 2023-02-01 by Kevin Feasel

Brendan Tierney looks at the data:

Getting back to the topic of this post, I’ve gathered some data and obtained some league tables from some sites. These will help to have a closer look at what is really happening in the Database market throughout 2022. Two popular sites who constantly monitor the wider internet and judge how popular Databases area globally. These sites are DB-Engines and TOPDB Top Database index. These are well know and are frequently cited. Both of these sites give some details of how they calculate their scores, with one focused mainly on how common the Database appears in searches across different search engines, while the other one, in addition to search engine results/searches, also looks across different websites, discussion forms, social media, job vacancies, etc.

I don’t necessarily believe that these are totally accurate, though on the whole, I do expect the results to be directionally accurate. I’ve used DB-Engines data several times in the past and like to point out that, for any given year, 7 or 8 of the top 10 database engines are relational.

Comments closed

Restoring a TDE Database Sans Certificate

Published 2023-02-01 by Kevin Feasel

Matthew McGiffen helps us recover from a big oopsie:

If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then you could be in a lot of trouble. There is one scenario where you have a way out. I’m going to assume you don’t have the possibility to recover your old server from a complete file system backup – if you do then you can do that and access all the keys you require. If the two following things are true though then you can still recover your database:

Read on to see what those requirements are and how you can, in specific circumstances, recover that database.

Comments closed

Sessions and Execution of Dynamic SQL

Published 2023-02-01 by Kevin Feasel

Deborah Melkin riddles us this on dynamic SQL:

I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an interesting problem that had me puzzled. I found a bunch of different blog posts that pointed to me to the right direction but required a little extra work to find the solution.

There are several concepts that are at play here, so I’ll try to break this out so we can put the pieces together. The first once is centered around dynamic SQL. There are two parts of this I want to make sure we understand first – how it fits into sessions and how it gets executed.

Read the whole thing.

Comments closed

Configuring Ola’s Scripts

Published 2023-02-01 by Kevin Feasel

Ben Miller begins a series on Ola Hallengren’s maintenance solution:

I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.

This first post acts as a primer for those who might have the solution but haven’t investigated it in any detail.

Comments closed

Java Licensing Changes Upcoming

Published 2023-02-01 by Kevin Feasel

Lindsay Clark covers an upcoming licensing change:

Industry experts have pointed out that businesses with limited Java use would have to license the software per employee under the new model, a dramatic shift from the one Oracle previously afforded them.

This week, Big Red – which acquired Java with its buyout of Sun Microsystems in 2009 – said the new Java SE Universal Subscription is “a simple, low-cost monthly subscription that includes Java SE Licensing and Support for use on Desktops, Servers or Cloud deployments.”

Seems like as good a reason as any to kick the Java habit.

Comments closed

Max and Min Functions in KQL

Published 2023-01-31 by Kevin Feasel

Robert Cain goes extreme:

The max and min aggregation functions are common to almost every language, and the Kusto Query Language is no exception. As you would think, when you pipe in a dataset max returns the maximum value for the column name you pass in. Likewise min returns the lowest value.

In addition, there are variants for each, maxif and minif. We’ll see examples for all of these in this post.

Click through for a few functions you can call via the summarize operator.

Comments closed

Organizing R Code

Published 2023-01-31 by Kevin Feasel

Tomaz Kastrun tidies up:

Keeping your R code organised is not as straightforward as one might think. Just think about the libraries, variables, functions, and many more. All these objects can be defined and later rewritten, some might get obsolete during the process.

This process is proven to be even more crucial when you are part of a larger group of engineers, and scientists, who collaborate with you.

Click through for some organizational tips specific to R code.

Comments closed