And, if you take a look at the following article, you'll find the "old fashioned way" actually has a bit of a performance edge over the PIVOT method...

[font="Arial Black"]Cross Tabs...

Actually... I take that back. They need to leave UPDATE FROM the heck alone so that we can still use the "Quirky Update" for "data smears" even if they...

GSquared (1/26/2009)

---

rog pike (1/26/2009)

---

Jeff Moden (1/26/2009)

---

I absolutely agree... and one of my favoritie proprietary pieces of code is UPDATE FROM... if that were not available, you'd end up having to...

rog pike (1/26/2009)

---

Jeff Moden (1/26/2009)

---

I absolutely agree... and one of my favoritie proprietary pieces of code is UPDATE FROM... if that were not available, you'd end up having to do...

Man, sorry, Gus... :blush: I meant a "While Loop" for the count comparison, lost my mind, and said "Cursor" instead. A WHILE LOOP has about the same speed...

The proper way to do such a detection is similar to the following where '1D3' could be a column name...

[font="Courier New"] SELECT 1 AS IsAllDigits

WHERE '1D3' NOT LIKE...

Be careful... ISNUMERIC should never be used as an "IS ALL DIGITS" function... For example...

SELECT ISNUMERIC('1D3'), ISNUMERIC('1E3'), ISNUMERIC('1.3'), ISNUMERIC('-13')

Yes, Sir! And I have to tell...

Dang, Lynn......

Sankar Reddy (1/26/2009)

---

Jeff, Thanks for your timely good spanking for using the TVF. I wouldn't use/propose this next time and you will see me use the numbers table here next...

GilaMonster (1/26/2009)

---

Jeff Moden (1/26/2009)

---

The backup to such a "break" for the quirky update method is that the ORDER BY method also works although it works at about half the speed....

GilaMonster (1/26/2009)

---

If that table is frequently inserted into, then I'd suggest put the cluster on the identity, even if it's never used. That way the cluster will organise the table...

As a side bar, I've finally discovered the true meaning of the acronym "CLR"...

[font="Arial Black"]C[/font]ursors, [font="Arial Black"]L[/font]oops, and other [font="Arial Black"]R[/font]BAR. 😛

Steve Jones - Editor (1/26/2009)

---

majorbloodnock, well said. SQL Server does work extremely well for the basics of what we need and it's definitely one of the most stable pieces of...

Luke L (1/25/2009)

---

Unfortunately, the reason why some very large companies end up dong this is that someone comes along and says he we need to represent x data y way......