VALUES limit

Question

VALUES limit

Steve Jones - SSC Editor

SSC Guru

Points: 736255
More actions
September 15, 2016 at 10:53 pm

#75749

Comments posted to this topic are about the item VALUES limit
September 16, 2016 at 12:53 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

paul.knibbs SSCoach Points: 15320 More actions · Answer 1

I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?

RoNoS SSC Eights! Points: 956 More actions · Answer 2

Same as 'Select top 1000' default value. Or 'Edit top 200'. Where does those numbers come from?

Gareth Davison Right there with Babe Points: 723 More actions · Answer 3

paul.knibbs (9/16/2016)
I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?

I suspect it is arbitrary, since you can only insert 1,000 rows if you using the following:

INSERT [Table] (Column)

VALUES (1), (1), (1)....(1);

Yet if you use the derived table syntax:

INSERT [Table] (Column)

SELECT N

FROM (VALUES (1), (1), (1)....(1)) AS t (N);

Then there is no limit, since the limit only appiies when using it directly into the insert list. I tested the latter up to 1,000,000 rows and it still worked fine:

DECLARE @i INT = 100000, @sql NVARCHAR(MAX) = '';

SET @sql = CONCAT('DECLARE @T TABLE (N INT); INSERT @T (N) SELECT N FROM (VALUES ',

STUFF((SELECT TOP (@i) ',(1)'

FROM sys.all_objects a, sys.all_objects b

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),

') t (N);');

EXECUTE sp_executesql @sql;

BrainDonor SSCoach Points: 19254 More actions · Answer 4

RoNoS (9/16/2016)
Same as 'Select top 1000' default value. Or 'Edit top 200'. Where does those numbers come from?

They are defaults within Tools/Options/SQL Server Object Explorer/Commands (in SSMS 2014 - may be elsewhere in other versions) and therefore configurable.

Steve Hall
Linkedin
Blog Site

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 5

I've been hit by this before, to the extend that I created a Uservoice suggestion for SQLPrompt to give warnings about exceeding this limit... https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/15205332-warning-when-insert-statement-exceeds-limit-of-row

Thomas Rushton
blog: https://thelonedba.wordpress.com

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 6

gareth.davison (9/16/2016)
paul.knibbs (9/16/2016)
I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?
I suspect it is arbitrary, since you can only insert 1,000 rows if you using the following:
INSERT [Table] (Column)
VALUES (1), (1), (1)....(1);
Yet if you use the derived table syntax:
INSERT [Table] (Column)
SELECT N
FROM (VALUES (1), (1), (1)....(1)) AS t (N);
Then there is no limit, since the limit only appiies when using it directly into the insert list. I tested the latter up to 1,000,000 rows and it still worked fine:
DECLARE @i INT = 100000, @sql NVARCHAR(MAX) = '';
SET @sql = CONCAT('DECLARE @T TABLE (N INT); INSERT @T (N) SELECT N FROM (VALUES ',
STUFF((SELECT TOP (@i) ',(1)'
FROM sys.all_objects a, sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
') t (N);');
EXECUTE sp_executesql @sql;

+1

Infact, I never realized of this limit, because I always use this syntax: INSERT INTO Table SELECT ....

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 7

From BOL:

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 8

BrainDonor (9/16/2016)
They are defaults within Tools/Options/SQL Server Object Explorer/Commands (in SSMS 2014 - may be elsewhere in other versions) and therefore configurable.

This affects the defaults within SSMS sessions for the Edit Top <n> or Select Top <n> rows, but not the limit on the VALUES statement. That would have be server specific for all sessions. If configurable, I'd be interested in knowing where or how to change it.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Strangely, I knew this one off the top of my head. I haven't hit it since yesterday when building a values clause from data in an Excel file.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Sean Lange (9/16/2016)
I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.

Yeah, I don't like doing it, but it's sometimes easier for the one-time tasks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Ed Wagner (9/16/2016)
Sean Lange (9/16/2016)
I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.
Yeah, I don't like doing it, but it's sometimes easier for the one-time tasks.

Of course!!! We all have those one-off tasks now and then where you just plug your nose and "git 'er dun". 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP