Column constructor Maximum row restriction in Select

Question

I have a Table Valued Constructor through which I fetch around 1 million records. It will be used to update another table.

 SELECT * FROM (VALUES (100,200,300), (100,200,300), (100,200,300), (100,200,300), ..... ..... --1 million records (100,200,300)) tc (proj_d, period_sid, val) 

Here is my original request: https://www.dropbox.com/s/ezomt80hsh36gws/TVC.txt?dl=0#

When I do the select above, it just shows an Error Request with any error message.

Update:. I tried to catch the error message or error number using the TRY/CATCH block, but do not use the same error as the previous image

 BEGIN try SELECT * FROM (VALUES (100,200,300), (100,200,300), (100,200,300), (100,200,300), ..... ..... --1 million records (100,200,300)) tc (proj_d, period_sid, val) END try BEGIN catch SELECT Error_number(), Error_message() END catch 

Why it fails, is there any limit for a constructor with table input in select . I know that for Insert this is 1000 , but I choose here.

Answer 1

There is no hard, hard restriction (65,536 * The size of the 4 KB network packet is 268 MB, and the script length does not come close to this), although it is not recommended to use this method for a large number of lines.

The error you see is thrown by client tools, not SQL Server. If you build SQL String in dynamic SQL compilation, you can at least start successfully

 DECLARE @SQL NVARCHAR(MAX) = '(100,200,300), '; SELECT @SQL = 'SELECT * FROM (VALUES ' + REPLICATE(@SQL, 1000000) + ' (100,200,300)) tc (proj_d, period_sid, val)'; SELECT @SQL AS [processing-instruction(x)] FOR XML PATH('') SELECT DATALENGTH(@SQL) / 1048576.0 AS [Length in MB] --30.517705917 EXEC(@SQL); 

Although I killed the above after ~ 30 minutes of compilation time, and it still hasn't produced a series. Literal values ​​must be stored inside the plan itself, since the constant table and SQL Server spend a lot of time trying to get properties about them as well.

SSMS is a 32-bit application and throws a std::bad_alloc when analyzing a package

He is trying to push an element onto a Token vector that has reached bandwidth, and his attempt to resize failed due to the inaccessibility of a sufficiently large adjacent memory area. Thus, a statement never even makes it to the server.

Vector capacity increases by 50% each time (i.e. after the sequence is here ). The capacity with which the vector should grow depends on how the code is laid out.

The following must be increased from 19 to 28.

 SELECT * FROM (VALUES (100,200,300), (100,200,300), (100,200,300), (100,200,300), (100,200,300), (100,200,300)) tc (proj_d, period_sid, val) 

and the next one requires size 2

 SELECT * FROM (VALUES (100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300)) tc (proj_d, period_sid, val) 

Below capacity> 63 and <= 94 is required.

 SELECT * FROM (VALUES (100, 200, 300), (100, 200, 300), (100, 200, 300), (100, 200, 300), (100, 200, 300), (100, 200, 300) ) tc (proj_d, period_sid, val) 

For a million lines laid out as in case 1, the vector capacity should grow to 3543 306.

You may find that any of the following will enable client-side parsing to succeed.

• Reduce the number of line breaks.
• Restarting SSMS in the hope that a request for more contiguous memory will succeed when less fragmentation of the address space occurs.

However, even if you successfully send it to the server, it will only end up killing the server during the creation of the execution plan, as discussed above.

You will be much better off using the import import wizard to load the table. If you have to do this in TSQL, you will find that you break it into smaller batches and / or use a different method, for example, XML shredding will work better than table constructors. For example, the next 13 seconds are executed on my computer (although if you use SSMS, you still have to split it into several batches, rather than sticking a massive XML string literal).

 DECLARE @S NVARCHAR(MAX) = '<x proj_d="100" period_sid="200" val="300" /> ' ; DECLARE @Xml XML = REPLICATE(@S,1000000); SELECT x.value('@proj_d','int'), x.value('@period_sid','int'), x.value('@val','int') FROM @Xml.nodes('/x') c(x) 

Answer 2

Check this for query limits: https://msdn.microsoft.com/en-us/library/ms143432.aspx