[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1161
  • Last Modified:

Temp table max size

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
ravichand-sql
Asked:
ravichand-sql
2 Solutions
 
guswebbCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
John_VidmarCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now