paul.knibbs (8/8/2014)
Jeff Moden (8/6/2014)
Actually, that's one of the questions that I frequently ask except it's to a million and the results have to be stored in a table.
I actually sat down to figure out how I'd do this, and I came up with the following:
INSERT INTO #test SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.name) AS num FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B
However, while figuring this out, I had to look up the syntax for ROW_NUMBER(), I forgot that the two tables in the CROSS JOIN had to be aliased if they were identical, and I then had a fun time figuring out why it *still* wouldn't work--it's because I'd enclosed the SELECT in brackets when it shouldn't be! I can only offer as a mitigating factor that I'm a general server admin and don't deal with SQL stuff daily, but I thought I knew T-SQL a bit better than that.
All I can say about that is... EXCELLENT! There are several performance nuances that could be included but the real key is what you did! You took the time to analyze and science out a problem, demonstrated that you can, indeed, find out something that you didn't know, and came up with a great first blush solution that will trounce a WHILE loop or recursive CTE. I love it when people have such intellectual curiosity. Well done, Paul!
Now, pretending that we're in an interview, I'd ask the next question...
With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia, explain or even speculate on what this method could be used for.
--Jeff Moden
Change is inevitable... Change for the better is not.