Solved

Temp table max size

Posted on 2013-12-15
3
866 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 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