Solved

Temp table max size

Posted on 2013-12-15
3
890 Views
Last Modified: 2013-12-23
Hello Experts,

I would like to get some insight about the temp tables in sql server. I am using sql server 2012. I have a temp table with 120 columns, I was wondering how many columns a temp table can handle and what is the limit of number of records that can be stored in the temp table.

Thanks in advance!!
0
Comment
Question by:ravichand-sql
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Assisted Solution

by:guswebb
guswebb earned 250 total points
ID: 39720172
Temp tables are created in the tempdb database so actually exist on disk like any other user table. SQL Server limitations can be seen here http://msdn.microsoft.com/en-us/library/ms143432.aspx

You are most likely to run out of disk space before you hit any of these limits I would expect.

Columns = 30,000 for wide table, or 1,024 for non wide table.
Rows = limited only by your disk space.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39720174
The differences between tempdb and any other database are minimal, especially when it comes to limits, so that applies to #tmp tables vs. physical tables.   So, #tmp tables are only limited by disk space, and SQL Server limitations. of 1024 columns and rows only limited by disk space.

What might be a more appropriate conversation is why would you want to take a set that is 120 column x however many rows, and make it a temp table vs. a physical table?
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39721507
Your data-page-size limits the size of each record contained in your table configuration.  Example, if you have a 4k data-page then the sum of the fixed-length columns may not exceed that limit (of course, there is data-page overhead, so you will not get exactly 4096 bytes for data).  So, in your example, 120 columns is achievable if you don't have many big char fields.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question