Press "Enter" to skip to content

Curated SQL Posts

Django Support for SQL Server

Published 2021-08-18 by Kevin Feasel

Warren Chu announces a 1.0 version of a new product:

We’re officially announcing the release of mssql-django v1.0 as an open source project!

At Microsoft we’ve heard from the community loud and clear – SQL Server is the biggest enterprise backend not yet fully supported in Django.

That’s about to change.

This project picks up where previous open source projects have left off. We began with a series of preview releases in February 2021, and we’re pleased to officially bring Microsoft support to SQL Server and Azure SQL DB with this version’s official release.

Django is still a fairly popular platform, so I’m happy to see this released.

Comments closed

Row Pattern Recognition in Snowflake

Published 2021-08-17 by Kevin Feasel

Koen Verbeeck knows how to make my blood boil:

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.

In the book T-SQL Window Functions – For data analysis and beyond, Itzik Ben-Gan explains the concept of row-pattern recognition (RPR) in a dedicated chapter (you can find a full book review here). It’s a concept that doesn’t exist in T-SQL, but is described in the SQL standard and is available in some other database systems. Snowflake has recently introduced support for RPR. 

Jokes about being angry aside, I’d really like to see row pattern recognition in SQL Server. It’s definitely not trivial to learn, but once you do, there’s a lot of power available to you. Koen also links to the Feedback item about this, so vote on that as well.

Comments closed

Naming Azure Purview Scans

Published 2021-08-17 by Kevin Feasel

Daniel Janik treats Azure Purview scans like pets rather than cattle:

If you’ve ever been a DBA and seen the mess that you get with SQL Agent Jobs without a clean naming standard for your job schedules and job names then you’ll appreciate this tip.

If you haven’t been a DBA that’s OK too. Years ago I came up with my own naming standard for SQL Agent artifacts and I’ve always felt better when the messy room was clean. No Really! That’s exactly what this is like. A messy room where you are pretty sure you put the item you’re looking for in but you just can’t seem to find it until you clean 95% of the mess and then you’re so exhausted that you don’t have time to do what you wanted to in the first place. Ever been there?

Read on to see what the scans look like by default, as well as some thoughts Daniel has regarding a better way to do things.

Comments closed

The CONTAINS Function in DAX

Published 2021-08-17 by Kevin Feasel

Marco Russo and Alberto Ferrari share their thoughts on the CONTAINS() function in DAX:

The CONTAINS function in DAX has been available since the very first version of the language in 2010. In the evolution of the language, new syntaxes and functions have been added, and several use cases for CONTAINS that were valid many years ago are no longer considered good practice. The goal of this article is to clarify when CONTAINS is a good practice and when there are better alternatives to solve common problems.

Read on for that explanation.

Comments closed

Filtered Indexes in SQL Server

Published 2021-08-17 by Kevin Feasel

Esat Erkec takes us through a frustratingly near-useful feature in SQL Server:

Indexes are the special data structures that help to improve the performance of the queries in SQL Server. Against this great benefit of the indexes, they occupy space on hard drives and can slow down the data modification operations (update, insert, delete) performance. When any query modifies the data in a table the database engine needs to update all of the related indexes where data has changed. In certain instances, to minimize these disadvantages of indexes, using SQL Server filtered indexes might be the appropriate approach. Assume that, we frequently query a small subset of a table with the same conditions and the rest of the table contains too many rows. In this scenario, we can use a SQL Server filtered index to access this small data set faster so that we can reduce storage and index maintenance costs.

When you can get them to work, such as when the part of your query which is supposed to access that index is not parameterized, filtered indexes are great. But Esat does get into what happens when that condition doesn’t quite hold.

Comments closed

Working around Unparameterized IN Clauses with EF

Published 2021-08-17 by Kevin Feasel

Erik Darling bears the bad news:

If you’re using an Entity Framework, and sending in queries that build up IN clauses, they won’t end up getting parameterized.

Even Forced Parameterization won’t help you if you’re sending in other parameters. One limitation is that it doesn’t kick in for partially parameterized queries.

Even if they did get parameterized, well, what’s one funny thing about IN clauses? You don’t know how many values will be in them.

Read on for a couple of work-arounds for this.

Comments closed

Using Power BI Goals to Drive OKRs

Published 2021-08-17 by Kevin Feasel

Treb Gatte has an interesting use case for Power BI Goals:

OKRs are but one of three emergent usage scenarios for Power BI Goals. I see some companies using Goals for Metric Aggregation and some for Balanced Scorecards. This post focuses on OKRs as I see the scenario offering broader benefits to the entire organization. OKRs are critical to creating an Action-centric culture.

Read on to understand what an OKR is and how Power BI Goals can help with tracking them.

Comments closed