Yesterday, I wrote a post about dividing two integers in SQL Server and receiving a float as the result. In the comments section, SQL Server MVP Jacob Sebastian provided some valuable insights that I would like to share with you.
Jacob explained that SQL Server automatically casts the result to the data type with the highest precedence. For example, if you divide two integers, the result will be an integer. However, if you divide an integer and a float, the result will be a float because float has a higher precedence.
Let’s take a look at an example:
SELECT 100 / 3 AS Result -- Result: 33 SELECT 100 / 3.0 AS Result -- Result: 33.333333
You can find the precedence of each data type in the official documentation.
After reading Jacob’s comment, I realized that the same concept applies when creating computed columns in SQL Server. A computed column is a column that is computed from an expression using other columns in the same table. The expression can be a non-computed column name, a constant, a function, or a combination of these connected by operators.
When you add a computed column to a table, you specify an expression for its definition. If the expression involves the addition of two integers, the computed column will have a data type of INT. This is an interesting concept.
For example, if a table has multiple columns with a data type of TINYINT, the computed column derived from these columns will also have a data type of TINYINT. The TINYINT data type has an unsigned range of 0 to 255 and a storage size of 1 byte.
Now, let’s consider a scenario where there are more than 256 TINYINT data types as part of the definition of the computed column. What happens in this case?
Here are the options:
- It will error out.
- It will automatically convert to the next upper range data type (e.g., INT).
- User workaround.
I would like to hear from all of you, my readers, about your thoughts on this question. If you believe option c) is the answer, please share your workaround as well.
In future articles, I will cover how to create computed columns and provide an explanation for the datatype puzzle.
Stay tuned for more SQL Server insights!
UPDATE: Solution to the Puzzle – SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation