March 22, 2010 at 2:18 pm
Hi,
I have a table where the Primary key (for an ID field) is not incremental and therefore doesn't follow a sequence. How do I get the unused numbers from 1 to 99999 so I can show the available ID's that can be used on future inserts.
Thanks.
March 22, 2010 at 2:32 pm
Bitter Monkey (3/22/2010)
Hi,I have a table where the Primary key (for an ID field) is not incremental and therefore doesn't follow a sequence. How do I get the unused numbers from 1 to 99999 so I can show the available ID's that can be used on future inserts.
Thanks.
This should do it for you. Note that it creates a virtual tally table... if you already have one, you can jump right to the last select statement.
;WITH Tens (N) AS
(
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
)
, Thousands(N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
)
, Millions (N) AS
(
SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2
)
, Tally (N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM Millions
)
SELECT N
FROM Tally
LEFT JOIN <YourTable> yt
ON yt.PK = Tally.N
WHERE yt.PK IS NULL
AND Tally.N < 100000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 7:24 am
Wayne,
Nice idea, but I have to say that is the least efficient in-line tally table I have ever seen!
Compare:
CREATE FUNCTION [dbo].[GetNumbers]
(@n AS BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
L0 (n) AS (SELECT 1 UNION ALL SELECT 1),
L1 (n) AS (SELECT 1 FROM L0 A, L0 B),
L2 (n) AS (SELECT 1 FROM L1 A, L1 B),
L3 (n) AS (SELECT 1 FROM L2 A, L2 B),
L4 (n) AS (SELECT 1 FROM L3 A, L3 B),
L5 (n) AS (SELECT 1 FROM L4 A ,L4 B),
Num (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM L5)
SELECT TOP (@n)
n
FROM Num
ORDER BY n;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 8:27 am
Paul White NZ (3/23/2010)
Wayne,Nice idea, but I have to say that is the least efficient in-line tally table I have ever seen!
What do you base the inefficiency on? I read a post from (I believe) Lynn a while back... he had been doing some testing and found that doing an initial CTE of 10 numbers to be a "sweet-spot" in efficiency in a dynamic tally table. Also note that you have 7 levels, while mine has only 4.
Would it have been better if the CTEs didn't take as many lines?
;WITH
Tens (N) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM Millions)
SELECT N
FROM Tally
LEFT JOIN <YourTable> yt
ON yt.PK = Tally.N
WHERE yt.PK IS NULL
AND Tally.N < 100000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 8:38 am
WayneS (3/23/2010)
What do you base the inefficiency on?
Initially, just by looking at it. Then by checking the execution plan. Then by testing it.
SET STATISTICS TIME ON;
;WITH
Tens (N) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM Millions)
SELECT TOP (1000000)
@N = N
FROM Tally;
SELECT @N = N
FROM dbo.GetNumbers (1000000);
SET STATISTICS TIME OFF;
Results:
Your/Lynn's code: CPU time = 2344 ms, elapsed time = 2655 ms.
Mine/Itzik's code: CPU time = 344 ms, elapsed time = 348 ms.
Never use a UNION where a UNION ALL will do 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 8:45 am
I guess the subtal difference between Wayne's and Paul's solution is the (ORDER BY N) vs. (ORDER BY (SELECT 0)). The last one doesn't require a sort. Check out the following script (eventually change ORDER BY N to ORDER BY (SELECT 0) to see what happens):
SET STATISTICS TIME ON
;WITH
Tens (N) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM Millions)
SELECT top 100000 N
FROM Tally
;WITH
L0 (n) AS (SELECT 1 UNION ALL SELECT 1),
L1 (n) AS (SELECT 1 FROM L0 A, L0 B),
L2 (n) AS (SELECT 1 FROM L1 A, L1 B),
L3 (n) AS (SELECT 1 FROM L2 A, L2 B),
L4 (n) AS (SELECT 1 FROM L3 A, L3 B),
L5 (n) AS (SELECT 1 FROM L4 A ,L4 B),
Num (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM L5)
SELECT TOP (100000)
n
FROM Num
SET STATISTICS TIME OFF
March 23, 2010 at 9:00 am
Another way to get rid of the sort is to make all values the same in Tens(n):
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
No need for a sort as all values are equal.
March 23, 2010 at 9:02 am
Peter Brinkhaus (3/23/2010)
I guess the subtle difference between Wayne's and Paul's solution is the (ORDER BY N) vs. (ORDER BY (SELECT 0))
Good point. 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 9:03 am
Peter Brinkhaus (3/23/2010)
I guess the subtal difference between Wayne's and Paul's solution is the (ORDER BY N) vs. (ORDER BY (SELECT 0)). The last one doesn't require a sort.
and
Never use a UNION where a UNION ALL will do 😛
It looks like the major performance difference is the UNION ALL. The (SELECT 0) does remove a sort operation, but doesn't drastically change the execution plan. I changed the code to this:
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
SELECT top 100000 N
FROM Tally
For time statistics of the two methods (from Peter's code):
(100000 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 4346 ms.
(100000 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 4245 ms.
(the first one is my modified code, the second is your code)
Execution plans:
My/Lynn's method with UNION ALL and SELECT 0 is 36%
Your/Itzik's method is 64%
Somehow, I bet that Lynn didn't make this mistake (using UNION instead of UNION ALL).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 9:06 am
Gentlemen,
There are indeed many fine ways to write an in-line number generator!
My point was simply that the originally posted code was not one of them.
All very interesting though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 9:14 am
Paul - thank you for showing me an area of improvement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 26, 2010 at 8:18 am
Thanks so much guys.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply