When a SQL UPDATE Statement DELETES Rows

Published on: 2019-11-26

Watch this week’s episode on YouTube.

At first I wasn’t going to write this post. I thought it would be too simple.

Then I watched two experienced SQL developers go through similar scenarios last week.

Sometimes the simple things are worth revisiting, so…

A SQL Server UPDATE Bug

I received a message from someone stating that when they update a row in their table, the row gets deleted.

Thinking that was strange, I asked the user if they could reproduce the issue. And they did. And after running the update statement, the row disappeared.

“WHAT THE…?”

Reproducing the Issue

So here’s the scenario: we had an SSIS configuration table that looks something like this:

DROP TABLE IF EXISTS ##Configuration;
CREATE TABLE ##Configuration	
(
	ConfigurationFilter nvarchar(255) PRIMARY KEY,
	ConfiguredValue nvarchar(255),
	ConfiguredValueType nvarchar(20)
	-- some other fields
);

If you use SSIS, you might be familiar with this setup. In the table we had some innocuous looking rows:

INSERT INTO ##Configuration VALUES ('AdventureWorks_ETL_Bypass','1','int');
INSERT INTO ##Configuration VALUES ('WideWorldImporters_ETL_Bypass','0','int');
INSERT INTO ##Configuration VALUES ('Northwind_ETL_Bypass','1','int');

Querying a single ConfigurationFilter value returns a single row:

SELECT * FROM ##Configuration WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'
1 record found

Let’s say we now want to update the 1 value to a 0::

UPDATE ##Configuration SET ConfigurationFilter = '0' 
WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'

Then, let’s check to see if our change went through:

SELECT * FROM ##Configuration WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'
no rows!

“WHAT THE …?”

Following the Rules

Do you see the problem?

Of course you do. But in the excitement of the moment, I didn’t see the issue. I thought there was some SQL Server feature taking over that I didn’t understand. Or possibly a bug in how UPDATE works in certain scenarios. How could an UPDATE possibly DELETE a row of data?

Look at the above UPDATE statement again. Our WHERE clause is filtering on the ConfigurationFilter field, which in this case is our table’s primary key; it will only ever return one unique row.

That is until we change the value of that row’s primary key: the SET clause is also updating ConfigurationFilter. This is the mistake. Since ConfigurationFilter has a different value after the update, our original query makes it appear that the row was deleted – when in reality it is now considered a different row based on the way we defined our primary key:

changed it to a 0...whoops

Since there a lot of “Config…” names in this table, the field used in the SET statement should have been ConfiguredValue¬†instead of ConfigurationName. Simple case of updating the wrong field.

Lesson Learned

SQL Server has been thoroughly vetted by running on millions(?) of systems. Bugs do exist, but the chances of you discovering a bug, let alone one that affects such a basic feature such as UPDATE, is very unlikely at this point.

The lesson here is that if you do think you find an issue, go back and check your query: it’s more likely there was an error with the connection between chair and keyboard rather than with the tool itself.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

First Time Speaking at PASS Summit

Published on: 2019-11-12

This week, I filmed my experience as a first time speaker at PASS Summit in Seattle. No blog post, just a video to relive the experience.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

SQL Server 2019 Feature Power Rankings

Published on: 2019-10-29

Watch me comment on all of the features in this week’s video.

With the release of SQL Server 2019 imminent, I thought it’d be fun to rank which features I am most looking forward to in the new release.

(Also, I needed a lighter blogging week since I’m busy finishing preparing for my two sessions at PASS Summit next week – hope to see you there!).

SQL Server 2019 Feature rankings

I decided to rank these features on two axes: Excitement and Priority

Excitement is easy to describe: how excited I am about using these features. In my case, excitement directly correlates with performance and developer usability improvements. That doesn’t mean “Low Excitement” features aren’t beneficial; on the contrary, many are great, they just don’t top my list (it wouldn’t be fun to have a quadrant with everything in the top right).

Priority is how quickly I’ll work on implementing or tuning these features. The truth is that some of these features will work automatically once a SQL Server instance is upgraded, while some will require extra work (ie. query rewriting, hardware config). Once again, “Low Priority” features aren’t bad, they just won’t be the features that I focus on first.

Finally, these rankings are based on Microsoft’s descriptions of these features and what little tinkering I’ve done with pre-releases of SQL Server 2019. As far as I know, this chart will totally change once I start using these features regularly in production environments.

And here are my rankings in list form in case that’s more your style:

High Excitement, High Priority

  • Scalar function inlining
  • Memory grant feedback
  • sys.dm_exec_query_plan_stats
  • Accelerated Database Recovery
  • Table Variable deferred compilation

High Excitement, Low Priority

  • Big Data Clusters
  • Polybase all the things
  • Enhancements to running on Windows, Linux, and containers

Low Excitement, High Priority

  • Batch mode on rowstore indexes
  • Index encrypted columns
  • Optimize for sequential key
  • Useful truncation error messages

Low Excitement, Low Priority

  • New graph functions
  • Java language extension

What are you most excited for in 2019? What features did I miss? Disagree with where something should be ranked? Let me know in the comments below.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!