Maximum Number of Rows for the Table Value Constructor

(last updated: 2019-05-23 @ 19:22 EST / 2019-05-23 @ 23:22 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. At the top of the page it states:

… The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause.

Then, in the “Limitations and Restrictions” section, 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… the first quote mentions the MERGE statement while the second quote does not. Does this mean that the USING clause of the MERGE statement is a third case that might have different behavior, or not? The documentation for the MERGE statement, in the definition of the USING clause in the “Arguments” section, directs the reader to the documentation for the FROM clause. This implies that this particular usage is also a derived table, and hence not treated as a third case. But, we need to test it to know for sure.

Also, 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 four 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.
  4. MERGE From TVC: The TVC documentation implies that this case might be handled differently than the other two. We will try inserting 20,000 rows. Most likely the TVC will be treated as a derived table and won’t be limited to 1000 rows.

 

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.

 

MERGE

DECLARE @SQL4 NVARCHAR(MAX) = N',(4)';
SET @SQL4 = N'DECLARE @Test TABLE (Col1 TINYINT NOT NULL);

MERGE INTO @Test AS [Target]
USING (VALUES (0)' + REPLICATE(@SQL4, 20000) + N') AS [Source]([Value])
   ON [Target].[Col1] = 55
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Col1]) VALUES ([Source].[Value]);

SELECT COUNT(*) FROM @Test AS [RowsInsertedIntoTableVariable];';

EXEC (@SQL4);
-- 20001

The query shown above succeeds inserting 20,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.
  3. The USING clause of the MERGE statement is the same as (or at least is treated the same as) a FROM clause, which means that a TVC used here is a derived table. Hence, there is no special handling for this case.

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

 

UPDATE 2019-05-10

I have submitted Pull Request #2115 for this update.

 

UPDATE 2019-05-23

Changes made via PR #2115 have been merged and will soon be live.

3 thoughts on “Maximum Number of Rows for the Table Value Constructor”

  1. for what is worth there’s also no limit (other than the buffer length for SQL) if you use

    SELECT … FROM (VALUES(),(),(),(),()…)

    Like

    1. Hi Noel, and thanks for commenting. Yes, if I am understanding you correctly, I believe this is the first test case shown in the post, labeled as “Derived Table”. If I misunderstood you, please let me know so that I can add this to the post. Take care, Solomon…

      Like

Thoughts? Questions? Comments? Suggestions? Words of praise?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s