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 bit
, int
, datetime
, varchar
(aka 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 VIEW
.
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:

DisplayInMenu
field is defined as “If Category’s IsRoot bit is false, always use False; otherwise, use the Group’s DisplayInMenu bit.” In even plainer English, it means we only want to “Display this Category in the Menu if it’s a ‘Root’ Category and it’s a member of a Group that gets displayed in the Menu.”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 COALESCE
or 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 JOIN
ed 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 bits
to 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!