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 123»»»

Temp Tables vs. Physical Tables Expand / Collapse
Author
Message
Posted Friday, January 23, 2009 3:25 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
What kind of storage requirements are required when creating a physical table vs. creating a temp table since I know the temp table only exists while the session is open. So for optimal disk space/performance which is better?

thx,

John
Post #642825
Posted Saturday, January 24, 2009 8:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Physical tables are USUALLY created to store the application data. They store all the persistent data that your application needs.

Temporary tables are used to store temporary information. Most of the times you dont need to keep them after you are done with the data. For example, a reporting stored procedure might decide to store the intermediary results in a temp table and process them for better performance.

So use TEMP tables to store all the temporary information and physical tables to store all the REAL data.


.
Post #642976
Posted Saturday, January 24, 2009 8:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
also keep in mind temp tables (@ / # / ## ) are hosted in tempdb. tempdb is used at (sqlserver) instance level, for more than only storing temp tables. (sorting, grouping, ..)

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #642980
Posted Saturday, January 24, 2009 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:39 PM
Points: 36,764, Visits: 31,220
ALZDBA (1/24/2009)
also keep in mind temp tables (@ / # / ## ) are hosted in tempdb. tempdb is used at (sqlserver) instance level, for more than only storing temp tables. (sorting, grouping, ..)


Actually, Temp Tables and Table Variables start out in memory... if they fit, that's as far as they go. If they don't, then they spill into the physical disk.

But, don't take anyone's word for that... instead of sifting through the manure to figure out what the horse was thinking, ask the horse. :P Please read the following Microsoft FAQ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #642985
Posted Sunday, January 25, 2009 3:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
Jeff Moden (1/24/2009)
[quote]... if they fit, that's as far as they go. If they don't, then they spill into the physical disk.


"If" is the unknown over here, so anyone needs to know temptb work different than regular tables.

And actually, it's the KB ref the OP needed.

Thanks for the ref.
Although it didn't pull up the kb.
this url worked for me http://support.microsoft.com/kb/305977/en-us

There is also a WP regarding tempdb. It is called "Working with tempdb in SQL Server 2005" and it contains "WorkingWithTempDB.doc".

general performance guidelines can be found overhere http://support.microsoft.com/kb/110352/


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #643137
Posted Sunday, January 25, 2009 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:39 PM
Points: 36,764, Visits: 31,220
Thanks for the links, Johan... I'll take a look (am I spelling your name correctly?)

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #643166
Posted Sunday, January 25, 2009 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
Jeff Moden (1/25/2009)
Thanks for the links, Johan... I'll take a look (am I spelling your name correctly?)


Yep, Johan is correct.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #643169
Posted Monday, January 26, 2009 2:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
Thank you all for the valuable input and resources.

John
Post #643698
Posted Tuesday, January 27, 2009 7:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:39 PM
Points: 36,764, Visits: 31,220
ALZDBA (1/25/2009)

general performance guidelines can be found overhere http://support.microsoft.com/kb/110352/


Wow... they don't write 'em like that anymore.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #644629
Posted Wednesday, January 28, 2009 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
It's a petty they've put a "Retired KB Content Disclaimer".

Apparently it isn't flashy anymore to summarise basic symple guidelines.

It must all be point and click and it will work..... until one notices errors, locks, unresponsive apps., ...


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #644709
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse