Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Temp Table Data Types Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 2:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,038, Visits: 2,342
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.
Post #1430250
Posted Wednesday, March 13, 2013 2:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,038, Visits: 2,342
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.
Post #1430257
Posted Wednesday, March 13, 2013 4:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1430279
Posted Wednesday, March 13, 2013 4:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1430280
Posted Wednesday, March 13, 2013 5:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:38 AM
Points: 386, Visits: 366
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.
Post #1430327
Posted Wednesday, March 13, 2013 6:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Great question. I also got the answer by running: " EXEC tempdb..sp_help '#TempQoD'; "
Post #1430349
Posted Wednesday, March 13, 2013 7:23 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:15 AM
Points: 1,447, Visits: 1,059
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.
Post #1430374
Posted Wednesday, March 13, 2013 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 1,860, Visits: 1,333
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).
Post #1430375
Posted Wednesday, March 13, 2013 7:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
For tips on how to post your problems
Post #1430381
Posted Wednesday, March 13, 2013 8:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 1,366, Visits: 1,730
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.
Post #1430422
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse