Maximum Number of Rows for the Table Value Constructor

(last updated: 2019-05-09 @ 15:50 EST / 2019-05-09 @ 19:50 UTC )

With the release of SQL Server 2008 we gained the ability to specify multiple rows in a single VALUES clause. This can be quite useful as it cuts down on the repetition of INSERT ... VALUES statements, or of SELECT ... UNION ALL SELECT ... UNION ALL ... when creating derived tables of static values. Great.

This construct is called the Table Value Constructor (TVC). I remember testing this when SQL Server 2008 came out. I found that attempting to do the following:

INSERT INTO SchemaName.TableName (Column1Name, Column2Name)
VALUES (1, 'a'), (2, 'b'), (3, 'c'), ..., (2000, 'xyz');

with over 1000 rows in the TVC would receive an error stating that there was a 1000-row limit. Ok, good to know. Wonderful when it can be used, but can’t always be used. Got it.

El Problemo

Several years later (February of 2017), I happened to mention this limitation when answering a question on DBA.StackExchange:

Table Value Constructor as Procedure Parameter

And there it sat, collecting dust for two years and a few months. Until yesterday.

On 2019-05-08, a helpful individual, Michael B, commented on my answer saying that the 1000-row limit only existed when using a TVC as the VALUES clause of an INSERT statement. And, that there was no limit when using a TVC as a derived table.

Could this be true?

First, let’s check the documentation to make sure that I didn’t miss something. It states:

Table value constructors can be used in one of two ways: directly in the VALUES list of an INSERT … VALUES statement, or as a derived table anywhere that derived tables are allowed. Error 10738 is returned if the number of rows exceeds the maximum. To insert more rows than the limit allows, use one of the following methods:

• Create multiple INSERT statements
• Use a derived table
• Bulk import the data by using the bcp utility or the BULK INSERT statement

Hmm… It doesn’t say that the error message is only for when using a TVC as the VALUES clause of an INSERT statement. But, the work arounds are only for INSERT statements, and one of the options is to use a derived table. BUT, this also doesn’t say that there is no limit when using a TVC as a derived table. This would seem to imply no limit when using as a derived table, but mostly if you already knew that’s how it behaved.

Next, we need to run some tests to see how SQL Server actually behaves. To be clear, even if the documentation did clearly state all of the relevant information, it is still important to test in order to verify that the information is actually correct (because sometimes it isn’t).

Tests

Fortunately, testing this should be fairly easy. We only need to check three scenarios:

1. Derived Table: I was told that this had no limit, so we will attempt 200,000 rows.
2. INSERT Directly From TVC: Meaning, INSERT ... VALUES. The documentation doesn’t provide the limit, but I have run into it before and it’s 1000 rows. So we will attempt 1001 rows.
3. INSERT Indirectly From TVC via Derived Table: Meaning, INSERT ... SELECT. If the derived table truly has no limit, then this should work. Besides, the documentation does suggest using a derived table if you receive an error for going over the row limit. We only need to prove that 1001 rows works, but we will attempt 10,001 rows just to be sure.

Derived Table

DECLARE @SQL1 NVARCHAR(MAX) = N',(1)';
SET @SQL1 = REPLICATE(@SQL1, 100000);
SET @SQL1 = N'SELECT COUNT(*) FROM (VALUES (0)'
           + REPLICATE(@SQL1, 2) + N') tab(col);';
EXEC (@SQL1);
-- 200001

The query shown above works with 200,001 rows in the TVC.

INSERT ... VALUES

DECLARE @SQL2 NVARCHAR(MAX) = N',(2)';
SET @SQL2 = N'DECLARE @Test TABLE (Col1 TINYINT NOT NULL);
INSERT INTO @Test (Col1) VALUES (0)' + REPLICATE(@SQL2, 1000) + N';';
EXEC (@SQL2);
/*
Msg 10738, Level 15, State 1, Line XXXXX
The number of row value expressions in the INSERT statement exceeds the
   maximum allowed number of 1000 row values.
*/

The query shown above fails, as expected, with an error message clearly stating the limit. But, just to be sure (since it’s not impossible that the error message could be incorrect), we can try again with only 1000 rows:

DECLARE @SQL2b NVARCHAR(MAX) = N',(2)';
SET @SQL2b = N'DECLARE @Test TABLE (Col1 TINYINT NOT NULL);
INSERT INTO @Test (Col1) VALUES (0)' + REPLICATE(@SQL2b, 999) + N';';
EXEC (@SQL2b);
-- (1000 rows affected)    (in "Messages" tab)

The query shown above succeeds inserting 1000 rows.

INSERT ... SELECT

DECLARE @SQL3 NVARCHAR(MAX) = N',(3)';
SET @SQL3 = N'DECLARE @Test TABLE (Col1 TINYINT NOT NULL);
INSERT INTO @Test (Col1) SELECT tab.col FROM (VALUES (0)'
           + REPLICATE(@SQL3, 10000) + N') tab(col);
SELECT COUNT(*) FROM @Test AS [RowsInsertedIntoTableVariable];';
EXEC (@SQL3);
-- 10001

The query shown above succeeds inserting 10,001 rows.

Conclusion

I ran the tests shown above on SQL Server 2012 SP4, SQL Server 2017 CU14, and SQL Server 2019 CTP 2.5, and the behavior was the same across all three versions.

We have confirmed that the TVC behavior is as follows:

1. When used as the VALUES clause of an INSERT statement, there is a limit of 1000 rows. Going over this limit will result in the following error:

   Msg 10738, Level 15, State 1, Line XXXXX

   The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

2. When used as a derived table, there is no apparent limit to the number of rows, even if the derived table is used as the source for an INSERT statement.

I will submit an update for the documentation to have this info stated explicitly.