(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.
Several years later (February of 2017), I happened to mention this limitation when answering a question on DBA.StackExchange:
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).
Fortunately, testing this should be fairly easy. We only need to check four scenarios:
- Derived Table: I was told that this had no limit, so we will attempt 200,000 rows.
INSERTDirectly 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.
INSERTIndirectly 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.
MERGEFrom 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.
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.
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.
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:
- When used as the
VALUESclause of an
INSERTstatement, there is a limit of 1000 rows. Going over this limit will result in the following error:
- 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
USINGclause of the
MERGEstatement is the same as (or at least is treated the same as) a
FROMclause, 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.
I have submitted Pull Request #2115 for this update.
Changes made via PR #2115 have been merged and will soon be live.