There is one topic in query and equation writing that is constantly a minor issue for SQL programmers: implicit data type conversions. Whenever you don’t specifically state the datatype of an expression, like when you write SELECT 1;, it can feel a bit of a mystery what the datatypes of your literal values are. Like in this case, what is 1 ? You probably know from experience that this is an integer, but then what happens when you compare CAST(1 as bit) to the literal 1. Is that literal 1 now a bit? Or is it still an integer?

Perhaps even more importantly, why does this query succeed?

SELECT CASE WHEN '1' = 1 THEN 'Equal' ELSE 'Different' END;

But will this query fail?

SELECT CASE WHEN 'One' = 1 THEN 'Equal' ELSE 'Different' END;

Of course it will, but why? Why isn’t it just false? Why aren’t both of them false? I mean, the string value '1' is not the same thing as a numeric value of 1. Right? Madness! (Madness with a pretty clear and simple pattern, at least.)

Dataype Precedence

The answer to the previous question is tied to the concept of datatype precedents. When you type a literal like '1', it will be treated as a char/nvarchar of some length (more on that in the next section). If you type it as N’1′, it will be treated as an nchar/nvarchar. But what happens when you compare two expressions that are of a different type, like '1' and 1 are.

The same is true when you do something like '1' + 1 to concatenate these values.

Note using + is different than CONCAT or the || operator added to SQL Serve 2025).

If you execute:

SELECT '1' + 1;

You will get a result set of:

-------
1

But why 1? And what datatype is the value that is returned?

The datatype precedence list for SQL Server (which you can find at this Microsoft Learn page: SQL Server Datatype Precedence List) is ordered from highest to lowest base datatype (the size of the type may be expanded in some cases when you do an operation, but not the base datatype which is typically key for comparisons.

So in my case '1' is a char or varchar, which are 29 and 28 on the list. And the 1 is treated as an int, which is 17 on the list. So the '1' is implicitly converted tothe integer value 1, and the values are added.

Note: When there is a need for a specific type, like in a comparison, explicitly setting types is almost always a better choice, and always better in reusable code.

If you look at the listing, you can see that from the lowest to the highest, the precedence is ordered in such a way that makes sense in that the type that represents fewer values is lower than the one that has more values.

This isn’t strictly true, but it is true in families of types. bit->smallint->int->bigint->smallmoney->money->decimal->real-float. And also with time->date->smalldatetime->datetime->datetime2->datetime2->datetimeoffset. It isn’t a perfect equation, and some things like json an xml being high on the chart, and sql_variant between them doesn’t make a ton of sense.

One of the lowest types after the binary types are the string types. char->varchar->nchar->nvarchar. This is very important because we use these character types a lot in our coding. '2026-02-27' is how we represent a date value, but that string is not a date value. It is a string value that can be implicitly converted into any one of a number of date types as needed. The fact that we see this value as a string is just for our convenience. If we don’t use the literal in any way that compares or uses it as a date type parameter, it will remain just a string of characters.

There are plenty of caveats. For example, some datatypes cannot be implicitly or explicitly converted. Like:

SELECT CAST(0 as datetime2(0));

This will cause:

Msg 529, Level 16, State 1, Line 41 Explicit conversion from data type int to datetime2 is not allowed.

Nor can you do:

SELECT CASE WHEN CAST('2026-02-27' AS date) = 1 THEN 'Equal' ELSE 'Different' END;

Which will throw:

Msg 206, Level 16, State 2, Line 48 Operand type clash: date is incompatible with tinyint

But wait. I thought 1 was treated as an integer? Here it is a tinyint. It is probably something in how a date is implemented? What if I cast it to an int?

SELECT CASE WHEN CAST('2026-02-27' AS date) = cast(1 AS int) 
            THEN 'Equal' ELSE 'Different' END;

Nope:

Msg 206, Level 16, State 2, Line 55 Operand type clash: date is incompatible with int

Note that you could do date math with integers using the old datetime and smalldatetime datatypes. I was never a big fan, but I did write about this back in 2011 because pre-date and datetime2, some operations were not as easy as they are now so it was a really useful technique.

So, how do you know the datatype of an expression?

For example, consider these literals.

• 1
• 'name'
• N'Name'
• 0xABCDEF
• REPLICATE('A',4001)
• REPLICATE(N'A',4001)

We can make a pretty good guess, but not really be totally sure. If you need to know, there are a few ways to go about it. Use
SELECT sys.dm_exec_describe_first_result_set or use a sql_variant type and check its type.

I have written previously about this topic a while back on my Simple Talk blog: Technique and Simple Utility to Determine the Datatype of a Scalar T-SQL Expression. In that post I focussed on using a sql_variant type , but using the sys.dm_exec_describe_first_result_set dynamic management view is far easier. If you want to see how to evaluate the datatype of a sql_variant, that post covers it nicely still.

When I was reading over that blog, Phil Factor had commented asking me why I didn’t use sys.dm_exec_describe_first_result_set. I miss him.

sys.dm_exec_describe_first_result_set was added back in 2012, and this function is useful to see what a query will have as its output. You can pass in any batch of SQL statements that you are keen to check, and it will describe what the first result set will be. There is no description of the second result set, and as it doesn’t execute the query, it will be the first result set possible.

For my needs, you really only need one simple result set of one column. So I could execute:

SELECT system_type_name, is_nullable, *
FROM   sys.dm_exec_describe_first_result_set 
                     ('SELECT 1 + ''One''',null,0) as dedfrs 

To find out what the 1 in the SELECT statement will be treated as. There are a lot of columns returned, which you can see here Learn.Microsoft.com, but for my purposes, all that I really need is the system_type_name and is_nullable. Other columns would be useful if you are analysing the result sets of a procedure, but I am just trying to get the datatype of an expression.

The output is:

system_type_name    is_nullable
------------------- -----------
int                 0

So you could just do the following:

--this is the expression, ignoring outer apostrophes
DECLARE @expression nvarchar(max) = N'1' 
--add the expression to a simple SELECT statement
DECLARE @SQL nvarchar(max) = 'SELECT ' + @expression + ' AS CheckMe'
--then add it to a query:
SELECT
    system_type_name,
    CASE is_nullable WHEN 1 
         THEN 'NULL' ELSE 'NOT NULL' END as nullability
FROM   sys.dm_exec_describe_first_result_set (@SQL,null,0) as dedfrs

There are two parameters for this procedure that let you send in a parameterized statement, as well as to show how the statement would work in a cursor.

Which returns:

system_type_name   nullability
------------------ -----------
int                NOT NULL

So, to make this easy, I will create this stored procedure that takes an expression and gives me the output:

CREATE OR ALTER FUNCTION dbo.ExamineExpression
(
    @Expression nvarchar(max)
) 
RETURNS @Output TABLE
(
    Datatype sysname,
    Nullability varchar(10),
    Expression nvarchar(max)
)
AS
 BEGIN
    --add the expression to a simple SELECT statement
    DECLARE @SQL nvarchar(max) = 'SELECT ' + @expression 
                                             + ' AS CheckMe'
    --then add it to a query:
    insert into @output(Datatype, Nullability, Expression)
    SELECT
        coalesce(system_type_name,'Invalid expression'),
        case when system_type_name is null then 'Error'
             when is_nullable = 1 then 'NULL' 
             WHEN is_nullable = 0 then 'NOT NULL' 
             else 'UNKNOWN' END as nullability,
        @Expression
    FROM   sys.dm_exec_describe_first_result_set (@SQL,null,0) 
                                                         AS dedfrs;
 RETURN;
END

Now, thinking back to the original expressions from the introduction, I tested them all out:

-- 'name'
SELECT CAST(Datatype as nvarchar(20)) as Datatype,
       Nullability,
       Expression
FROM   dbo.ExamineExpression(N'''name''');

This outputs:

Datatype             Nullability Expression
-------------------- ----------- -------------
varchar(4)           NOT NULL    'name'

And you can see after that, we have the other examples discussed earlier:

Datatype             Nullability Expression
-------------------- ----------- -------------
nvarchar(4)          NOT NULL    N'name'
Datatype             Nullability Expression
-------------------- ----------- --------------
varbinary(3)         NOT NULL    0xABCDEF
Datatype             Nullability Expression
-------------------- ----------- -----------------------
varchar(4001)        NULL        REPLICATE('A',4001)
--This one is something that every SQL programmer 
--typically learns the hard way.
Datatype             Nullability Expression
-------------------- ----------- -----------------------
nvarchar(4000)       NULL        REPLICATE(N'A',6001)

If you replicate a non-max string value, more than the maximum non-(max) varchar type can handle, it will stop at 8000 for varchar and 4000 for nvarchar. So you have to do this to get the output you expect:

REPLICATE(CAST(N'A' as nvarchar(max)),6001)

Which you can test using:

SELECT CAST(Datatype as nvarchar(20)) as Datatype,
       Nullability,
       Expression
from   dbo.ExamineExpression('REPLICATE(cast(N''A'' 
                                         AS nvarchar(max)),6001)');

Now it works as expected;

Datatype             Nullability Expression
-------------------- ----------- -----------------------
Invalid expression   Error       REPLICATE(cast(N'A' as nvarchar(max)),6001)

Whatever works in a SQL statement, you can test here:

100.00 * 10.00

Can be tested like this:

SELECT CAST(Datatype as nvarchar(20)) as Datatype,
       Nullability,
       Expression
FROM   dbo.ExamineExpression('100.00 * 10.00');

Which you can see outputs a numeric (10,4) result:

Datatype             Nullability Expression
-------------------- ----------- ----------------
numeric(10,4)        NULL        100.00 * 10.00

Note that what is considered not NULLable is pretty tight. Simple literals (or source columns that are not nullable) are seemingly what is going to be defined as NOT NULL.

A bit of a caveat

A return here does NOT necessarily mean this will be a valid output at execution time. For example, take 1 + 'One'. We already noted that 1 = 'One' will cause an error, and so will:

SELECT 1 + 'One';

As it will throw this error:

Msg 245, Level 16, State 1, Line 212 Conversion failed when converting the varchar value 'One' to data type int.

BUT, this does not mean that the output of the datatype checking expression function will return an error:

SELECT CAST(Datatype as nvarchar(20)) as Datatype,
       Nullability,
       Expression
FROM   dbo.ExamineExpression('1 + ''One''');

This returns:

Datatype    Nullability Expression
----------- ----------- ---------------------
int         NULL        1 + 'One'

This is because datatype precedence will often be a runtime error, not compile time. This isn’t always the case, as this will evaluate to an error:

SELECT CAST(Datatype as nvarchar(20)) as Datatype,
       Nullability,
       Expression
FROM   dbo.ExamineExpression('sysdatetime() + ''One'''); 

This returns:

Datatype             Nullability Expression
-------------------- ----------- -----------------------
Invalid expression   Error       sysdatetime() + 'One'

This is because while integer and string are compatible types:

SELECT sysdatetime() + 'One';

Will throw an error to tell you that datetime2 and varchar are not

Msg 402, Level 16, State 1, Line 247
The data types datetime2 and varchar are incompatible in the add operator.

Summary

Understanding how datatypes are compared and concatenated implicitly (and explicitly) is a very important topic when you are working with data in a SQL process. In SQL Server, there is a very specific way that datatypes are implicitly converted, and it is important to realize it and understand when a comparison will be trouble, particularly when doing comparisons in a WHERE clause.

In this blog, I have shown you how you can interrogate an expression to see what the datatype will be when you do an implicit conversion like 1 + '1' or 1 + 'One'; both of which are valid syntax, but one of which will cause a runtime error.