CHECKSUM(NewId()) executes multiple times per row

Question

When reviewing code I came across something odd, someone had read that you can use ABS(CHECKSUM(NewId())) % N to get you random numbers from 0 to N-1 (as RAND() doesn't occur per-row), but I suspect they didn't really test their code (simplified down now with table variables, and actual code did a TOP 1 country to incorrectly get around a problem below):

DECLARE @Values TABLE
(
    id int identity,
    country VARCHAR(100)
)

INSERT INTO @Values (country) VALUES ('UK'), ('USA'), ('China')

SELECT *, (SELECT country FROM @Values v WHERE v.id = ABS(CHECKSUM(NewId())) % 3 + 1)
FROM @Values

When executed the following error occurs:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My first question was how can the subquery return more than one value? and how is it that it some times returns no value at all? (Note not the question that I'm asking at SO) Tested by:

SELECT country FROM @Values v WHERE v.id = ABS(CHECKSUM(NewId())) % 3 + 1

Yet no matter how many times the following is executed:

SELECT ABS(CHECKSUM(NewId())) % 3 + 1

The returned results are always 1,2,3 (this was the 'testing' the programmer had used when writing their code)

From all this, I suspect this is because it's being re-executed for every compare and not every row, can someone confirm this and provide a good link to explain this behaviour so that I can point it out to him?

I don't get what you're saying here. You're saying that they picked the checksum route because `RAND` was not evaluated per row, and now you're asking why it *is* evaluating per row? — Lasse V. Karlsen, Feb 27 '12 at 11:33
@LasseV.Karlsen - Looks like they need something that is evaluated once per outer query row but stable when passed into the sub query. `SELECT *, (SELECT country FROM @Values v WHERE v.id = CA.id) FROM @Values CROSS APPLY (SELECT ABS(CHECKSUM(NewId())) % 3 + 1) CA(id)` does this but isn't guaranteed to. — Martin Smith, Feb 27 '12 at 11:42
@MartinSmith if you post that as an answer I'll accept that. — Seph, Feb 27 '12 at 12:24
`SELECT *, (SELECT country FROM @Values v WHERE v.id = CA.id) FROM @Values CROSS JOIN (SELECT ABS(CHECKSUM(NewId())) % 3 + 1) CA(id)` works too (as (non-)reliably as CROSS APPLY, possibly). — Andriy M, Feb 28 '12 at 05:57
[Blogged about this here](http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression). — Aaron Bertrand, Jun 12 '14 at 15:59

GSerg · Accepted Answer · 2012-02-27T11:18:38.390

2

Why, this is expected.

ABS(CHECKSUM(NewId())) % 3 + 1 is evaluated once for each row in @Values, and therefore has different values for each row.

So it can return multiple rows, or no rows at all.

This is exactly what you should be expecting when saying, This expression evaluates for every row.

Apparently what you actually want is an expression that evaluates once per query.

edited Feb 27 '12 at 11:18

answered Feb 27 '12 at 11:13

GSerg

76,472
17
159
346

score 0 · Answer 2 · answered Feb 27 '12 at 12:41

0

newID will always be called once per row, if you need a unique value use something like:

ROUND(((3) * RAND() + 1), 0)

answered Feb 27 '12 at 12:41

Diego

34,802
21
91
134

that only calculates it's value once per query execution, `CROSS APPLY (SELECT ABS(CHECKSUM(NewId())) % 3 + 1) CA(id)` as pointed out by @Martin will give a unique value for each row in the parent table, while not being called every time in the inner query – Seph Feb 27 '12 at 14:22

CHECKSUM(NewId()) executes multiple times per row

2 Answers2