Jeff Moden (1/16/2012)

---

I have no problem with the debate and I don't even mind people like you being as dogmatic it their opposition to the article as I am for...

Jeff Moden (1/16/2012)

---

If I recall correctly, the "ultra-fast DISTINCT" is only fast when there are very few duplicates. The recursive counting CTE causes it to become much slower than...

Are you able to provide us...

This seems a reasonable starting point (it covers all the query examples you gave). You will obviously want to test that they are...

Roy Ernest (1/16/2012)

---

Change of topic... I got my second part of my Audit series published here... Yay..!!!!

And I got a A+ review for the year 2011 from my CTO. So...

Jeff Moden (1/16/2012)

---

Generating the data with a script that uses NEWID() avoids another problem... backwards compatibility for restores. Here's what happens when someone using SQL Server 2005 tries to...

I do like a happy ending 🙂

Jeff Moden (1/16/2012)

---

Heh.. hence the need for the same "tired old dogma". Although a counting rCTE might not be a significant part of THIS problem, people need to be...

So...

mario.balatellii (1/16/2012)

---

Thanks for your reply, however i think i did not explain my self correctly.

I need multiple row in one column..

so instead of having seperate rows for UNITS column I...

Jeff Moden (1/16/2012)

---

Generating the data with a script that uses NEWID() avoids another problem... backwards compatibility for restores. Here's what happens when someone using SQL Server 2005 tries to...

This is about as close as you can get, taking the question literally:

SELECT

CASE

WHEN Numbered.RowNumber = 1

...

You are right about the sample data not matching the output.

The LEFT JOIN...IS NULL construction does not have the quite same semantic...

SQL Server allows transactions to be nested for exactly this sort of situation. See http://msdn.microsoft.com/en-us/library/ms189336.aspx

Dev (1/1/2012)

---

Try following commands on Test Server... Once satisfied move it PROD.

ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()

SPLIT RANGE (11000000) -- New Range

GO

ALTER PARTITION SCHEME [psTBLTRANS_ID]

NEXT USED [ filegroup_name...