Press "Enter" to skip to content

Author: Kevin Feasel

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

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

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

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

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

Creating Functions in Kusto Queries

Published 2021-08-17 by Kevin Feasel

Dennes Torres continues a series on working with the Kusto language:

In the previous blog, I illustrated how to create sub-queries in Kusto.

However, sometimes we may face even more complex situations and we may need to create not only a sub-query, but a function.

Another way to think about a function inside a Kusto query is like a parameterized sub-query.

Read on for an example of creating a Kusto function.

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

Fixing Hadoop Namenodes in Safe Mode

Published 2021-08-16 by Kevin Feasel

The Hadoop in Real World team doesn’t need to play it safe:

When namenode is started or restarted, namenode will be in safemode for a period of time. At this time you will not be able to use your Hadoop cluster fully. Write operations to HDFS will fail and because of that your MapReduce jobs will also fail.

Read on for other reasons why the namenode might be stuck in safe mode and what you can do to fix it.

Comments closed

Fun with GO and Preventing SQL Batches from Parsing

Published 2021-08-16 by Kevin Feasel

Solomon Rutzky has an apropos post for Friday the 13th:

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.

Comments closed