November 14, 2022 at 12:00 am
Comments posted to this topic are about the item The VALUES Limit
November 14, 2022 at 12:17 am
answer is not always correct on this case.
November 14, 2022 at 1:46 am
answer is not always correct on this case.
Interesting... When and how is it incorrect... in any case?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2022 at 7:56 am
A table valued constructor is only limited to 1000 rows when used directly as a source for an insert statement. When used to create a derived table there is no limit. From the documentation:
Limitations and Restrictions
When used as a derived table, there is no limit to the number of rows.
When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1000 rows. Error 10738 is returned if the number of rows exceeds the maximum.
So trhere are multiple "correct" answers to this question, and I was pretty sure that whichever answer I chose, the other one would be the one marked as correct.
November 14, 2022 at 8:56 am
The question is not precise enough to give an answer without doubt (derived table vs insert statement)!
November 14, 2022 at 11:14 am
frederico_fonseca wrote:answer is not always correct on this case.
Interesting... When and how is it incorrect... in any case?
I didn't give more details just to allow others to try and answer it without knowing the answer.
but the 2 other posts done after do clarify why the "correct" answer is not always the correct one.
November 14, 2022 at 2:05 pm
The preferred answer of 1,000 is incorrect. The 1,000 rows only applies when you are doing a direct INSERT.
While that may be the most common use of a table value constructor, it is not the only use.
To test, simply use a "SELECT * FROM (VALUES..." and you'll find that you can use far more than 1,000 values.
November 14, 2022 at 2:20 pm
Here is a quick demo using 1,200 values...
SELECT
tvc.int_val
FROM
( VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),
(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),
(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),
(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),
(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),
(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),
(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),
(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),
(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),
(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),
(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),
(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),
(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),
(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),
(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),
(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),
(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),
(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),
(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),
(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),
(253),(254),(255),(256),(257),(258),(259),(260),(261),(262),(263),(264),
(265),(266),(267),(268),(269),(270),(271),(272),(273),(274),(275),(276),
(277),(278),(279),(280),(281),(282),(283),(284),(285),(286),(287),(288),
(289),(290),(291),(292),(293),(294),(295),(296),(297),(298),(299),(300),
(301),(302),(303),(304),(305),(306),(307),(308),(309),(310),(311),(312),
(313),(314),(315),(316),(317),(318),(319),(320),(321),(322),(323),(324),
(325),(326),(327),(328),(329),(330),(331),(332),(333),(334),(335),(336),
(337),(338),(339),(340),(341),(342),(343),(344),(345),(346),(347),(348),
(349),(350),(351),(352),(353),(354),(355),(356),(357),(358),(359),(360),
(361),(362),(363),(364),(365),(366),(367),(368),(369),(370),(371),(372),
(373),(374),(375),(376),(377),(378),(379),(380),(381),(382),(383),(384),
(385),(386),(387),(388),(389),(390),(391),(392),(393),(394),(395),(396),
(397),(398),(399),(400),(401),(402),(403),(404),(405),(406),(407),(408),
(409),(410),(411),(412),(413),(414),(415),(416),(417),(418),(419),(420),
(421),(422),(423),(424),(425),(426),(427),(428),(429),(430),(431),(432),
(433),(434),(435),(436),(437),(438),(439),(440),(441),(442),(443),(444),
(445),(446),(447),(448),(449),(450),(451),(452),(453),(454),(455),(456),
(457),(458),(459),(460),(461),(462),(463),(464),(465),(466),(467),(468),
(469),(470),(471),(472),(473),(474),(475),(476),(477),(478),(479),(480),
(481),(482),(483),(484),(485),(486),(487),(488),(489),(490),(491),(492),
(493),(494),(495),(496),(497),(498),(499),(500),(501),(502),(503),(504),
(505),(506),(507),(508),(509),(510),(511),(512),(513),(514),(515),(516),
(517),(518),(519),(520),(521),(522),(523),(524),(525),(526),(527),(528),
(529),(530),(531),(532),(533),(534),(535),(536),(537),(538),(539),(540),
(541),(542),(543),(544),(545),(546),(547),(548),(549),(550),(551),(552),
(553),(554),(555),(556),(557),(558),(559),(560),(561),(562),(563),(564),
(565),(566),(567),(568),(569),(570),(571),(572),(573),(574),(575),(576),
(577),(578),(579),(580),(581),(582),(583),(584),(585),(586),(587),(588),
(589),(590),(591),(592),(593),(594),(595),(596),(597),(598),(599),(600),
(601),(602),(603),(604),(605),(606),(607),(608),(609),(610),(611),(612),
(613),(614),(615),(616),(617),(618),(619),(620),(621),(622),(623),(624),
(625),(626),(627),(628),(629),(630),(631),(632),(633),(634),(635),(636),
(637),(638),(639),(640),(641),(642),(643),(644),(645),(646),(647),(648),
(649),(650),(651),(652),(653),(654),(655),(656),(657),(658),(659),(660),
(661),(662),(663),(664),(665),(666),(667),(668),(669),(670),(671),(672),
(673),(674),(675),(676),(677),(678),(679),(680),(681),(682),(683),(684),
(685),(686),(687),(688),(689),(690),(691),(692),(693),(694),(695),(696),
(697),(698),(699),(700),(701),(702),(703),(704),(705),(706),(707),(708),
(709),(710),(711),(712),(713),(714),(715),(716),(717),(718),(719),(720),
(721),(722),(723),(724),(725),(726),(727),(728),(729),(730),(731),(732),
(733),(734),(735),(736),(737),(738),(739),(740),(741),(742),(743),(744),
(745),(746),(747),(748),(749),(750),(751),(752),(753),(754),(755),(756),
(757),(758),(759),(760),(761),(762),(763),(764),(765),(766),(767),(768),
(769),(770),(771),(772),(773),(774),(775),(776),(777),(778),(779),(780),
(781),(782),(783),(784),(785),(786),(787),(788),(789),(790),(791),(792),
(793),(794),(795),(796),(797),(798),(799),(800),(801),(802),(803),(804),
(805),(806),(807),(808),(809),(810),(811),(812),(813),(814),(815),(816),
(817),(818),(819),(820),(821),(822),(823),(824),(825),(826),(827),(828),
(829),(830),(831),(832),(833),(834),(835),(836),(837),(838),(839),(840),
(841),(842),(843),(844),(845),(846),(847),(848),(849),(850),(851),(852),
(853),(854),(855),(856),(857),(858),(859),(860),(861),(862),(863),(864),
(865),(866),(867),(868),(869),(870),(871),(872),(873),(874),(875),(876),
(877),(878),(879),(880),(881),(882),(883),(884),(885),(886),(887),(888),
(889),(890),(891),(892),(893),(894),(895),(896),(897),(898),(899),(900),
(901),(902),(903),(904),(905),(906),(907),(908),(909),(910),(911),(912),
(913),(914),(915),(916),(917),(918),(919),(920),(921),(922),(923),(924),
(925),(926),(927),(928),(929),(930),(931),(932),(933),(934),(935),(936),
(937),(938),(939),(940),(941),(942),(943),(944),(945),(946),(947),(948),
(949),(950),(951),(952),(953),(954),(955),(956),(957),(958),(959),(960),
(961),(962),(963),(964),(965),(966),(967),(968),(969),(970),(971),(972),
(973),(974),(975),(976),(977),(978),(979),(980),(981),(982),(983),(984),
(985),(986),(987),(988),(989),(990),(991),(992),(993),(994),(995),(996),
(997),(998),(999),(1000),(1001),(1002),(1003),(1004),(1005),(1006),(1007),(1008),
(1009),(1010),(1011),(1012),(1013),(1014),(1015),(1016),(1017),(1018),(1019),(1020),
(1021),(1022),(1023),(1024),(1025),(1026),(1027),(1028),(1029),(1030),(1031),(1032),
(1033),(1034),(1035),(1036),(1037),(1038),(1039),(1040),(1041),(1042),(1043),(1044),
(1045),(1046),(1047),(1048),(1049),(1050),(1051),(1052),(1053),(1054),(1055),(1056),
(1057),(1058),(1059),(1060),(1061),(1062),(1063),(1064),(1065),(1066),(1067),(1068),
(1069),(1070),(1071),(1072),(1073),(1074),(1075),(1076),(1077),(1078),(1079),(1080),
(1081),(1082),(1083),(1084),(1085),(1086),(1087),(1088),(1089),(1090),(1091),(1092),
(1093),(1094),(1095),(1096),(1097),(1098),(1099),(1100),(1101),(1102),(1103),(1104),
(1105),(1106),(1107),(1108),(1109),(1110),(1111),(1112),(1113),(1114),(1115),(1116),
(1117),(1118),(1119),(1120),(1121),(1122),(1123),(1124),(1125),(1126),(1127),(1128),
(1129),(1130),(1131),(1132),(1133),(1134),(1135),(1136),(1137),(1138),(1139),(1140),
(1141),(1142),(1143),(1144),(1145),(1146),(1147),(1148),(1149),(1150),(1151),(1152),
(1153),(1154),(1155),(1156),(1157),(1158),(1159),(1160),(1161),(1162),(1163),(1164),
(1165),(1166),(1167),(1168),(1169),(1170),(1171),(1172),(1173),(1174),(1175),(1176),
(1177),(1178),(1179),(1180),(1181),(1182),(1183),(1184),(1185),(1186),(1187),(1188),
(1189),(1190),(1191),(1192),(1193),(1194),(1195),(1196),(1197),(1198),(1199),(1200)
) tvc (int_val);
November 14, 2022 at 2:29 pm
And just for the fun of it, I just did a successful test with 120,000 values.
November 14, 2022 at 3:15 pm
"What's a table valued constructor?" was definitely my "correct" answer today! Learned something.
November 14, 2022 at 3:59 pm
This ol' man learned something new today thanks to the replies above. Thanks, folks. Special thanks to Jason Long for posting demonstrative code to drive the point home.
So the correct answer should have been "It Depends". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2022 at 6:12 pm
Just keep in mid that, "just because you can, doesn't mean you should"... Super long value lists can really eat up compile memory and compile time.
If you want to additional tests, here is the code I used to generate the test values...
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP(100)
ROW_NUMBER() OVER (ORDER BY a.n) * 12 - 11
FROM
cte_n2 a CROSS JOIN cte_n2 b
ORDER BY
a.n
)
SELECT CONCAT(
'(', t.n, '),',
'(', t.n + 1, '),',
'(', t.n + 2, '),',
'(', t.n + 3, '),',
'(', t.n + 4, '),',
'(', t.n + 5, '),',
'(', t.n + 6, '),',
'(', t.n + 7, '),',
'(', t.n + 8, '),',
'(', t.n + 9, '),',
'(', t.n + 10, '),',
'(', t.n + 11, '),'
)
FROM
cte_Tally t;
November 14, 2022 at 6:32 pm
Just keep in mid that, "just because you can, doesn't mean you should"... Super long value lists can really eat up compile memory and compile time.
Oh, I get that. Just every once in a while, you want to put something together for a "one-off what-if" and thing like this come in handy.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2022 at 7:23 pm
Oh, I get that. Just every once in a while, you want to put something together for a "one-off what-if" and thing like this come in handy.
Agreed.
November 15, 2022 at 11:19 am
Jason A. Long wrote:Just keep in mid that, "just because you can, doesn't mean you should"... Super long value lists can really eat up compile memory and compile time.
Oh, I get that. Just every once in a while, you want to put something together for a "one-off what-if" and thing like this come in handy.
handle with care even with this. I have a case on one of my current projects where the "inputs" are 60k rows of 12 columns - if all is done in a single "values" it won't even run (neither SSMS or SQLCMD).
to make it work I just had to split it into chunks - I picked up 900 rows just to have good performance and it works fine even with all rows on the same script.
Easy to do this break in excel (where it is being done now)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply