An Easy Way to Determine the Data Type of an Expression Result

I suspect SQL Server developers and DBAs spend very little time thinking about data types, probably because SQL Server generally is so forgiving about data types. SQL Server will, without complaint, perform implicit conversions whenever it can to match input data types to the defined data types of columns and variables and the specified data types for arguments of functions. This gives SQL Server the flexibility to execute T-SQL queries and commands that would otherwise fail due to data type mismatches. When developers and DBAs rely too heavily on this feature and fail to appreciate the nuances of SQL Server’s handling of data types, though, their code can be susceptible to stealthy errors. Some of these errors may not become apparent until a specific use case arises, like the sneaky data type conversion error I described in my last post, often long after the code is in production.

In the coming weeks, I’ll be posting more about data types and the importance of good data type management in T-SQL code. One of the challenges of good data type management, though, is knowing what data types are in play. Mathematic operators and aggregate functions, for example, often return data types other than the input data types. While it’s possible to puzzle out the ending data type, it can be tedious with a long equation or nested functions. As I was debugging a data type error last week, a colleague reminded me of a quick and easy way to determine the data type of an expression’s result, which I’ll leave you with now:

DECLARE @decimal103 decimal(10,3) = -13423.334
 ,@decimal42 decimal(4,2) = 34.67
 ,@int int = 5
 ,@tinyint tinyint = 255
SELECT @decimal103 + @decimal42 AS column1
 ,@decimal103 - @int AS column2
 ,@tinyint * @decimal42 AS column3
 ,@decimal42 / @int AS column4
INTO dataTypeTest
SELECT s.name, t1.name AS system_type_name, s.max_length, s.precision, s.scale 
FROM sys.columns s
LEFT OUTER JOIN sys.types t1 ON s.system_type_id = t1.system_type_id
WHERE s.object_id = object_id(N'dataTypeTest')
Advertisements

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

%d bloggers like this: