Clear and easy question! Thanks!

🙂

It's...

Easy, if you know it.

Too much easy!

Thanks for TWO free points.

+1

sknox (10/1/2013)

---

Haven't had time to research SQL 2014 yet.

Guessed optimistically.

Got it wrong.

Can anyone explain why this should still be around when CLR has been available to replace its functionality since...

I think that extended procedures are unremovable, also, if Microsoft discourages their use.

Perfect qotd and explanation.

Thanks!

John Mitchell-245523 (9/26/2013)

---

I know it's a matter of preference, but I'd rather have any complexity tucked away in the CTE definition than in the body of...

No recursion needed to create a dynamic tally table with a CTE:

-- Code below is SQL Server 2008 or newer

with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

...

Be aware of case:

DECLARE @str VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @str

It should be:

DECLARE @str VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE sp_executesql @str

🙂

John Mitchell-245523 (9/25/2013)

---

Clearly not useless, even if your personal preference is not to use them. And no more or less complicated, in my...

Dscheypie (9/25/2013)

---

Carlos,

sure, you may be right.

And the topic of this QotD is CTE and the usage of DML.

With the example for identifying duplicate records I wouldn't agree to CTEs...

CTE is useless and complicated!

The same result is reachable in a standard way:

create table #a (i int)

insert #a SELECT object_id FROM sys.objects

delete from t

from (SELECT * FROM #a WHERE i...

+0.5

Never used a CTE at all.