Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Table Data Types


Temp Table Data Types

Author
Message
Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 2958
Stewart "Arturius" Campbell (3/13/2013)
Interesting question, thanks Dave

Another way to check the detail of the temp table would be to execute
tempdb..sp_help #TempQoD



Now this is very very new thing to me... thank you.

(and for the INTO.. table stuff, I thought it is obvious... like when you use the import/export tool and when you trying to import the data from the excel file it marks all the column as varchar except the first one if there are any number like 1 2 3... it marks as int... and if we need the col type to any specific then the tool allows us to modify the value accordingly. As the source data is like a variant, I thought it will be difficult to sql to pick the exact datatype to meet the source and the destiny.... until proven otherwise. This is very interesting, thank you for posting the question, Dave)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 2958
Carlo Romagnano (3/13/2013)
okbangas (3/13/2013)
Learned something new here, and by the way, the reference should be:

Date, Time, and Timestamp Escape Sequences

Good!


+1

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2439 Visits: 2763
Raghavendra Mudugal (3/13/2013)
Stewart "Arturius" Campbell (3/13/2013)
Interesting question, thanks Dave

Another way to check the detail of the temp table would be to execute
tempdb..sp_help #TempQoD



Now this is very very new thing to me... thank you.

(and for the INTO.. table stuff, I thought it is obvious... like when you use the import/export tool and when you trying to import the data from the excel file it marks all the column as varchar except the first one if there are any number like 1 2 3... it marks as int... and if we need the col type to any specific then the tool allows us to modify the value accordingly. As the source data is like a variant, I thought it will be difficult to sql to pick the exact datatype to meet the source and the destiny.... until proven otherwise. This is very interesting, thank you for posting the question, Dave)

New thing for me too :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2439 Visits: 2763
Stewart "Arturius" Campbell (3/13/2013)
Interesting question, thanks Dave

Another way to check the detail of the temp table would be to execute
tempdb..sp_help #TempQoD


+1 :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
db4breakfast
db4breakfast
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 389
Stewart "Arturius" Campbell (3/13/2013)
Interesting question, thanks Dave

Another way to check the detail of the temp table would be to execute
tempdb..sp_help #TempQoD



I often use sp_help to get db object info, but never try the query on explanation. That query can be handy since it provides more info about a particular table. Thank Dave!

Derived on the query on the explanation, this query can be used to object info for other db objects such as trigger and stored procedure.

USE tempdb;
go
SELECT *
FROM sys.objects o
LEFT JOIN sys.columns AS c ON c.object_id = o.object_id
LEFT JOIN sys.types AS t ON t.system_type_id = c.system_type_id
WHERE o.object_id = object_id('#TempQoD');



--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1145
Great question. I also got the answer by running: " EXEC tempdb..sp_help '#TempQoD'; "
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1062
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
Primo Dang
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1940 Visits: 1396
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
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4111 Visits: 72512
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 Smile



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 2714
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search