SQL Server中的计数表和普通表之间的区别 [英] Difference between tally table and normal table in SQL server
问题描述
我想知道Sql server中Tally表的附加功能。
我想将字符串拆分成多个字符串,以下两种方法以相同的方式为我工作。
第一种方法
= ===========
DECLARE @Parameter VARCHAR(8000)
SET @Parameter =',Element01,Element02,Element03,Element04,Element05, '
IF OBJECT_ID('tempdb ..#mytable')IS NOT NULL
DROP TABLE #mytable
SELECT TOP(选择LEN(@)参数))identity(int,1,1)as N INTO #mytable from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
- SET @Parameter =','+ @ Parameter +','
- =====做与循环相同的事情做了......逐步通过变量
- 并返回角色位置和角色...但只有
- 如果它是逗号的那个位置......
SELEC T t1.N,SUBSTRING(@参数,T1.N + 1,CHARINDEX(',',@参数,T1.N + 1)-T1.N-1)val
FROM #mytable T1内连接#mytable t2 on t1.n = t2.n-1
WHERE t1.N< = LEN(@Parameter)
AND SUBSTRING(@Parameter, t1.N,1)=','
订购N
理货表
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(选择LEN(@Parameter))标识(int,1,1)为来自Master.dbo.SysColumns的N INTO dbo.tally sc1,
Master.dbo.SysColumns sc2
- SET @Parameter =','+ @ Parameter +','
- =====做与循环相同的事情......逐步通过变量
- 并返回字符位置和字符..但只有
- 如果它是那个位置的逗号......
SELECT t1.N,SUBSTRING(@ Parameter,T1.N + 1,CHARINDEX(',',@ Parameter,T1.N + 1)-T1.N-1)
来自dbo.Tally T1内部联接dbo.tally t2 on t1.n = t2.n-1
WHERE t1.N< = LEN(@Parameter)
AND SUBSTRING(@参数,t1.N,1)=','
订购N
请指教
谢谢
我的尝试:
tally表和临时表对我来说都是一样的
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
推荐答案
我实际上没有运行你的代码;但是我能够找出预期的问题。
实际上你所拥有的都是使用理货表。一个是永久品种,另一个是临时品种;因此,它们应该具有相同的功能。
使用永久表的优势在于性能:
- 每次调用时都不需要创建它。
- 索引。虽然两者都可以编入索引,但永久表只需要进行一次。
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.
这篇关于SQL Server中的计数表和普通表之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!