Tuesday, July 15, 2014 - 3:20am

Thank you Itzik for this excellent walk-through!

Thursday, February 6, 2014 - 7:30am

See my set-based solution in second part of this article.
1.5 seconds and 14 000 reads.

Thursday, February 6, 2014 - 6:25am

Hi Itzik. My solution uses 14000 reads and executes in about 1.5 seconds.
My solution is a variant of Relational Division; Ordered Relational Division.

DECLARE @Items INT = (SELECT...

Thursday, February 6, 2014 - 6:23am

Hi Itzik.

The problem is just another Relational Division; Ordered Relational Division. My solution finishes in just 1.5 seconds and uses about 14000 reads.

DECLARE @Items INT = (...

Monday, November 21, 2011 - 2:57am
I use it to enforce data integrity in our data warehouse. Currently there are two business systems feeding the DW with data, and some tables (like Orders.SalesDetail) containts data from both systems...
Monday, August 22, 2011 - 2:00pm
Itzik, let me know if you got my script by email. //Peter
Friday, July 15, 2011 - 2:32am
Test done on production server. -- Celko #1 (181 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'dimDate'. Scan count 7, logical reads 7. Table '...
Thursday, July 14, 2011 - 11:34pm
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding both yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final...
Thursday, July 14, 2011 - 11:31pm
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final output...
Wednesday, July 13, 2011 - 11:15pm
You're welcome. See you for next challenge.
Saturday, July 9, 2011 - 7:09pm
Holy smoley! Only 1 scan count and 344 reads. ;WITH cteSource(dt, NumVisits, Added, Removed, Remained) AS ( SELECT dt, SUM(NumVisits) AS NumVisits, SUM(NumVisits) - SUM(Remained) AS Added...
Saturday, July 9, 2011 - 2:44pm
Thank you . That is what I suspected. I get similar timings on my dualcore laptop. 3108 ms duration vs 943 ms (1934 ms CPU vs 1732 ms). On my 16-core server I get this timings; 1470 ms duration vs...
Saturday, July 9, 2011 - 5:57am
AMB, can you test my #6 on your machine?
Friday, July 8, 2011 - 8:03am
This is the last one. This runs in 0.1 second on my server while the featured #3 runs in 1.4 seconds. Enjoy! -- SwePeso #6 ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added...
Friday, July 8, 2011 - 7:31am
Here is a better solution, #5. Almost the same number of reads, twice the CPU and 1/10th the duration of the featured #3 query. ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added...
Friday, July 8, 2011 - 6:55am
Here is another approach from me. It uses twice the CPU as my previous solution, 300 times more reads but finishes in almost a 1/10th of the time of my previous version. This suggestion heavily...
Thursday, July 7, 2011 - 10:01pm
Thank you Alejandro. I hope you liked the solution description. The matching section placeholders in code seem to be missing.
Wednesday, July 6, 2011 - 11:47am
Here is my stats with 100,000+ row sample data using same date interval as original (8 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'DailyVisits'. Scan...
Wednesday, July 6, 2011 - 12:51am
Studiomoderna, give it a new try. Think about it, is there a way to minimize the number of joins? Is there another way to get rid of the distinct aggregate?
Thursday, June 30, 2011 - 5:23pm
Here is a teaser for my solution. -- Peso Table 'DailyVisits'. Scan count 1, logical reads 2
Thursday, June 30, 2011 - 5:23pm
Here is a teaser for my solution. Table 'DailyVisits'. Scan count 1, logical reads 2
Friday, March 25, 2011 - 1:12am
Where is my #3? ;-)

Tuesday, March 1, 2011 - 5:17pm
It would be a really nice feature if this could be combined with this request

http://connect.microsoft.com/SQLServer/feedback/details/470881/allow-more-than-one-resultset-to-be-stored-with-...
Monday, February 21, 2011 - 2:57am
Not much activity here...

Tuesday, February 8, 2011 - 5:58pm
Wow! A 14 second solution.
Maybe it's time to reveal "Itzik 1"? :-)




Thursday, January 20, 2011 - 8:13pm
Does set-based mean "one-query solution", or just the absence of a cursor?

I have a solution that runs in 61-63 second and uses 1,116,205 reads but have 3 queries; one insert, one merge and...
Tuesday, August 17, 2010 - 1:58pm
My bad. Just realized clustered index is over travelerid and travelstart (not milestraveled).

;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
...
Tuesday, August 17, 2010 - 10:55am
;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
Destination,
MAX(MilesTraveled) - MIN(MilesTraveled) AS TotalMiles...
Wednesday, June 2, 2010 - 12:45am
Hre we go. For the published solutions above, only "Itzik Loop", "Peso Loop" and "Itzik Recursive 2" passed before the 30 minute mark on our Data Warehouse, which has 160 tables. I used the "anchor"...
Tuesday, June 1, 2010 - 11:01pm
I'll test them all on the data warehouse (with 160+ tables) later this morning.
Earlier I only measured the duration for my and Itzik's solutions.

In short, the recursive CTE's (except...
Upcoming Conferences

Register now to get the best rate available!

From the Blogs
Baby duck swimming alone
Jul 15, 2014
blog

The Marginalization of SQL Server Standard Edition 5

Microsoft seems to be bent on marginalizing SQL Server Standard Edition—both in the sense of the artificial constraints placed upon how much memory it can use, and in terms of what seems to be a shift in focus on the role of Standard Edition from Microsoft....More
Jul 8, 2014
blog

Replication: Sometimes More is Less

The unconventionality of having two publications for complex, unruly databases typically ends up saving huge amounts of time over the long haul and makes replication much easier to manage....More
Red restore key on computer keyboard
Jun 24, 2014
blog

AlwaysOn Availability Groups and Third Party Log Readers

When DBAs and SysAdmins learn the ins-and-outs of AlwaysOn Availability Groups, they’re then able to address high availability and disaster recovery concerns from a single interface or set of tooling, thus providing better scalability of management....More
SQL Server Pro Forums

Get answers to questions, share tips, and engage with the SQL Server community in our Forums.

Sponsored Introduction Continue on to (or wait seconds) ×