Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Multipe letters into one Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2012 12:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
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.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1367419
Posted Wednesday, October 3, 2012 3:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:52 AM
Points: 2,031, Visits: 2,535
yeah..n(n+1)/2..


karthik
Post #1367486
Posted Wednesday, October 3, 2012 4:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
karthik M (10/2/2012)
Coming back to your question, count 1 to 100,

select sum(n) from tally where n < 101

am i right :)


Apparently you didn't run your own code to test to see if it does the job, Karthik.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1367541
Posted Wednesday, October 3, 2012 4:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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.

If you know this stuff, you should be able to talk about it spontaneously including some pros and cons. If you've only memorized it, you might only come up with the cross join and offer no extra information without me having to ask a thousand questions to find out what you know.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1367545
Posted Wednesday, October 3, 2012 5:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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: -


That depends on whether or not you have a numbers or tally table already defined. If you do then: -
--== TALLY ==--
SELECT N
FROM Tally
WHERE N > 0 AND N <= 100;

Which would do an index seek on what would hopefully be an index with a fill factor of 100 since the data in the table should be static.

If there isn't already a numbers or tally table defined, I'd prefer to add one as they can be extremely useful in solving lots of different issues.

If, for one reason or another, adding a tally or numbers table is not possible then we could do it in a few ways.

The most common answer is probably to do a loop. This is also the worst performing solution, as SQL is designed to be set-based. However, in the interests of completeness: -
--== WHILE LOOP ==--
DECLARE @HOLDER TABLE (N INT);
DECLARE @COUNTER INT = 0;

WHILE @COUNTER < 100
BEGIN
SET @COUNTER = @COUNTER + 1;
INSERT INTO @HOLDER
SELECT @COUNTER;
END

SELECT N
FROM @HOLDER;

The next way to do it would be a recursive CTE. I've found these to be as bad as loops for performance scaling, so I wouldn't generally advise that they be used. Again, in the interest of completeness: -
--== RECURSIVE CTE ==--
WITH CTE(N) AS (
SELECT 1
UNION ALL
SELECT N+1
FROM CTE
WHERE N+1 <= 100)
SELECT N
FROM CTE;

Finally, we could use a cross join method to get the cartesian product of a set of 10, then use the row number function. Either we could use the method that has been made popular by Ben-Gan: -
--== CASCADING CROSS JOIN ==--
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE X, CTE Y)
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM CTE2;

Or, since we know it's only 100, we could just do a cross join on two sets of 10: -
--== CROSS JOIN ==--
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
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);

So, my preference would be a numbers or tally table, but if I can't use one then I'd go with a cross join method.



Looking back, perhaps I should've gone into the creation of a tally table. Never mind, if I'd got that job then I wouldn't have my current position which I'm enjoying a lot



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1367577
Posted Wednesday, October 3, 2012 5:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1367589
Posted Wednesday, October 3, 2012 6:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1367595
Posted Wednesday, October 3, 2012 6:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1367598
Posted Wednesday, October 3, 2012 6:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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().



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1367602
Posted Wednesday, October 3, 2012 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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().


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)


--EDIT--
I think there might be a connect item about it somewhere. . . but can't seem to find it.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1367641
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse