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?