Hi Magy,

The main question is about you "untested" CTE:

SELECT ce.testerId, @partId as testId, 0 as resultCount,...

Define "spoilt".

It's all the matter of preferences, you know.

Sorry, was having lunch, and did not refresh the page before hitting "Add Reply".

Did not even bother checking email notifications...

Shame...

ALTER FUNCTION [dbo].[DelimitedSplit8K_NoCTE]

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!

RETURNS TABLE AS

...

Complete version, including non-CTE on-fly Tally table:

DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1)

SET @pString = 'aaaa,,ccc,d,'

SET @pDelimiter = ','

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY cteLen.N1),

...

Lynn Pettis (1/6/2016)

---

Unfortunately you failed to recreate the function as originally coded. You are relying on the existence of a tally table in the database. The original DelimitedSplit8K...

Here you go:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[DelimitedSplit8K_NoCTE]

--===== Define...

You know, people been splitting delimited lists for many years...

Lynn Pettis (1/6/2016)

---

Same could be said if you populate temporary tables with the results of a complex query and then use that table in another query. You have to...

GilaMonster (1/6/2016)

---

No, and that's not what Lynn said.

Subqueries and CTEs are both part of a larger statement. Compilation is at the batch level, each statement in a batch gets compiled....

erics44 (1/6/2016)

---

and even if you have a senior developer, everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of...

Hugo Kornelis (1/6/2016)

---

In theory it is also possible that the optimizer decides to first get the results of the CTE, store them in a worktable (called a Spool in the...

Lynn Pettis (1/6/2016)

---

I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete...

Very questionable. Very.

CTE's break the structure of Structured Query Language, moving part of FROM effectively out of...

Hugo Kornelis (1/6/2016)

---

A CTE allows you to define a complex subquery once and refer to it multiple times in the same query without repeating the code. Technically possible before CTE,...