Press "Enter" to skip to content

Curated SQL Posts

Running Queries with Always Encrypted

Published 2023-06-14 by Kevin Feasel

Matthew McGiffen retrieves some data:

In this post we’ll look at how you interact with data that is encrypted using Always Encrypted. The examples here will show how you run queries from SSMS, in later posts we’ll look at stored procedures and application code – as well as what happens in the background when you execute a query.

For these examples we’re going to be using the database we created in the last post.

Always Encrypted definitely changes the way you work with those encrypted columns, and you might run into some frustrating errors along the way, as things you could get away with before are no longer possible.

Comments closed

DirectQuery Data Modeling

Published 2023-06-14 by Kevin Feasel

Jason Cockington share some advice:

From my experience, most people who have reports built on a DirectQuery connection into their data source did so because of a lack of understanding of what the DirectQuery connection was designed to achieve.  For the vast majority of reports, Import mode is the best solution for working with data in Power BI.  DirectQuery should really only ever be applied when you are trying to solve one of the following challenges.

  1. Real-time Data – you need to see the latest available data from the source
  2. Huge Datasets – you have many billions of rows of data (more than 10Gb) so you just can’t import it into Power BI
  3. Regulatory Compliance – the data must stay in the source for data security/privacy reasons

Click through for more information.

Comments closed

A Primer on SQL Audit

Published 2023-06-14 by Kevin Feasel

Deepthi Goguri gives us an overview of Azure SQL Database’s built-in auditing functionality:

As you all know how crucial it is to Audit activity on the Server for both prod and non-prod environments, turning on the auditing in Azure SQL is pretty simple and the results we see in the audit log are similar to the logs we see on-prem. The difference is where we save the audit data in Azure.

You can enable the auditing at the Server level and at the database level just like the way we can audit SQL Server on-prem. If you would like to enable audit at the Server level in Azure, it will automatically audit all the databases under that Server. If we allow the auditing at the server level (logical Server for Azure SQL Databases) and also at the database level, we might get double the amount of collected audit data as it contains the same data twice. Always chose the Storage account if you wanted to audit the data at the Server level. If you just want to collect the audit data on one or some databases only, you can disable the logical Server level audit and enable the Auditing at the database level.

Read on for more information and to see a bit of it in action.

Comments closed

Table Clustering and Search Optimization in Snowflake

Published 2023-06-13 by Kevin Feasel

Kedhar Natekar continues a series on Snowflake performance:

Clustering does not guarantee improved performance on non-clustered columns. 

If you have frequent queries on non-clustered columns and performance is the key irrespective of cost, then opt for a search optimization service over the entire table of specific columns.

It’s similar to enabling indexing on RDBMS databases like Oracle on specific columns.

Read on to see how these work and get a few tips along the way.

Comments closed

Creating a Spark UDF

Published 2023-06-13 by Kevin Feasel

The Big Data in Real World team creates a Spark user-defined function in Scala:

In this post we are going to create a Spark UDF which converts temperature from Fahrenheit to Celsius.

Here is our data. We have day and temperature in Fahrenheit.

And, of course, it’s roughly the same in PySpark. Also, note that user-defined function performance will take a hit, and that answers are fairly consistent through the years, so save these for when you need them.

Comments closed

Calculating Moving Averages with DAX

Published 2023-06-13 by Kevin Feasel

Leila Etaati smooths the curve:

The calculation will be done for all rows, till we don’t have the anymore data ahead ( 3 rows ahead) as you can see it will finished on row 2018 as 2018 is the average of 2018, 2019 and 2020.

so in the moving average we will miss some data point but replacing with average of data.

Less data point but help us to see a clear trend.

Click through to see how to do this. Moving averages is really good for the reports in which you want to get a rough magnitude but don’t care about exact values and don’t want oscillations to throw you off. For example, if I need to know how many seats to reserve for an event, I might use a moving average of the last several events as my baseline, rather than the prior event’s number.

Comments closed

Creating a Microsoft Fabric Environment

Published 2023-06-13 by Kevin Feasel

Kevin Chant gets at it:

In reality, there are a few different ways to join the Microsoft Fabric (Preview) trial.

For example, you might be lucky enough to have it enabled in the workplace already. However, there are ways that you can create your own Microsoft Fabric environment as well.

Click through for the process, and note that the trial is 60 days, though Microsoft will let you renew the trial until the product goes GA.

Comments closed

Missing Index Hints and Index Rebuilds

Published 2023-06-13 by Kevin Feasel

Forrest McDaniel abuses that poor server:

The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.

An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.

Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.

Comments closed

SSMS 19.1 Updates

Published 2023-06-13 by Kevin Feasel

Erin Stellato has a two-parter around SSMS 19.1. The first includes a small but important change:

In SSMS 19.1, the default value for Use system default web browser was changed to True.  This only applies to new installations; anyone upgrading from an earlier version of SSMS 19 will need to change the option to True manually.

This means that, for new installations, clicking on a link in SSMS will no longer open in the built-in SSMS browser, which doesn’t work for a lot of newer sites.

Part 2 picks up with some additional changes:

We have heard complaints, for months, about the startup time for SSMS.  Between 18.12.1 and 19.0 we made a few changes, and in 19.1 we delayed initializing the output window to also reduce startup time.  Some of you noticed!  Glenn Berry completed a round of testing and documented findings in his SSMS 19.1 Performance Improvements blog post.  We are hoping to also gain improvement when we get to SSMS 20, so know that our work is not yet done.

Click through for the full set of chnages.

2 Comments

Shiny Apps and Fullscreen Behavior

Published 2023-06-12 by Kevin Feasel

Tim Brock gives us a demo:

Browsers have been implementing variations on a JavaScript fullscreen API for over a decade. Unfortunately, for much of that time the APIs varied across browsers. This made actually using it in production somewhat cumbersome.

Finally, with the release of Safari 16.4 in March of this year, the latest versions of all major desktop browsers now support a single, standardized interface. Legacy versions of Safari for desktop are still in use and there’s still no support at all for the Fullscreen API on iPhones; so while you can cover most users with the standardized API, it should still be for progressive enhancement and not as a fundamental requirement for operation of an application.

Click through for the script.

Comments closed