Friday, November 14, 2014 - 19:22

Also, there is a difference between mine and Itzik's solutions. If there is no gap with Itzik's solution, it still returns the number after the gap. Mine solution returns an empty set.

--...

Friday, November 14, 2014 - 19:17

Slightly less cost. Reduce the WHERE clause like this

WITH C AS
(
SELECT col1, LEAD(col1, 1, 0) OVER(ORDER BY col1) AS nxt
FROM dbo.T1
)
SELECT TOP (1)...

Tuesday, July 15, 2014 - 02:20

Thank you Itzik for this excellent walk-through!

Thursday, February 6, 2014 - 06:30

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

Thursday, February 6, 2014 - 05: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 - 05: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 - 01: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 - 13:00
Itzik, let me know if you got my script by email. //Peter
Friday, July 15, 2011 - 01: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 - 22: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 - 22: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 - 22:15
You're welcome. See you for next challenge.
Saturday, July 9, 2011 - 18: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 - 13: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 - 04:57
AMB, can you test my #6 on your machine?
Friday, July 8, 2011 - 07: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 - 06: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 - 05: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 - 21: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 - 10: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...
Tuesday, July 5, 2011 - 23: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 - 16:23
Here is a teaser for my solution. -- Peso Table 'DailyVisits'. Scan count 1, logical reads 2
Thursday, June 30, 2011 - 16:23
Here is a teaser for my solution. Table 'DailyVisits'. Scan count 1, logical reads 2
Friday, March 25, 2011 - 00:12
Where is my #3? ;-)

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

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




Thursday, January 20, 2011 - 19: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 - 12: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 - 09:55
;WITH cteTravel(TravelerID, Destination, TotalMiles)
AS (
SELECT TravelerID,
Destination,
MAX(MilesTraveled) - MIN(MilesTraveled) AS TotalMiles...

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
Dec 16, 2014
Sponsored

Eight SQL Server Signs the Illuminati Exist

Make no mistake about it, the signs that the Illuminati exist are all around us. You can find their markings on our buildings, on our currency and even in our music videos. That’s right, they’re everywhere!...More
Dec 2, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 16: Job Synchronization Checkup Logic

With Linked Servers configured to allow cross-host or cross-box communication between the servers you’re using to host your AlwaysOn Availability Groups, it’s time to start looking at some scripts that can be used to regularly check whether or not SQL Server Agent jobs are correctly synchronized across all servers in your topology....More
Abstract illustration of linked SQL Servers
Nov 18, 2014
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 15: Setting Up Linked Servers

Learn about setting up Linked Server definitions to let two (or more) servers in an AlwaysOn Availability Group talk to each other and check up on job statuses of jobs running on each other....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) ×