T-SQL CTE Table Implicit Iteration

Question

I try the instance query as below to convert a commas separated list into multiple records and I found the cte table 'tmp' can run on its own until all sub-strings were loaded in a new record. This example query is from this thread.

I don't want to focus on this functionality but the use of the temporary table here. Can anyone please advise what term is used in T-SQL to describe this computing techniques for the cte table? The cte table is recalled by itself in the definition statements and without explicit looping statements the table actually run iteratively until the filter statement where Data > '' return NULL.

create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22,25,26'
insert Testdata select 2, 8, '17,19'
insert Testdata select 3, 7, '13,19,20'
insert Testdata select 4, 6, ''


;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem
from tmp
order by SomeID

I think you mean the `CTE` here not a `TEMP` table, those are different in SQL Server. — Ilyes, Mar 18 '18 at 21:06
@seesharpguru, yes, I talked about CTE and I treated it as a temporary table in the query. — LUSAQX, Mar 18 '18 at 21:07

score 1 · Answer 1 · answered Mar 19 '18 at 00:54

You are correct, the term is Recursive Common Table Expression, AKA rCTE. See: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

A couple things to note: First, for most things in T-SQL, rCTEs are slow and generate a lot of IO. For splitting (AKA tokenizing) a delimited string they are a poor choice. rCTEs are good for handling hierarchal data which is why, if you do a Google search for Recursive CTE, the most common example will be how to traverse a hierarchical employee table or the like.

A great article about rCTEs and what they are good for can be found here: http://www.sqlservercentral.com/articles/T-SQL/90955/

score 0 · Answer 2 · answered Mar 18 '18 at 21:14

0

After a quick research, I found a term 'Recursive Common Table Expressions' which should fit this topic. Any other advice is more than welcome.

answered Mar 18 '18 at 21:14

LUSAQX

377
2
6
19

1

A common table expression can include references to itself. This is referred to as a recursive common table expression. – Ilyes Mar 18 '18 at 21:16
1

All what you need is to visit the **[Docs](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql)**, You will find all needed informations there, that's my advice :). – Ilyes Mar 18 '18 at 21:22

T-SQL CTE Table Implicit Iteration

2 Answers2