CODEWITHSUNDEEP
sql-server
Rick
Rick

Reputation: 1569

Limit for temp tables in SQL Server

Is there any limit for the number of temp tables to create in a stored procedure? One of my functionality is so huge with so many hanging modules. The best way I found was to create temp tables for the hanging modules and pull data out from those tables. Right now, there are 22 temp tables in that stored procedure of 2500 lines. Is there any potential limit or performance threat related to count of temp tables?

Upvotes: 1

Views: 2401

Answers (1)

Xedni
Xedni

Reputation: 4695

Practically speaking there's no limit to how many temp tables you can have simultaneously.

You would probably have to have an out of control process spewing out and populating temp tables over and over to run into issues related simply to the overhead of the number of temp tables.

You probably want to pay more attention to how much data is moving around in them though as tempdb resources are shared across all databases, and you could end up monopolizing things like IO and CPU if you just go hog wild. But if you need 22 temp tables, you need 22 temp tables. You can monopolize your resources against a single temp table as easily, so it's not a magic bullet simply to have fewer tables. So I wouldn't lose much sleep over that aspect of it. The footprint of each additional temp table is pretty negligible. They do maintain their own statistics, so that is one factor that might come into play (again, if you were to go absolutely nuts with it) but that's about all I can think of.

Upvotes: 1

Related Questions