hrvoje.piasevoli (8/24/2010)

---

Hi all, played for a while with Hugo's example and ... look what I found :w00t:

paul.knibbs (8/24/2010)

---

Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!

Hugo Kornelis (8/24/2010)

Super! Thanks for testing and reporting back here.

Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.

paul.knibbs (8/24/2010)

---

Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.

Jeff Moden (8/24/2010)

---

Oh jeez.... here we go with bloody semantics again. If data is missing or absent, what value is it, Hugo?

Hugo Kornelis (8/24/2010)

---

Here is an interesting experiment (works on every client):

SELECT ROUND(789.98,-3) AS WeirdCol

INTO WeirdTable;

go

EXEC sp_help 'WeirdTable';

go

UPDATE WeirdTable

SET WeirdCol = WeirdCol - 0.001;

go

DROP TABLE WeirdTable;

go

Alex-668179 (8/24/2010)

---

Yes, I find that a lot of the confusion goes away by not calling NULL a "value" but seeing as a state (or state indicator).

da-zero (8/24/2010)

---

I would've liked a paragraph on the behaviour of NULL in COUNT(*) and COUNT(DISTINCT *).