| Downloads |
|---|
| 25886.zip |
Editor's Note: SQL Server Magazine welcomes feedback about the magazine. Send your comments to [email protected]. Please include your full name, email address, and daytime phone number with your letter. We edit all letters and replies for style, length, and clarity.
Time Trials for Missing Numbers Queries
Itzik Ben-Gan's T-SQL Black Belt column "Missing Numbers" (July 2002, InstantDoc ID 25103) was very interesting and prompted me for the first time to respond to a SQL Server Magazine article. Although Ben-Gan's article didn't address the speed of his query, which represents the missing numbers as ranges, here is a faster solution:
SELECT *
FROM (SELECT
n1.a + 1 begin_range,
(SELECT MIN(n2.a)
FROM Test1 n2
WHERE n2.a > n1.a) - 1 end_range
FROM Test1 n1) t
WHERE t.end_range IS NOT NULL
AND (end_range - begin_range) >= 0
You're right, speed wasn't on my mind when I wrote the ranges query, but it should have been. I also received a query from Craig Bennett and compared my solution, your solution, and his solution after populating the Test1 table with 100,000 rows, as the script in Listing 1 shows. The following code shows Bennett's query, which ran for 10 seconds on my laptop:
SELECT
(SELECT MAX(a) FROM Test1 WHERE a
< f1.a) + 1 AS begin_range,
a - 1 AS end_range
FROM Test1 AS f1
WHERE a <> (SELECT MAX(a) FROM Test1
WHERE a < f1.a) + 1
ORDER BY a
Your query ran for 6 seconds. And mine? Well, I had to cancel it after a few minutes because it hadn't finished (to review my long-running query, see Listing 6 in "Missing Numbers").
More Query Analyzer Shortcuts
In regard to Michael Otey's SQL Seven column "Query Analyzer Shortcuts" (May 2002, InstantDoc ID 24349), you can also run the current query or selection in Query Analyzer by using either Ctrl+E or Alt+X. In addition, pressing F1 at any time will bring up Query Analyzer Help, and selecting any SQL statement and pressing Shift+F1 will give you T-SQL Help about the command you selected. These shortcuts work with both SQL Server 2000 and 7.0. Note, however, that except for F5 (which lets you run the current query), the shortcuts in the article work only with SQL Server 2000.
