May 23, 2008 at 11:12 am
Yes. Apologies, it's a colloquial expression meaning 'Spot on'. And the word Rooster is apparently an Americanism for the animal featured on my Avatar.
1772, from roost (earlier roost cock, 1606), in sense of "the roosting bird," favored in the U.S. as a puritan alternative to cock
Hey, I'm from Derbyshire, in the UK. We are very rural...
🙂
Dave J
June 16, 2008 at 9:10 am
Great stuff. This really helps in cases where we tend to think the "loop" is the solution to all problems in SQL..
Awesome
N
June 16, 2008 at 10:04 pm
Thanks, Navket... I appreciate the feedback.
If you really want to see something that replaces the loop or a cursor, take a look at the following...
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
August 6, 2008 at 10:02 pm
The tally table is wonderful -- thought I'd post another use I'm getting from it: finding a minimum numeric record key within a potentially gapping set.
So my TableWithManualKey table might have the values 1, 2, 5, 7, 12 stored in the ManualKey due to record deletions. Here's how I quickly figure out where to fill the gap (grabbing a 3).
declare @NewKeyValue int
begin tran
select @NewKeyValue = min(N)
from someDb.dbo.Tally T
where not exists (select 'x' from someDb.dbo.TableWithManualKey MK where T.N = MK.ManualKey)
insert into someDb.dbo.TableWithManualKey (ManualKey, someOtherFields)
values (@NewKeyValue, @otherValues)
commit tran
Obviously you have to fill the tally table up as high as your keys might go.
Thanks!
August 6, 2008 at 11:58 pm
Good idea, but be careful... the Tally table is not a panacea. For example, there is code that will beat the pants off the Tally table for finding missing sequences... I've got an example somewhere... lemme see if I can find it...
--Jeff Moden
August 7, 2008 at 1:24 am
thomas.lamp (8/6/2008)
The tally table is wonderful -- thought I'd post another use I'm getting from it: finding a minimum numeric record key within a potentially gapping set.So my TableWithManualKey table might have the values 1, 2, 5, 7, 12 stored in the ManualKey due to record deletions. Here's how I quickly figure out where to fill the gap (grabbing a 3).
declare @NewKeyValue int
begin tran
select @NewKeyValue = min(N)
from someDb.dbo.Tally T
where not exists (select 'x' from someDb.dbo.TableWithManualKey MK where T.N = MK.ManualKey)
insert into someDb.dbo.TableWithManualKey (ManualKey, someOtherFields)
values (@NewKeyValue, @otherValues)
commit tran
Obviously you have to fill the tally table up as high as your keys might go.
Thanks!
Hi Thomas,
As Jeff already said, this might not be the best way to find your first gap. Here are two alternatives that, provided the table has an index on the ManualKey column, will probably be at least as fast as the numbers table:
INSERT INTO dbo.YourTable
(KeyColumn, OtherColumn)
SELECT MIN(t.KeyColumn) + 1, @OtherColumn
FROM dbo.YourTable AS t
WHERE NOT EXISTS
(SELECT *
FROM dbo.YourTable AS t2
WHERE t2.KeyColumn = t.KeyColumn + 1);
INSERT INTO dbo.YourTable
(KeyColumn, OtherColumn)
SELECT TOP (1) t.KeyColumn + 1, @OtherColumn
FROM dbo.YourTable AS t
WHERE NOT EXISTS
(SELECT *
FROM dbo.YourTable AS t2
WHERE t2.KeyColumn = t.KeyColumn + 1)
ORDER BY t.KeyColumn;
You can also try using an anti-semi join instead of the NOT EXISTS, though I doubt it will make a difference - it's probably a variation that the optimizer will consider by itself anyway.
INSERT INTO dbo.YourTable
(KeyColumn, OtherColumn)
SELECT MIN(t.KeyColumn) + 1, @OtherColumn
FROM dbo.YourTable AS t
LEFT JOIN dbo.YourTable AS t2
ON t2.KeyColumn = t.KeyColumn + 1
WHERE t2.KeyColumn IS NULL;
INSERT INTO dbo.YourTable
(KeyColumn, OtherColumn)
SELECT TOP(1) t.KeyColumn + 1, @OtherColumn
FROM dbo.YourTable AS t
LEFT JOIN dbo.YourTable AS t2
ON t2.KeyColumn = t.KeyColumn + 1
WHERE t2.KeyColumn IS NULL
ORDER BY t.KeyColumn;
August 9, 2008 at 1:52 am
I must say that it is very interesting and informative article. I use the strategy very frequently and have suggested many others to use this.
Great Article...
Atif Sheikh
August 9, 2008 at 10:36 am
Thanks for the feedback, Atif!
--Jeff Moden
August 9, 2008 at 10:42 am
Hugo Kornelis (8/7/2008)
As Jeff already said, this might not be the best way to find your first gap. Here are two alternatives that...
Here's another take on the same problem... test data is included...
--===== Setup for speed and to prevent blocking
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--=============================================================================
-- Create an experimental table to simulate the table being examined
-- Again... 6 million rows...
--=============================================================================
--===== Create the experimental temp table and populate with Serial #'s on the fly
-- This works because SysColumns always has at least 4000 entries
-- even in a new database and 4000*4000 = 16,000,000
SELECT TOP 6000000 SerialNumber = IDENTITY(INT, 1, 1)
INTO #JbmTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- --===== Like any good table, our experimental table needs a Primary Key
ALTER TABLE #JbmTest
ADD PRIMARY KEY CLUSTERED (SerialNumber)
-- This deletes a "monster" range just to see how it's handled.
DELETE #JbmTest
WHERE SerialNumber BETWEEN 5000000 AND 5500000
-- This deletes every third row in the first 1000 rows
DELETE #JbmTest
WHERE SerialNumber %3 = 0
AND SerialNumber <= 1000
PRINT REPLICATE('=',100)
--=============================================================================
-- Test the code
--=============================================================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
--===== Calculated Gaps
SELECT GapStart = (SELECT ISNULL(MAX(b.SerialNumber),0)+1
FROM #JbmTest b
WHERE b.SerialNumber < a.SerialNumber),
GapEnd = SerialNumber - 1
FROM #JbmTest a
WHERE a.SerialNumber - 1 NOT IN (SELECT SerialNumber FROM #JbmTest)
AND a.SerialNumber - 1 > 0
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
--Jeff Moden
August 15, 2008 at 12:36 pm
I thought this to be an excellent article with super examples - I was even able to update the freight example to use the AdventureWorks database without help:P
The question I have is you mentioned that you regularly create the tally table, do you create it in the tempdb database and just use it from there in all your queries that need it or do you put a copy in each database?
Tim "the newbie DBA"
August 15, 2008 at 7:58 pm
Thanks for the awesome compliment, Timothy.
It depends on what the client is comfortable with... some won't allow it as a permanent table so I have to code it in where ever it's needed. It's very fast to build so it's not much of a problem to build it as a temp table and just go from there.
Others insist that it be in every database (Model, too) but most want it in a Utility DB... some want it as a "permanent" table in Temp DB on boot and some want it in the Master DB.
I've even had customers ask for it to be in a Utility DB with a pass through view in every DB.
I guess my preferences would be to have it in every DB just for performance sake and ease of code or to have it in a Utility DB to reduce backup size a bit. The only time I backup a Utility DB is after a change or addition.
--Jeff Moden
September 11, 2008 at 3:32 am
Tnx for a very enlightning article, including all comments in this thread. I have wondered many times about using SET-based techniques instead of LOOP-like techniques. This article has opened my eyes in a way that other articles could not.
Many tnx.
Greetz,
Hans Brouwer
September 11, 2008 at 7:09 pm
Thanks for the awesome feedback, Hans. You made my day. Just remember, the Tally table is not a panacea... it cannot be used for all things that loop. For example, lot's of folks have asked, but I don't know of a way to use it in SQL Server 2000 to number rows sequentially as in Ranking. There are other methods for that as in the article below...
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
On the other hand, the Tally table does solve things that a lot of people would actually write a loop for.
--Jeff Moden
September 12, 2008 at 8:00 am
Thanks, Jeff, for the excellent example of how to loop through a CSV column using a Tally Table.
I knew how to use a Tally Table for creating a series of dates, etc. but really couldn't picture how to use it instead of loops. This article explained it very clearly. I can't wait to clean up some code!
September 12, 2008 at 7:33 pm
Thanks for the great feedback, Carla. If you want a bit more on splitting things, take a peek at the followup article below...
http://qa.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Viewing 15 posts - 166 through 180 (of 497 total)
You must be logged in to reply to this topic. Login to reply