Watch this week's video 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'
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'
"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 Configuration Filter 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 Configuration Filter . 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:
Since there a lot of "Config..." names in this table, the field used in the SET statement should have been Configured Value instead of Configuration Name . 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.