Where Clauses Cannot Prevent Divide-by-Zero Errors

So, your query looks something like this:

SELECT s.Quantity / t.TotalQuantity
FROM Sales s
INNER JOIN Totals t
ON t.ProductNumber = s.ProductNumber
WHERE s.SalesDate >= '1/1/2012'
AND t.TotalQuantity <> 0

Totally safe, right? That check on non-zero TotalQuality values should prevent divide-by-zero errors?

Then you run it in production against 100,00 rows and BLAM!

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Ye Gods!  How could this possibly happen?!? Thousands of years of mathematics and physics turned on its head from one simple SELECT statement!

I’ll tell you how it happens… SQL Server has to break this query into a step-by-step plan… and, in its nearly infinite wisdom, it may decide to do things in an order you did not expect.

For example, it may decide to filter out the SalesDate part first, then do all the joins, and then the division and THEN filter on the TotalQuality clause.  If  this is the plan it chooses, and you have zeroes for TotalQuantity… BLAMMO!  You just got yourself a divide-by-zero before you even got to the last filter step which would have saved you.

The ONLY way to guarantee this doesn’t happen is to use a CASE statement in your SELECT as follows. It’s ugly, but safe:

SELECT CASE WHEN t.TotalQuantity = 0 THEN 0
ELSE s.Quantity / t.TotalQuantity
END
FROM Sales s
INNER JOIN Totals t
ON t.ProductNumber = s.ProductNumber
WHERE s.SalesDate >= '1/1/2012'
AND t.TotalQuantity <> 0

Any time you do a division anywhere in your SQL code, you should follow this pattern or risk the wrath of the divide-by-zero gods.

Advertisements

ADO.Net Leaks Isolation levels across pooled connections

Did you know that ADO.Net leaks isolation levels across pooled connections?

This is particularly bad if you’re trying to use Snapshot isolation in SQL Server 2005+.

So, let’s say you create an ADO.NET connection (say, from Entity Framework) and do an update… you’re fierce about your data integrity, so you set your isolation level to Serializable… or you use TransactionScope and don’t bother to specify an isolation level and you get the default of Serializable (ye gods!)

When you’re done, that connection goes back into pool.

Now you open a new connection to and try to read data from a snapshot database with a simple select statement.  That connection you just got came off the pool, but with Serializable isolation level still set!

Now, rather than the beautiful clean, non-blocking read you were hoping for, you get a blocked read… exactly the thing you hoped  snapshot isolation would prevent!

The problem, essentially, is that sp_resetconnection does not reset the isolation level.  You need to do this manually, or use different connection strings for updates/reads so you don’t grab the same connections off the pool.

More detail here:

http://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections

UPDATE (2/10/2017):

This appears to have been fixed in SQL 2014:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/916b3d8a-c464-4ad5-8901-6f845a2a3447/sql-server-2014-reseting-isolation-level?forum=sqldatabaseengine&forum=sqldatabaseengine

Create a change logger with knockout.js

We have a lot of complex editors on our site.  We often find ourselves needing to track changes within a complex view model.  With the ability to extend knockout observables, you can create some really useful and reusable tools to go along with the already powerful knockout framework.

To begin, you’ll need to extend the knockout observable with a change tracking extension.  The power of extending the observable lies in the ability to hang additional observables off the observable, itself.  With these, we’ll be able to track the observable’s original state and provide an isDirty computed column that can then be interrogated at the view model level.  This version handles numbers and strings pretty well, but you might have to do some more coding to deal with dates or more complicated values.

ko.extenders.trackChange = function (target, track) {
    if (track) {
        target.isDirty = ko.observable(false);
        target.originalValue = target();
        target.subscribe(function (newValue) {
            // use != not !== so numbers will equate naturally
            target.isDirty(newValue != target.originalValue);
        });
    }
    return target;
};

Now, we can add this extension to any observable we want to track like so:

self.MyProperty = ko.observable('My Test Value').extend({ trackChange: true });
self.MyProperty2 = ko.observable('My Test Value 2').extend({ trackChange: true });

… and these properties can now be interrogated by the view model:

self.MyProperty.isDirty()
self.MyProperty2.isDirty()

We can write an outer computed function that iterates over the entire view model looking for changed items… this could probably be cleaner, but it gets the job done:

self.isDirty = ko.computed(function () {
    for (key in self) {
        if (self.hasOwnProperty(key) && ko.isObservable(self[key]) 
            && typeof self[key].isDirty === 'function' && self[key].isDirty()) {
            return true;
        }
    }
    return false;
});

It would be cool to be able to run this on the view model as a whole and it would spider over the entire model, including observable arrays, and track changes all the way down.  If it could be made generic, it would make a nice GitHub project.

Enjoy!