What is the default data-type for columns made from an expression

Question

What is the default data-type for columns made from an expression

Rob

Ten Centuries

Points: 1035
More actions
February 21, 2020 at 2:52 pm
Go to Answer
#3727698
In Microsoft SQL Server Management Studio 2014, when you execute the following statement:
```
SELECT 1 AS [MyColumn1]
```
...or the case expression / code:
```
SELECT CASE WHEN 1 = 1 THEN 1 ELSE 0 END AS [MyColumn2]
```
What is the datatype for the column, or how do I find it out? In both scenarios above the value of 1 could be a tinyint, smallint, int32, int64 - does SSMS default to a particular datatype when not specified?
John Mitchell-245523

SSC Guru

Points: 148809
More actions
February 21, 2020 at 3:03 pm
Answer
#3727704

I don't know, but you could find out by doing a SELECT INTO and inspecting the definition of the ensuing table.
John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rob Ten Centuries Points: 1035 More actions · Answer 1

I don't understand what you mean because if I SELECT INTO an existing table, doesn't that mean the table I am selecting into already has its datatypes defined? So if the example I gave was returning a tinyint, and I select it into a column defined as an INt32 I'd get Int32 and not tinyint?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

INSERT INTO inserts into an existing table. SELECT INTO creates a new table and inserts into that - all in one operation.

John

Rob Ten Centuries Points: 1035 More actions · Answer 3

Thanks John, I didn't know of SELECT INTO and misread it as INSERT INTO.

Doing as you suggested has shown me that in this instance '1' got evaluated as an INT.

select 1 as test into myNewTable

select * from myNewTable

select DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myNewTable' AND COLUMN_NAME = 'test'

ScottPletcher SSC Guru Points: 101126 More actions · Answer 4

FYI, you can do it without creating a table, like so:

SELECT
    SQL_VARIANT_PROPERTY ( MyColumn2, 'BaseType' ) AS MyColumn2_BaseType,
    SQL_VARIANT_PROPERTY ( today, 'BaseType' ) AS today_BaseType
FROM (
    SELECT CAST(CASE WHEN 1 = 1 THEN 1 ELSE 0 END AS sql_variant) AS [MyColumn2], 
        CAST(GETDATE() AS sql_variant) AS today
) AS derived

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".