Temporary Table Record Limit in SQL Server

Temporary table record limit in Sql server

The differences between tempdb and any other database are minimal, especially when it comes to limits.

If you can store it in a user table, you can store it in a temporary table as well. It does not have to fit into RAM as the tempdb is stored on disk just like any other database (only with more aggressive caching and less logging).

Source: http://msdn.microsoft.com/en-us/library/ms190768.aspx

Maximum Number of Records a table variable can have in SQL Server

As such the official MSDN site where the Maximum Capacity Specifications for SQL Server there is no such upper limit defined for table variables because it depends on the database size and the free memory available for the storage. You can also refer the MSDN forum discussing the same; Maximum Capicity of Table Variable

Do I need to go with Table Variable or Temporary Table?

You can use any of them as there is no such golden rule as to when you should use Table variable and when to use Temporary variables. There are some references which can be helpful to understand it more:

• Temporary Tables vs. Table Variables and Their Effect on SQL Server
  Performance
• When should I use a table variable vs temporary table in sql
  server?
• TempDB:: Table variable vs local temporary table

Oracle global temporary tables - maximum number of records?

there is no hard limit to the number of records in a global temporary table. Data will eventually be written to disk and therefore will be subject to read/write speed when you insert/update your data or query the table. You would expect performance (i.e data modification and data access) to be roughly of the same level as a regular table -- a little faster since there is less redo generated.

You can add indexes to a global temporary table to ease retrieval of a subset of rows (it will obviously slow down insert and consume more temp space, this is a trade-off)

Not able to INSERT more than 1K records to temp table in SQL

Depending on where your actual values for your insert are coming from could change what/how to do the INSERT part. Are you doing the insert from a single connection or opening and closing a connection to use the data? This would change how you could do it as well.

You are using a table variable (the @ in front of name shows that). See my notes and code below for what is wrong and how to fix it.

use DatabaseName 

SET NOCOUNT ON 

-- this is creating  permeant table not a temp table or table variable.
CREATE Table TempRefundDetails (PolicyNumber NVARCHAR(10))

-- this inserts into a table variable not temp table
INSERT INTO @TempRefundDetails (PolicyNumber)

-- this will create a temp table
IF OBJECT_ID('tempdb..#TempRefundDetails') IS NOT NULL 
    DROP TABLE #TempRefundDetails

CREATE TABLE #TempRefundDetails ( 
    PolicyNumber NVARCHAR(10)
)

INSERT INTO #TempRefundDetails (
    PolicyNumber
)
VALUES (), () -- and so on

OR select from another table source directly into the table

INSERT INTO #TempRefundDetails (
    PolicyNumber
)
Select PolicyNumber
From SomeTableNameHere

SQL Server: the maximum number of rows in table

It's hard to give a generic answer to this. It really depends on number of factors:

• what size your row is
• what kind of data you store (strings, blobs, numbers)
• what do you do with your data (just keep it as archive, query it regularly)
• do you have indexes on your table - how many
• what's your server specs

etc.

As answered elsewhere here, 100,000 a day and thus per table is overkill - I'd suggest monthly or weekly perhaps even quarterly. The more tables you have the bigger maintenance/query nightmare it will become.

Do SQL Server inline queries create temp tables under the hood

SQL Server can employ a technique called Spooling wherein it creates a structure similar to a temp table within tempdb.

It will do this, in general, where it has an intermediate result that it needs to use more than once and it believes that the cost of storing and retrieving this data will be lower than the cost of re-generating the intermediate result.

You may be able to identify that this is happening by generating an Estimated Execution Plan for your query. Unfortunately, there's no guarantee that the estimated plans will be identical to the actual plans that the query generates.

---

Related Topics