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

  • r.gall

    Ten Centuries

    Points: 1030

    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: 148671

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

    John

  • r.gall

    Ten Centuries

    Points: 1030

    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: 148671

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

    John

  • r.gall

    Ten Centuries

    Points: 1030

    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: 98401

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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