Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I want to know the additional features of Tally table in Sql server.
I want to split the string into multiple strings for which the below two methods worked for me in the same way.

First Method
============
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO #mytable from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

-- SET @Parameter = ','+@Parameter +','

--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)val
FROM #mytable T1 inner join #mytable t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N

Tally Table
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('DBO.TALLY') IS NOT NULL
DROP TABLE DBO.TALLY
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO dbo.tally from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- SET @Parameter = ','+@Parameter +','

--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)
FROM dbo.Tally T1 inner join dbo.tally t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N

Please advise

Thanks

What I have tried:

Both tally table and temp table works same for me
Posted
Updated 8-Aug-18 5:58am
Comments
RedDk 8-Aug-18 11:55am    
Interesting question but what do you mean when you say:

-- SET @Parameter = ','+@Parameter +','

Does this really work?

I get Msg 537 "Invalid length parameter passed to the LEFT or SUBSTRING function"

[EDIT]
OK, check that. I get it ... just a comment
[END EDIT]

... working on it.
Member 13867163 9-Aug-18 6:19am    
i put it in comment ignore that
awaiting your reply
RedDk 9-Aug-18 12:57pm    
The word "tally" is probably a professors insert. Check your course notes. (It doesn't even appear in the SSMSE BOL and returns only a light sprinkling of returns in other online code queries).

Did you know: that you can CREATE SCHEMA [zizznoofuss] and dispense with the use of [dbo] altogether?
RedDk 8-Aug-18 12:13pm    
Using "#" to prefix a table name makes that table temporary and available only to the current user (local). It gets deleted when the session ends (disconnect). By the same token using "##" makes the temporary table a global table that can be used and also gets deleted but only when all other users are not using it.

So it's a time thing. But don't expect to know when the session terminates. Which is why doing temporary work under [dbo] schema control seems like the best thing to do at all times, imho.

1 solution

I did not actually run your code; but I was able to figure out the intended question.

Actually what you have are both using a "tally" table. One is of the "permanent" variety and the other is "temporary"; therefore, they should function identically.

The advantage to going with the "permanent" table is performance:
- It does not need to be created each time it is called.
- Indexing. While both can be indexed, a permanent table would only need to be done once.
 
Share this answer
 
Comments
Member 13867163 9-Aug-18 6:22am    
Great..
I would like to know whether both do the same work or any other features by using the dbo.tally table.
MadMyche 9-Aug-18 10:08am    
Any two tables with the same schema are going to operate the same. They are going to take the same effort to setup and seed. The advantage of a "dbo.tally" table is that this does not need to be done at runtime. Another advantage is that multiple operations can use it simultaneously.
Above that, the dbo.tally table can be used for more than one purpose; I have worked with them setup with various "usage" columns for seeding PKs and similar "common key set" applications.
A good place to read up on Tally Tables in general is Sql Server Central
Member 13867163 14-Aug-18 7:44am    
Thank you so much it is clear
MadMyche 14-Aug-18 9:38am    
You're welcome.
Please consider accepting/rating the answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900