您应尽量避免使用游标,它们是一个基本循环,导致逐行解决方案在大型SQL数据集上可能非常慢 . 游标还在缓冲区(内存)中保留空间,并可以从其他进程中窃取资源 . 如果必须循环,则应使用WHILE构造 .
SQL提供了其他基于SET的解决方案,可以取代CURSOR解决方案 .
使用递归CTE可以更有效地实现您的目标 .
这是一个递归CTE的例子,它可以取代光标的需要:
CREATE TABLE #Splitter
(
Id INT
,Vals VARCHAR(10)
)
INSERT INTO #Splitter VALUES
(1,'A,B,C'),
(2,'D,E,F')
;WITH cte AS
(
--Anchor point, the first Value from each ID
SELECT
Id
,LEFT(Vals,CHARINDEX(',',Vals)-1) AS Val
,RIGHT(Vals,LEN(Vals)-CHARINDEX(',',Vals)) AS Remainder
FROM #Splitter
WHERE
Vals IS NOT NULL AND CHARINDEX(',',Vals)>0
UNION ALL
--Central Recursion for each value
SELECT
Id,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
FROM cte
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
UNION ALL
--Error prevention, handling the end of the string
SELECT
Id,Remainder,null
FROM cte
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT ID,VAL FROM cte
如果您的[Vals]列始终采用设置格式,那么您可以使用CROSS APPLY和VALUES来获得更有效的解决方案 .
SELECT
ID
,v.Val
FROM
#Splitter S
CROSS APPLY (VALUES (left(S.Vals,1)),(SUBSTRING(Vals,3,1)),(RIGHT(Vals,1))) v(Val)