Press "Enter" to skip to content

Day: May 4, 2020

IS DISTINCT FROM with Snowflake

Koen Verbeeck shows us a good operator in Snowflake:

This single expression both checks for the equality of its members, but also checks the nullability of both columns. Awesome. A good habit would be to use IS [NOT] DISTINCT FROM instead of every = or <> in every expression (join clauses, WHERE clauses etc.) and you’ll never get burned by those pesky NULLs again!

Koen has a link to a Microsoft feedback item to add this syntax to SQL Server. But that item’s been there for more than a decade, so I would not hold my breath waiting for it to show up.

Comments closed

The Limitations of Metadata-Only Updates

Eitan Blumin does not like the limitations of metadata-only column changes with SQL Server 2016:

This is an excellent mechanism on the one hand…

However, it’s completely useless when the column you want to change has a CLUSTERED INDEX defined on it (regardless of whether it’s also a PRIMARY KEY or not).

Such a scenario would especially be common with IDENTITY columns (which, ironically, is exactly the kind of examples that Paul presented in his post).

Click through to understand the scope of this limitation.

Comments closed

The Halloween Problem, Continued

Paul White continues a series on the Halloween Problem. Part 2 looks at insert and delete statements:

In the first part of this series, we saw how the Halloween Problem applies to UPDATE queries. To recap briefly, the problem was that an index used to locate records to update had its keys modified by the update operation itself (another good reason to use included columns in an index rather than extending the keys). The query optimizer introduced an Eager Table Spool operator to separate the reading and writing sides of the execution plan to avoid the problem. In this post, we will see how the same underlying issue can affect INSERT and DELETE statements.

Part 3 looks at the train wreck MERGE operator:

In the right circumstances, the SQL Server optimizer can recognize that the MERGE statement is hole-filling, which is just another way of saying that the statement only adds rows where there is an existing gap in the target table’s key.

For this optimization to be applied, the values used in the WHEN NOT MATCHED BY TARGET clause must exactly match the ON part of the USING clause. Also, the target table must have a unique key (a requirement satisfied by the PRIMARY KEY in the present case).

Where these requirements are met, the MERGE statement does not require protection from the Halloween Problem.

If only it weren’t busted in so many other ways!

Part 4 wraps up the series:

The SQL Server optimizer has specific features that allow it to reason about the level of Halloween Protection (HP) required at each point in the query plan, and the detailed effect each operator has. These extra features are incorporated into the same property framework the optimizer uses to keep track of hundreds of other important bits of information during its search activities.

Each operator has a required HP property and a delivered HP property. The required property indicates the level of HP needed at that point in the tree for correct results. The delivered property reflects the HP provided by the current operator and the cumulative HP effects provided by its subtree.

This last one goes into some nice detail.

Comments closed

Using Cognitive Services in Power BI without a Premium Subscription

Marc Lelijveld and Kathrin Borchert show how we can take advantage of Cognitive Services and Power BI without having to pay for Power BI Premium:

Recently, I was presenting my session about AI Capabilities for Power BI to make AI Accessible for Everyone for the Virtual Power BI Days Hamburg. A great event organized by Kathrin Borchert. Part of my session was about the Artificial Intelligence capabilities offered as part of Power BI Premium. A day later, Kathrin came up with a great idea how you can leverage these AI capabilities without the need for Power BI Premium.

I was directly enthusiastic about that idea since I thought about this in the past as well. Back then, there were some blockers which are sorted now. I asked Kathrin if she was open for co-authoring this blog and she immediately agreed.

Click through for the technique. Basically, it’s a trade-off between simplicity and cost.

Comments closed

Replicating SQL’s IN Operator with Azure Data Factory

Rayis Imayev shows how we can find values in a group using Azure Data Factory:

However only this use-case for the OR function with 2 condition could be possible:or(equals(variables(‘var1’), ‘A’), equals(variables(‘var1’), ‘B’)) – limit of two conditions

But what if we have an ability to check if a particular element variable/parameter/other ADF object value belongs to a range of values (array of value), similarly to what we can do with the IN operator in SQL language, this would definitely solve our problem and remove the limitation of logical conditions to check.

Click through for the answer.

Comments closed

Detecting and Changing Multiple Columns’ Data Types with Power Query

Imke Feldmann has a quick tip for us:

Very often, when you expand a column in Power Query that contains a table or some records, the expanded columns will lose their types (like Chris Webb has described here for example). Or you might just have accidently deleted a “Changed Type”-step.

Did you know there is actually a superfast and easy way to do it?

Read on to see how you can change the data type for multiple columns all at once.

Comments closed

The Value of Table Visuals

Shannon Holck gives us several scenarios in which tables are a good choice of visual:

Exact numbers are needed
If your report goes to a customer and you need an exact quantity or price or total, absolutely, a table is likely needed.

Displaying a few discrete values
If you need to display 5-10 things and they all represent unique values, a table may be appropriate.

Need to establish trust in the data
If you don’t trust the data (yet) and want to verify data, you can sometimes compare data at a roll-up level to a known source.  This is a great way to test not only the data but that the calculations are accurate.  

Click through for more reasons, followed by cases in which you want to avoid table visuals, and finally a few ways to improve tables. I’m not the biggest fan of the improved tables (because I want simpler and denser), but this is good food for thought.

Comments closed