Have a look a Jeff Modens excellent article here[/url] for an explanation

;with cte (temp_id, temp_test_id, temp_status)

as

(

select 1, 2, 'failed' union all

select 2, 2, 'failed' union all

select 3, 2, 'passed' union all

select 4, 17, 'failed' union all

select 5, 17, 'passed' union...

dcwilson2009 (8/23/2012)

---

Thank you for your response. While you were coming up with that, I came up with this as an entire solution finally. If you can, please take a look...

Try this

with cte as (

select DayKey, holi_id, holi_desc, holi_dt,

count(*) over(partition by DayKey, holi_id) as cn

from Source)

select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in...

ChrisM@Work (8/23/2012)

---

Mark-101232 (8/23/2012)

---

ChrisM@Work (8/23/2012)

---

mburbea (8/22/2012)

---

While this article is a bit old I did want to comment on a trick that I've discovered to build an itvf for a xml splitter....

ChrisM@Work (8/23/2012)

---

mburbea (8/22/2012)

---

While this article is a bit old I did want to comment on a trick that I've discovered to build an itvf for a xml splitter. This will...

Assumes ID is contiguous

DECLARE @t TABLE(ID INT,Status INT)

INSERT INTO @t(ID,Status)

SELECT 1,0 UNION ALL

SELECT 2,1 UNION ALL

SELECT 3,0 UNION ALL

SELECT 4,1 UNION ALL

SELECT 5,1 UNION ALL

SELECT 6,1 UNION ALL

SELECT 7,1 UNION...

with cte1 as (

select acc_no,number,c_name,

sum(R_Value) over(partition by acc_no) as R_Value,

sum(time_spent) over(partition by acc_no) as time_spent,

...

Hand coding the SQLCLR doesn't help much either, nowhere near Dwains 'REVERSE'

================================================================================

DWAIN

================================================================================

SQL Server Execution Times:

CPU time = 998 ms, elapsed time = 998 ms.

================================================================================

MARK'S SQLCLR

================================================================================

...

Marginal improvement

From this

CPU time = 2262 ms, elapsed time = 2277 ms. ...

Oops, my bad. Original post updated.

SQLCLR doesn't perform that well

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

'0000' +...

Using a numbers/tally table

http://www.sqlservercentral.com/articles/T-SQL/62867/

SELECT t.N

FROM dbo.Tally t

WHERE EXISTS(SELECT * FROM @TT1 t1 WHERE t.N BETWEEN t1.StartNo AND t1.EndNo)

EXCEPT

SELECT DDNo

FROM @TT2

Known issue. I don't believe there's a simple workaround.

http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

Try changing

OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'),

to

OriginalSchema.value('Form[1]/Codes[1]/code[sql:column("y.myNum")][1]','varchar(max)')