Temp Table Data Types

Question

Temp Table Data Types

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

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

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 1

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 12388 More actions · Answer 5

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 More actions · Answer 6

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: 2460 More actions · Answer 7

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

Simone

Sathishk Right there with Babe Points: 738 More actions · Answer 8

Sathishk

Right there with Babe

Points: 738

March 13, 2013 at 9:31 am

#1596584

Good one

Miles Neale SSChampion Points: 13147 More actions · Answer 9

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

Not all gray hairs are Dinosaurs!

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Revenant

SSC-Forever

Points: 42467

March 13, 2013 at 12:10 pm

#1596653

Really new OotD tack - much thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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: 11368 More actions · Answer 12

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: 64790 More actions · Answer 13

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 SSCoach Points: 17191 More actions · Answer 14

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 15

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.

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