I came upon this issue when I was building some views to support legacy integrations to an app that I was refactoring. The view is supposed to have exactly the same column definitions as a table in the old database that I am redesigning, so to make SSIS packages and other integrations run smoothly, I want the view’s columns to have the same datatypes, nullability, etc.
But there are some gotchas to watch out for with CAST and CONVERT.
I’m trying a new type of blog post, and if it works out, I would be happy to post more of the same going forward. The format is a real-world troubleshooting mystery, and I’ll clue you in to the details along the way.
How quickly can you crack it?
NULL is an undefined value and as such, it has a special status as values and datatypes go in SQL Server. There are quite a few potential pitfalls to watch out for when you’re dealing with data that can contain NULL values.
When you perform divisions, you need to make sure that the divisor, the number to the right of the division operator, isn’t zero. If it is, you will end up with a division-by-zero error message:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Dividing by NULL, however, doesn’t. So to avoid division-by-zero errors, simply use NULLIF() to replace zeros in the divisor with a NULL value.