May 7, 2008 at 10:27 am
Mark (5/7/2008)
Jeff Marcus (5/7/2008)
If you are using SQL Server 2005 you can use ROW_NUMBER instead.
If you're not using SQL 2005, I guess you could simulate ROW_NUMBER like this...
declare @param varchar(8000)
set @param = '101,2008.04.02,1.1,102,2008.04.03,2.2,103,2008.04.04,3.3'
select
max(case when RowNumber % 3 = 0 then v end) as val,
max(case when RowNumber % 3 = 1 then v end) as ord,
max(case when RowNumber % 3 = 2 then v end) as fval
from (
select
len(left(@param, N)) - len(replace(left(@param, N), ',', '')) as RowNumber,
substring(@param, N, charindex(',',','+@param+',',N+1)-N-1) v
from dbo.Tally where N <= len(@param) and substring(','+@param+',',N,1)=',') a
group by RowNumber / 3
/* Results
val ord fval
--------------- ----------------- -----------------
101 2008.04.02 1.1
102 2008.04.03 2.2
103 2008.04.04 3.3
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 7, 2008 at 10:48 am
Jeff, once again, you are my hero!!
Beautifully-written article about such a useful topic.
I've used a "dates" table for quite a while, and actually have a tally table in the current database that I'm working with, but really didn't understand how to use it until your article.
The "dates" table has been a great help. It's amazing how such seemingly simple things, like these auxilliary tables, can be so useful.
Best regards,
SteveR
May 7, 2008 at 11:03 am
Great article.
I used an "Integers" table for years, but starting with 2005 found that CLR functions to stream out integers is better (faster and takes virtually no space in the DB).
Or here's a pure T-SQL solution instead of having a table:
http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/
Cheers.
May 7, 2008 at 11:28 am
Really enjoyed the article.
However... on the parameter split if parameter list happens to be exactually 8000 characters the substring in the select will fail. For example:
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = REPLICATE('1,2,3,4,5,6,7,8,9,10',1000)
will produce @Parameter with a length of 8000 and cause the error. I got around this buy checking the length and if it was 8000 using a select with a case statement looking for zero to be returned by charindex.
May 7, 2008 at 1:07 pm
Well Done! Clear and great examples. Thanks for another arrow in the quiver.
May 7, 2008 at 3:25 pm
Great article. Jeff, you have a wonderful knack for explaining things. Even someone new to tally tables would be able to follow your article and absorb the idea.
May 7, 2008 at 8:34 pm
Shamshad Ali (5/7/2008)
Hello,My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can't find a way to replace cursor. Here is my requirement and i want if there is any way to replace cursor with Tally Table, let me know.
I have a table that has many fields and one is bit, there is a nightly job that checks this tables bit column if found 1 then it iterates and send other columns data to an Stored procedure, that SP checks if there is any session found between that parameter it calls another Stored procedure that is basically calling a C# (3.5) dll which recovery some information and dump in other tables.
so here is some graphical presentation
1- select startTime, endTime, schedule from tbl_Schedules where bit=1
2- EXEC SP_FindUserSession @startTime, @endTime, @schedule
3- EXEC SP_CallDLLToPostUserCredit (@SessionID, User)
at the Top, I am using CURSOR_tbl_Schedule, and iterate then send information to Step 2, that is also Finding Session (Thousands of users session hit during that startTime and endTime) and then finally i send those sessions with the help of CURSOR_Session to SP that recovers Users Credit and Update Users marks in specific table.
I would like to know if i can replace cursor. Right now I am in testing phase and planned to implement on production. But I really worried about those cursors, that may slow down performance.
If you have any questions or require more details, let me know.
I hope there is a way to solve this via Tally Table logic.
Shamshad Ali.
Hi Shamshad,
Thanks for your interest. Sadly, a Tally table is not a panacea for repairing all forms of RBAR. In English, that means that a Tally table offers no hope when you have a stored procedure or a CLR that will only process one row at a time... both of the stored procs and the CLR will need to be rewritten to handle sets of data instead of RBAR. Sorry for the bad news...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 8:36 pm
RyanRandall (5/7/2008)
Nice work, Jeff. That plural table name must've been difficult for you to come to terms with 😀
Heh... it kept me up all bloody night! But "When in Rome, do like the Romans". 😛
Thanks for the kudo, Ryan.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 8:38 pm
Calvin Lawson (5/7/2008)
OMG, everybody out of the pool, quick! Read this article, now! Do not write a single piece of SQL code until you've finished it, and understand it.Top notch as usual, Jeff. If I could give you six stars I would.
Awesome compliment... thanks alot, Calvin!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 8:50 pm
Chirag (5/7/2008)
nice article jeff.Here's another one with more uses of tally table.
http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
Thanks for the compliment, Chirag.
Yeah, they do have some neat uses on that particular link. It's very strange that they did like everyone else does, though... they say that the Tally table is used for setbased programming and get ready to reel in the code that will follow... then they say how to create the Tally table...
[font="Courier New"]WHILE....[/font]
... and that blows me away. :hehe:
They also used a WHILE loop to create some of their test data... for those that haven't seen it before, I guess this is as good a place as any to show a set based method for making pot wads of flat data to test with... it's pretty darned fast, too!
[font="Courier New"]--drop table jbmtest
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest_RowNum PRIMARY KEY CLUSTERED (RowNum)
[/font]
If I remember correctly, it was my good friend and testing buddy, Matt Miller, that showed me the CHECKSUM trick... prior to that, I was using a conversion to VARBINARY which was a fair amount slower than CHECKSUM.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 8:53 pm
karthikeyan (5/7/2008)
Jeff,Excellent Article!
It seems that you have written for me at the right time.:) Really, It is very useful for me.
Because so far i have replaced more than 5 cursors and used Tally table in that places.
Thank You Sql teacher.:)
Thank you for the great feedback and outstanding compliment, Karthik... keep up the great work!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 9:27 pm
chris (5/7/2008)
How about using recursion :hehe: to replace loops? (One of the great new functions in SQL2005)No need for a tally or temporary tables.
Heres a params example:
DECLARE @params varchar(8000)
SET @params = '1,2,3,4,5,6,7,8,9,10';
WITH Params AS (
-- start with last item
SELECT REVERSE(SUBSTRING(REVERSE(@Params), 0, charindex(',', REVERSE(@Params)))) as [value], len(@Params) as start
UNION ALL
-- base case
SELECT SUBSTRING(@Params, 0, charindex(',', @params)) as [value], charindex(',', @params) + 1 as start
UNION ALL
-- recursive case
SELECT SUBSTRING(@Params, start, charindex(',', @params, start) - start) as [value], charindex(',', @params, start) + 1 as start
FROM Params
WHERE charindex(',', @params, start) > 0
)
SELECT *
FROM Params
OPTION (MAXRECURSION 999); -- must be greater than max number of items
Good idea... but I've found recursion to be a fair bit (a lot, actually) slower than either the Tally table or by encapsulating the same logic I used to make the Tally table in the article in a CTE. Recursion is actually a form of "hidden RBAR". Of course, that's an extraordinary claim on my part... extraordinary claims require extraordinary proof... and here it is... 3 different methods that smoke recursion... all the methods dump their data into a temp table just to take display delays out of the picture...
[font="Courier New"] SET NOCOUNT ON
--=============================================================================
-- Recursive CTE does the count
--=============================================================================
PRINT '========== Recursive CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT 1 AS N
UNION ALL
SELECT N+1 FROM cteTally WHERE N<@Top
)
SELECT N
INTO #Test1
FROM cteTally
OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
--=============================================================================
-- ROW_NUMBER CTE does the count
--=============================================================================
PRINT '========== ROW_NUMBER CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N
FROM Master.dbo.spt_Values sc1,
Master.dbo.spt_Values sc2
)
SELECT *
INTO #Test2
FROM cteTally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
--=============================================================================
-- ROW_NUMBER query does the job directly
--=============================================================================
PRINT '========== ROW_NUMBER Query =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N
INTO #Test3
FROM Master.dbo.spt_Values sc1,
Master.dbo.spt_Values sc2
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
--=============================================================================
-- IDENTITY query does the job directly
--=============================================================================
PRINT '========== IDENTITY =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
SELECT TOP(@Top) IDENTITY(INT,1,1) AS N
INTO #Test4
FROM Master.dbo.spt_Values sc1,
Master.dbo.spt_Values sc2
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
DROP TABLE #Test1,#Test2,#Test3,#Test4
[/font]
... and here's the results on my box...
[font="Courier New"]========== Recursive CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3625 ms, elapsed time = 4435 ms.
====================================================================================================
========== ROW_NUMBER CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 120 ms.
====================================================================================================
========== ROW_NUMBER Query ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 115 ms.====================================================================================================
========== IDENTITY ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 237 ms.====================================================================================================
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 9:37 pm
Carl Federl (5/7/2008)
Another great article ! ThanksThe first reference for a tally table that I can recall is in "Guide to Sybase and SQL Server" by C. J. Date and D.McGoveran published in June of 1992.
Does anyone know of an earlier reference ?
Thanks for the tip, Carl... I've gotta get me a copy of that... anyone who came up with a tool that useful has gotta have other "goodies" in there, as well.
1992... wasn't that before they made cursors available?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 9:41 pm
Mike DiRenzo (5/7/2008)
This is a super cool article. Let me repeat, this is a super cool article. What a refreshing read!I look back on all the TSQL I have written using loops or cursors - arrrgh! Oh, and all the arguments/discussions on cursors, loops, variables of type table....
I am hooked on Tally tables. It is an extremely obvious in-your-face concept and I can't imagine why I haven't used this technique before.
Thanks again.
-Mike
Awesome compliments, Mike! Thanks. I really appreciate the part about "refreshing read". It's a real bugger to take something so "dryly" technical and make it still sound like someone other than the poster boy for the "Dry Eyes" commercial wrote it. 😛 Thanks again.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 9:44 pm
Marius Els (5/7/2008)
:smooooth: This is slick...... Great work Jeff!This is a great example of implementing set-based programming, three thumbs up from me.
Now to apply this to problems going forward!
Thanks for the excellent article and insight into this subject.
Heh... if you have 3 thumbs, Marius, then you should have used this icon, instead...
:alien:
Thanks for the compliment and the feedback! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 511 total)
You must be logged in to reply to this topic. Login to reply