Press "Enter" to skip to content

Curated SQL Posts

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

Data Types and CONCAT_WS

Published 2023-01-31 by Kevin Feasel

Koen Verbeeck hits a concatenation issue:

I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very long, for example if a dimension has a lot of columns. When debugging, I noticed a couple of statements failing with various errors. Turns out, they were truncated after 4000 characters. What was going on?

Read on to see what happened.

Comments closed

Restoring a TDE-Enabled Database

Published 2023-01-31 by Kevin Feasel

Matthew McGiffen notes how to restore a database with transparent data encryption:

When encrypting a database with Transparent Data Encryption (TDE), a vital consideration is to make sure we are prepared for the scenario where something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?

Click through to learn what you’ll need to have.

Comments closed

Minimizing Cross-Highlighting on Direct Query

Published 2023-01-31 by Kevin Feasel

Dany Hoter explains why cross filtering might not be the best when you are using Direct Query in Power BI:

Every time a user clicks on a row or a column in a visual, every other visual in the page feels an urge to refresh itself and respond to the click.

Visual that are based on direct query may issue multiple queries for each refresh.

Depending on the number of measures used in the visual, the number of generated queries can be as high as 10 or even more for a single visual.

Read on for more information and what you can do to avoid this problem.

Comments closed

Window Functions in DAX

Published 2023-01-31 by Kevin Feasel

Marco Russo and Alberto Ferrari lay out the foundations of window functions:

Window functions by themselves do not increase the expressivity of DAX. Most if not all of the calculations performed with window functions can be expressed with more complex DAX code. The goal is to simplify authoring these calculations and improve their performance.

These new functions also introduce a new concept to the DAX language: “apply semantics”. We will publish more articles about window functions and “apply semantics” over time. SQLBI+ subscribers will get a dedicated video course later this year and already have access to the window functions whitepaper we are currently writing.

Click through for an introduction.

Comments closed