Temp Table Data Types

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Great question. I also got the answer by running: " EXEC tempdb..sp_help '#TempQoD'; "

  • Rose Bud

    SSCrazy

    Points: 2971

    I nominate this question for Best Question of the Month! Well-written and has real world implications if not understood.

    But, I will point out this is not just true for the creation of temp tables (as the title "Temp Table Data Types" suggests.) This happens when you create regular, permanent tables, too, which I think it the most hazardous reason of all not to use "Select Into" to create a new table.

  • Primo Dang

    SSCrazy

    Points: 2643

    This behaviour isn't exclusive to select into, it has to do with how SQL Server determines data types from values that aren't explicitly typed. For example:

    select case when 1 = 0 then 0 else 'test' end

    The code above will return a conversion error from string to int, because the data type was inferred to be int (of course the definition of which value should be converted has to do with data type precedence).

  • mtassin

    SSC-Insane

    Points: 23099

    bitbucket-25253 (3/12/2013)


    Nice question .. learned something from it ... thanks

    Same here.... I learned how much I hate depending on SQL to determine my data types 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • sknox

    SSChampion

    Points: 12292

    I wasn't sure what the confusion was here. All of the values in the example SELECT...INTO are constants. When you create a constant, the format you express it in tells SQL Server what data type to use. It's that simple.

    Then, I read Microsoft's documentation on constants here: http://msdn.microsoft.com/en-us/library/ms179899.aspx .

    If you value your sanity DO NOT READ MICROSOFT'S DOCUMENTATION ON CONSTANTS. They manage to get it COMPLETELY WRONG. This is the worst Microsoft documentation I have seen to date.

    It starts with this: "The format of a constant depends on the data type of the value it represents." That's completely back-to-front. You create the data type and express it in a format first, then SQL Server interprets what it is based on the format you provide. The correct statement is "The data type of a constant is based on the format passed to SQL Server." It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.

    What this means for this question is that '15 Jan 2013', being formatted as a varchar, is interpreted by SQL Server as a varchar. It will be treated as varchar by SQL Server until you tell SQL Server to treat it as something else, either by an explicit CAST/CONVERT or by assigning it to an expression already typed as something else.

    It's that simple.

  • Jason Whitish

    SSChasing Mays

    Points: 642

    I'm particularly curious about the use of the date escape sequence (e.g. {d '2012-11-15'}).

    Is it good practice to use those if you're having to hard code dates in procedures, etc.?

  • sburcombe

    SSCrazy

    Points: 2450

    Great question. I hadn't heard of escape sequences, so learned something today.

    Simone
  • Sathishk

    SSChasing Mays

    Points: 628

    Good one

  • Miles Neale

    SSChampion

    Points: 13147

    Good question and a help in understanding the mind of SQL.

    Not all gray hairs are Dinosaurs!

  • Revenant

    SSC-Forever

    Points: 42467

    Really new OotD tack - much thanks!

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    A useful reference that should perhaps have been in the explanation is the BOL datetime page which has a information about the ODBC timestamp literals with their literal_type fields such as the one - {d '2012-11-15'} - used in this QotD. All ODBC timestamp literals represent SQL datetime values, even when the literal_type specifies "date only" (as in today's example) or "time only". These are the only datetime literals recognised by T-SQL. As far as I know T-SQL recognises no date literals, no time literals, and no datetime2 literals, so it is not possible to create a column which has any of those types by using select into with literal constant values.

    Tom

  • Toreador

    SSChampion

    Points: 11256

    sknox (3/13/2013)


    It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.

    Blimey. That really is quite special. Presumably they were trying to say something that was actually true, but I'm struggling to think of anything plausible...

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Toreador (3/14/2013)


    sknox (3/13/2013)


    It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.

    Blimey. That really is quite special. Presumably they were trying to say something that was actually true, but I'm struggling to think of anything plausible...

    What that page is trying to describe is how implicit conversions of string constants to date are handled. These are very common in real code. For instance, you can have an INSERT or UPDATE with a string constant for a datetime column, or a SET. Or you use a DATEDIFF or DATEADD function with a string constant to represent one of the dates.

    All these cases need string constants, because unlike Access, T-SQL doesn't have date constants. (And unfortunately, there are millions of lines of code in the real world where this string constant to date conversion is based on unreliable formats. *sigh*)

    EDIT: And the page mentioned by sknox is guilty of that as well. Two examples use December 5th, 1985. The third example suddenly switches to December 5th, 2085. And the last example then introduces May 12th, 2085. At least, that is how I would interpret the given examples. (I am quite sure the author of the page would disagree).

    Check http://www.karaszi.com/sqlserver/info_datetime.asp - even though it is quite outdated (the newer date/time types are not covered, as far as I know), most of the information is still very valid.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Ken Wymore

    SSCoach

    Points: 16588

    Very nice question. The escape sequence for that ODBC date was new to me.

  • Jeff Moden

    SSC Guru

    Points: 996622

    Dineshbabu (3/12/2013)


    One easiest way to avoid this confusion is .. don't use SELECT * INTO.

    Always prefer CREATE TABLE..

    Ah, not so fast there. SELECT/INTO is, depending on what your needs are, is an incredibly high performance tool. To adopt a rule of thumb of not using SELECT/INTO is a mistake that can leave you out in the cold performance wise. Like all else in SQL Server, "It Depends".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 37 total)

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