mardi 14 mars 2017

T-SQL with random returns multiple rows where one is expected

Consider the following T-SQL code (MS SQL Server 2014) that fetches a random row from a table variable:

DECLARE @priceClasses TABLE (
    RowIndex INT,
    PriceClassID NVARCHAR(MAX))
;

INSERT INTO @priceClasses VALUES
    (0, 'RETAIL')
    ,(1, 'WHOLESALE')
    ,(2, 'WHOLESALE2')
;

DECLARE @priceClassesCount AS INT;
SET @priceClassesCount = (SELECT COUNT(*) FROM @priceClasses);

SELECT 
    PriceClasses.RowIndex,
    PriceClasses.PriceClassID 
FROM
    @priceClasses AS PriceClasses
WHERE
    PriceClasses.RowIndex = ABS(CHECKSUM(NEWID())) % @priceClassesCount;

The last SELECT statement, for some reason, sometimes returns 0 rows, sometimes 1, 2, or 3 rows.

It does not make sense to me. The value of the column is unique, and ABS(CHECKSUM(NEWID())) % @priceClassesCount cannot be multi-valued, can it?

The issue disappears when I declare RowIndex INT NOT NULL PRIMARY KEY instead of just RowIndex INT. Any thoughts?




Aucun commentaire:

Enregistrer un commentaire