October 3, 2012 at 5:54 am
Cadavre (10/3/2012)
I've answered the counting question before. I didn't get the job in the end, but I suspect it was due to experience rather than interview technique (when I asked them if there was anything specific that had stopped them from hiring me, I didn't get an actual answer). Anyway, this is how I answered the counting question: -
Now THAT's what I'm talking about! Nicely done. I've been known to take the ability to communicate and explain like that over experience (depending on the demands of the job, of course) because I think it's an incredibly important aspect of being a DBA or SQL Developer that so very many people overlook and fail to achieve.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2012 at 6:04 am
Jeff Moden (10/3/2012)
Jason-299789 (10/3/2012)
Karthick,Jeff was making valid point about about how to create the Tally table in the first place, most people will write a while loop, or a recursive CTE rather than using a cross join between two of the sys tables in the db.
I suspect Jeff wants to see the developer do the cross join rather than the CTE or a While loop. 😀
Or a cascading cross-joined CTE Itzik Ben-Gan style. Or a SELECT from a Tally table.
Bonus points if the Developer says, "It Depends" and then demonstrates and explains all 3 without further prompting. Even more points for the tangent of using IDENTITY vs ISNULL(ROW_NUMBER()) on the creation of a physical Tally Table.
I think I'd get 3 out of 4. What's that last bit about?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2012 at 6:09 am
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.
I'd not heard or seen Ben-gans cascading cross join CTE, but have now and it looks like a good solution for large number values, add on that it can easily be wrapped in a Inline table function and you save yourself a permanant table in your DB.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 6:12 am
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.
OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2012 at 6:54 am
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N
INTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
I think there might be a connect item about it somewhere. . . but can't seem to find it.
October 3, 2012 at 7:32 am
Cadavre (10/3/2012)
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NINTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
--EDIT--I think there might be a connect item about it somewhere. . . but can't seem to find it.
Ah... I understand. Now I can be 4 for 4.
Jeff - any openings? 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2012 at 4:51 pm
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.I'd not heard or seen Ben-gans cascading cross join CTE, but have now and it looks like a good solution for large number values, add on that it can easily be wrapped in a Inline table function and you save yourself a permanant table in your DB.
Check it in profiler. Compare speeds and reads for different sizes compared to a physical Tally Table. The Tally Table frequently wins for performance and the cCTE produces virtually no reads when done properly. Fun stuff.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2012 at 4:52 pm
Cadavre (10/3/2012)
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NINTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
--EDIT--I think there might be a connect item about it somewhere. . . but can't seem to find it.
+1
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2012 at 4:55 pm
dwain.c (10/3/2012)
Cadavre (10/3/2012)
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NINTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
--EDIT--I think there might be a connect item about it somewhere. . . but can't seem to find it.
Ah... I understand. Now I can be 4 for 4.
Jeff - any openings? 🙂
4 for 5. Any other easy set-based way to count from 1 to 100?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2012 at 6:16 pm
Jeff Moden (10/3/2012)
dwain.c (10/3/2012)
Cadavre (10/3/2012)
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NINTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
--EDIT--I think there might be a connect item about it somewhere. . . but can't seem to find it.
Ah... I understand. Now I can be 4 for 4.
Jeff - any openings? 🙂
4 for 5. Any other easy set-based way to count from 1 to 100?
Consulting my toolbox suggests this will work:
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100
A little trick I picked up from someone with the initials JM. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2012 at 8:54 pm
dwain.c (10/3/2012)
Jeff Moden (10/3/2012)
dwain.c (10/3/2012)
Cadavre (10/3/2012)
dwain.c (10/3/2012)
Jason-299789 (10/3/2012)
I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().:unsure:
Because if you do: -
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NINTO yourTallyTable
FROM (VALUES(1))a(n);
Then N is a NULLable column. You want it to be NOT NULL, so include the ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),0)
--EDIT--I think there might be a connect item about it somewhere. . . but can't seem to find it.
Ah... I understand. Now I can be 4 for 4.
Jeff - any openings? 🙂
4 for 5. Any other easy set-based way to count from 1 to 100?
Consulting my toolbox suggests this will work:
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100
A little trick I picked up from someone with the initials JM. 😀
Heh... "Taaa-daaa!" Dwain for President! 😀 He's got his ears on and has been receiving 5 by 5 all along. Well done!
There might be other ways, as well, but that's the kind of stuff that I believe you'll never find on a test but can make all the difference in the world. These aren't just "oolies". These are techniques from folks that have been under fire in the field.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2012 at 5:15 am
Hi there!
There is another method without using ROW_NUMBER...OVER and undocumented table from master database (which can be changed or even dropped by MS without any notice http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx;-)):
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))b(N);
October 4, 2012 at 6:05 am
Eugene Elutin (10/4/2012)
Hi there!There is another method without using ROW_NUMBER...OVER and undocumented table from master database (which can be changed or even dropped by MS without any notice http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx;-)):
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))b(N);
Yes Sir! That's another way! Well done.
So far as the article you cited goes... here's a quote from the article...
There are several reasons why all or some of the system tables might go missing. An improper database upgrade from SQL Server 2000 can cause some or all of these tables go missing. Also, user activity (right-click and delete) can cause these tables go missing.
First, if you have users deleting stuff from Master, then you have much bigger privs problems than you can imagine. And, failed upgrades don't count here.
Second, I agree... spt_values could go away. That's why it keeps getting bigger with every release up through 2012... it'll make a bigger sound if they drop it. 😀 I suspect it'll still be there even in the next version of SQL Server, though. That notwithstanding, if you have used it in production code and they do someday drop it, it's a good thing to know other quick ways to do the same thing. I agree that it should not be used as a "free" Tally Table in production code but someone that knows it's there and what it contains is usually better trained than someone with just rote memorization.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2012 at 6:48 am
Jeff Moden (10/4/2012)
I agree that it should not be used as a "free" Tally Table in production code but someone that knows it's there and what it contains is usually better trained than someone with just rote memorization.
Either that or they just have a bigger script library. 😛
Nice one Eugene!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 4, 2012 at 6:50 am
No undocumented vapourtables, no sort:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply