Allow me a short revisit to the previous topic, because this example came from “real life” in my actual job in a production environment, and I wanted to share. And remember, take this with a grain of salt — there are very few absolutes, a DBA’s favorite answer is “it depends”, and even when we spout rules or decrees, there are usually some exceptions.
Now, let’s start at the top. T-SQL is a strongly typed language. SQL Server, like most RDBMSs, counts datatypes as a foundational element in its ecosystem. Things like
string), etc. We also have this concept of
NULL. Any element of any datatype can be
NULL, which, coincidentally, means a
bit field can actually be 3-valued instead of just binary; BUT, that’s not the topic of this post. However, it does involve a
bit field. And, as the title says, a
First, the larger problem, or “what these little oversights / mis-steps can lead to”, as blogged by someone much smarter than me: Jonathan Kehayias – Implicit Conversions.
Now the diagram:
Do you see the problem? It’s not so obvious if you’re not thinking about datatypes, but look closely. Yes, our view’s
DisplayInMenu field is a different datatype than its base field (origin). That’s because the result of the
CASE expression that defines it is “promoted” to the
int type, instead of remaining a
bit. The same would be true of a
ISNULL expression. This is an example of datatype precedence. If you’ve ever wondered “who would win in a fight?” amongst the SQL datatypes, Microsoft has the answer right there in black & white.
This isn’t necessarily a problem, all by its lonesome. So why did it bite us in the proverbial behind? Two reasons. First, query & usage patterns:
vwCategoryByGroup.DisplayInMenu happens to be an important field in our queries, and usually it’s compared against a
bit parameter or variable that’s passed down from the app’s lower tier. Sometimes it’s even
JOINed to another column, say,
GroupMenuProperty.DisplayInMenu — which is, of course, a
bit. But because it’s an
int in the view, SQL is doing extra work every time to implicitly convert those
ints so that each side of the comparison operation is of the same type. And again, not always, but sometimes, this causes performance problems.
The second reason
is, admittedly, a bit beyond my understanding, so I’ll just explain the symptoms and leave the technical details to the more inquisitive minds. Basically, during a performance crisis, one of the measures we took was to “fix” the view by turning
DisplayInMenu back into a
bit. However, we found that it literally broke the dependent .NET application sitting on top, which started throwing exceptions of the “invalid cast” flavor. I believe it’s using Entity Framework. Thanks to a helpful tip from the Brent Ozar Office Hours webcast, I found out that it’s because the EF entity mapped to this
view had that field (property?) defined as an
int, and in order to make it “see” the datatype change, the code itself would need to be changed, i.e. that property would need to be defined as a
bit. Yay learning!
So, dear reader, be conscious of your decisions with datatypes, especially when it comes to views with superficial computed columns. But more to the point, beware of implicit conversions and mis-matched datatypes. They can be, at best, a source of technical debt; at worst, a silent killer.
Til next time!