Views & Mismatched Datatypes

If you’ve ever wondered “who would win in a fight” amongst SQL datatypes…

Advertisements

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:

category, categorygroup, view-category-by-group
In English, the view’s 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 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 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.

ms-sql-stop-making-me-work-so-hard
I’m tired! Can’t you see I’ve been implicitly converting these datatypes all day?

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!

the-more-you-know
Also, did you know that EF really isn’t all that bad? 😉

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!

Author: natethedba

I'm a SQL Server DBA, family man, and all-around computer geek.

3 thoughts on “Views & Mismatched Datatypes”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s