Press "Enter" to skip to content

Month: September 2022

Non-Updating Updates

Michael J. Swart has an update for us:

According to Microsoft’s documentation, an UPDATE statement “changes existing data in a table or view”. But what if the values don’t actually change? What if affected rows are “updated” with the original values? Some call these updates non-updating. This leads to a philosophical question: “If an UPDATE statement doesn’t change any column to a different value, has the row been updated?”

I answer yes to that question. I consider all affected rows as “updated” regardless of whether the values are different. I think of the UPDATE statement as more of an OVERWRITE statement. I also think of “affected rows” rather than “changed rows”. In most cases SQL Server thinks along the same lines.

I list some features and areas of SQL Server and whether non-updating updates are treated the same or differently than other updates:

Click through for multiple scenarios and to see whether SQL Server is smart enough to ignore non-updating records in an UPDATE statement.

Leave a Comment

Using DaxDebugOutput when testing EvaluateAndLog()

Gilbert Quevauvilliers hooks us up:

I have seen a few great blog posts with regards to the new DAX function EvaluateAndLog which can be used to show/debug what happens with DAX Measures.

When I tried this out myself one of the challenges I had was where to download DaxDebugOutput, and then how to use it with Power BI Desktop.

In this blog post I will show you how I downloaded, installed, and used DaxDebugOutput application with Power BI Desktop.

Read on to see how the tool works, as well as where you can get it.

Leave a Comment

The Power of Bookmarks in Power BI

Mara Pereira likes bookmarks:

Yes, I know some of you are not a fan of this incredible feature, but hopefully I can change your mind with this blog post.

I feel that for you to like bookmarks, you really need to know all the ins and outs of it, otherwise it can be quite overwhelming, specially if you have to create loads of bookmarks in the same report.

Read on for more information about how you can take best advantage of bookmarks in Power BI. My main issue with them is that it’s difficult to keep bookmarks up to date, especially as you get more complicated combinations of actions (like hiding and displaying certain sets of visuals). But that is for the next post, apparently.

Leave a Comment

Triggering Dumps for Specific Errors and States

Bob Dorr has a plan:

I had an inquiry about dbcc dumptrigger today and realized that state filtering was added to dbcc dumptrigger but not well known.

You can collect a process dump (SQL Dumper) when a specific error occurs using XEvent (error_reported/ex_raise2 events with dump capture action) or dbcc dumptrigger.

The common use of dbcc dumptrigger is: dbcc dumptrigger(‘set’, 208) to produce a dump when error 208 is encountered.

This is pretty useful, especially if you’re troubleshooting a bug in the database engine.

Leave a Comment

Understanding CCPA

Anas Baig provides a primer on the California Consumer Privacy Act:

While the GDPR has some of the most crushing requirements, such as the brief 12-hour window to report a breach, CCPA takes a broader view. It goes even further to locate and protect what constitutes personal or private data as per the GDPR. 

The primary difference between the two is that GDPR is more about prior consent, while CCPA is about opting out. GDPR binds businesses to ask for consent before having a consumer’s data stored and processed. On the other hand, CCPA requires businesses to enable consumers to opt-out at any point. 

It allows consumers to access every piece of personal information saved and a complete list of third parties it is shared with. Moreover, consumers have the right to sue a company for violating privacy guidelines even if there has been no breach.

Considering how much we heard about GDPR, I’m surprised there’s been so little about CCPA.

Leave a Comment

Search on Windows with Everything

Tom Zika reviews a product:

Not everything in the general sense, but a tool called Everything by voidtools (Download link). Usually, I have to make this distinction when googling.

No matter how great is my folder structure or naming conventions, there comes a time when I have trouble locating something.
Maybe the software has a default download location which I forgot (*cough* Teams *cough*), or I want to find an install folder, or I can’t locate a picture I’ve recently saved.

Read on to learn a bit more about the free tool. After Tom’s recommendation, I gave it a try and yeah, it’s fast.

Leave a Comment

In-Place SQL Server Upgrades

Garry Bargsley rolls the dice:

In my experience, two options exist to get the desired result. One, create a new server, install the latest supported version of SQL Server, and migrate your data. Two, upgrade SQL Server on the existing server.

There are pros and cons to each of these options. My preference is to go with option number one as it allows you more flexibility in your migration plan. However, many smaller shops might not have the hardware resources for this option, so they are forced to option number two.

In-place upgrades have improved considerably, though certain ancillary services (like Machine Learning Services) have breaking changes between versions, so you may be forced into the first route regardless.

Leave a Comment

MERGE in Dedicated SQL Pools

Emily Tehrani notes an addition to Azure Synapse Analytics:

We are thrilled to announce that the MERGE T-SQL command for Azure Synapse Dedicated SQL pools is now Generally Available! MERGE has been a highly requested addition to the Synapse T-SQL library that encapsulates INSERTs/UPDATEs/DELETEs into a single statement, drastically easing migrations and making table synchronization a breeze.

If you do decide to use this, I’d expect it to have the same bugs which make its use on-premises a mess. As always, MERGE responsibly.

Leave a Comment

Azure SQL MI Error Loading Backup Retention Policies

Paloma Garcia Martin troubleshoots an error:

When you try to create a new database (*) using Azure Portal using non supported characters, you will see an error indicating characters that you cannot use on the database name.

But if you use SSMS tool, it doesn’t include these characters cheeking and it will not avoid you to use these non-supported characters on the database name. 

Click through for an example of this error in action.

Leave a Comment