Tuesday, July 15, 2014 - 03:20

Thank you Itzik for this excellent walk-through!

Thursday, February 6, 2014 - 07:30

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

Thursday, February 6, 2014 - 06:25

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 - 06:23

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 - 02:57
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 - 14:00
Itzik, let me know if you got my script by email. //Peter
Friday, July 15, 2011 - 02:32
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 - 23:34
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 - 23:31
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 - 23:15
You're welcome. See you for next challenge.
Saturday, July 9, 2011 - 19:09
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 - 14:44
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 - 05:57
AMB, can you test my #6 on your machine?
Friday, July 8, 2011 - 08:03
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 - 07:31
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 - 06:55
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 - 22:01
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:47
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 - 00:51
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 - 17:23
Here is a teaser for my solution. -- Peso Table 'DailyVisits'. Scan count 1, logical reads 2
Thursday, June 30, 2011 - 17:23
Here is a teaser for my solution. Table 'DailyVisits'. Scan count 1, logical reads 2
Friday, March 25, 2011 - 01:12
Where is my #3? ;-)

Tuesday, March 1, 2011 - 17:17
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 - 02:57
Not much activity here...

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




Thursday, January 20, 2011 - 20:13
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 - 13:58
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:55
;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
Destination,
MAX(MilesTraveled) - MIN(MilesTraveled) AS TotalMiles...
Wednesday, June 2, 2010 - 00:45
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 - 23:01
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...

Digital Magazine Archives

Browse back issues of SQL Server Pro, from January 2007 through the last issue published in April 2014. Find the back issues here.

 

From the Blogs
business people around a table and laptop
Oct 9, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 10: Dynamic Detection Gotcha #3

You can interject a SQL Server Job Step into your Jobs and have this new/injected Job Step do some checking for you. Here's a look at everything that’s involved in such an approach....More
Man holding a piece of paper with the word GOTCHA
Oct 7, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 9: Dynamic Detection Gotcha #2 3

Given that a USE statement is parsed before code is actually run, there are a few options that could be used as a around when it comes to detecting whether or not code should be run....More
iceberg
Oct 2, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 8: Dynamic Detection Gotcha #1 1

Learn about why the option of dynamically detecting whether or not to run a SQL Server Job at execution time is quite a bit more problematic than you might think....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) ×