Watch this week's video on YouTube
A couple of weeks ago I decided to rebuild my recording studio by getting rid of my fabric backdrop and replacing it with a true wall instead. Doing this would allow me more flexibility when shooting, further improving my filming process efficiency.
To determine how much lumber I would need for building the new walls, I decided to write a SQL query to help with my framing calculations. I was building a 6 foot wall and wanted to put a stud every 16 inches. Easy enough to do the math on this:
SELECT (6*12)/16
The output of the query above was 4, indicating the number of studs I would need for one wall section.
What's interesting is that if we do this same equation in a calculator, we get a slightly different answer: 4.5.
And while I didn't end up framing my walls incorrectly, if I trusted the output of my query I would have had some incorrectly sized walls.
Is SQL Server Bad At Simple Math?
What happened? Well it all has to do with how SQL Server handles calculations.
While tweeting about my studio rebuild process, Kenneth Fisher from SQL Studies tweeted about what I would learn about SQL Server from rebuilding the studio.
https://twitter.com/sqlstudent144/status/1170376006370283520
Jokingly, I tweeted back the above SELECT (6*12)/16 example because it is funny (scary?) how SQL Server chops off the .5 if you don't understand what's going on.
When you perform calculations in SQL Server, it converts any expressions to the datatype that has the highest precedence. In the above example, since all of the numbers we are dealing with are integers, SQL Server keeps the final answer as an integer, apparently not caring what should have come after the decimal.
The quick and dirty way to solve this is to include a datatype in the equation that allows for decimals and has a higher precedence than integer. Basically, convert one of the integers to a numeric by adding .0 to any of the values:
SELECT (6*12)/16.0
This will then return the expected result.
Following up on Twitter, Andy Mallon mentions that you don't even need the 0, simply adding . will suffice:
https://twitter.com/AMtwo/status/1170391334500388865
SELECT * (6*12)/16.
Pat Phelan then took it a step further, saying you can use the e syntax if you want to get the same successful result but confuse your users:
https://twitter.com/YetAnotherSQL/status/1170400514716164096
SELECT * (6*12)/16e0
Out of all of these methods, I prefer adding the .0 because it is the least ambiguous. For calculations that matter however, I also like to throw a CAST around individual values or the entire equation to be certain that I am getting a result with the precision and scale that I expect instead of letting SQL Server automatically guess for me:
SELECT CAST((6*12)/16.0 AS NUMERIC (2,1))
Yes, it's a few extra characters, but the intent is clear.
Why Does All of This Matter?
Implicit conversions and datatype precedence are something that most people starting with SQL Server are not aware of until they discover that their results are "wrong". If your queries require precise answers, then you have to be precise and explicit in how you handle the data (otherwise you might build a studio wall incorrectly!)
For more information about all these types of conversions, check out Andy's post on the subject which has even more fun examples.