The VALUES Limit

Question

The VALUES Limit

Viewing 8 posts - 16 through 22 (of 22 total)

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

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 1

The question is asked clearly: "What is the limit for rows in a Table Valued Constructor?". The documentation states that there is a limit. So there is nothing left to do but enter its value. Thanks Steve for this simple logic problem.

I forgot to point out that the VALUES Limit hint is in the QotD title.

This reply was modified 2 years ago by George Vobr.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 2

George Vobr wrote:

The question is asked clearly: "What is the limit for rows in a Table Valued Constructor?". The documentation states that there is a limit. So there is nothing left to do but enter its value. Thanks Steve for this simple logic problem.

The documentation states that there is a limit in a particular case, which is as a direct source for an INSERT statement. In other cases there is no defined limit. The question does not indicate which of these cases is the correct one

Just because you're right doesn't mean everybody else is wrong.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

frederico_fonseca wrote:

Jeff Moden wrote:
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)

It was, actually, an Excel spreadsheet that I was doing a one-off on. I should have just exported it to a TSV and imported it. It would have been faster than messing around with splitting things up like I did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Rune Bivrin wrote:

George Vobr wrote:
The question is asked clearly: "What is the limit for rows in a Table Valued Constructor?". The documentation states that there is a limit. So there is nothing left to do but enter its value. Thanks Steve for this simple logic problem.
The documentation states that there is a limit in a particular case, which is as a direct source for an INSERT statement. In other cases there is no defined limit. The question does not indicate which of these cases is the correct one

In any case, there are several valuable lessons to be learned whether you earned the 1 point or not.

It's not like this was a question for a job interview... and, to your point, it's also why I hate supposed "Most Frequently Asked Interview Quesitons" posts. The correct answer here, if you know what some of the others did, would in fact be "no limit". The book has a number of "1000", which someone knowing the "no limit" answer would also know. Which do you choose when you know both are true. Did the person writing the test intend for 1000 to be the correct answer or did they intend for the "no limit" answer to be correct because they want a Ninja and so they wrote an "Ace Breaker" question? Did the person writing the question actually even know that "no limit" is a possibility.

And that's why I hate "multiple-guess" questions and tests in general... there is no way for someone that actually knows the correct answer to answer this question if the author of the question doesn't actually know of that possibility and there's no place on such an example to explain.

The other thing that the question did is it brought out some experts on the subject that exposed other possibilities. In other words and once again, the discussions are king on forums whether they be attached to articles or supposedly simple questions about BOL. They gave everyone the opportunity to learn something different other that the "rote" knowledge a lot of people have (including me, in this case especially since I ran into the 1000 limit just a couple of weeks ago).

And THAT is a part of the reason why I stay here at SSC... a lot of other forums actually discourage such discussions. It makes me SO mad! (If you caught that, the PUN was seriously intended).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 5

Apologies. Was out of touch last week, but I've adjusted the question to note an INSERT statement and award points back.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

The question is still technically "ambiguous" given that the following also works...

CREATE TABLE #TestData (
int_val int NOT NULL 
);

INSERT #TestData (int_val)
SELECT 
tvc.int_val
FROM
( VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
...
(1189),(1190),(1191),(1192),(1193),(1194),(1195),(1196),(1197),(1198),(1199),(1200)
) tvc (int_val);

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 7

I'm not sure that counts as a TVC in an INSERT. The TVC is in the SELECT statement.

I also don't know how to word this without just giving away an answer.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

Steve Jones - SSC Editor wrote:

I'm not sure that counts as a TVC in an INSERT. The TVC is in the SELECT statement.
I also don't know how to word this without just giving away an answer.

I've gotta tell you, though... it's a great question! Look at the knowledge coming out in this discussion! Thank you, Mr. Jones!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)