SQL Server: how can i generate unique random number at each row of record?

Question

I'm using SQL Server 2000. I'd like to generate random number without repetition at each row. However, my code is not working. I tried many times but i cannot fix the repetition problem. My code as below: I used "case" to handle duplicate number but not work. Anyone can help me? So much Thanks!

DECLARE @index INT
    ,@ran_no INT
    ,@counter INT
    ,@i INT

SET @i = 1

WHILE @i <= 18
BEGIN
    SELECT @index = count(basket_seq)
    FROM dbo.test
    WHERE basket_seq = @i
        AND flag = 'left'

    BEGIN
        UPDATE dbo.test
        SET @ran_no = convert(INT, 1 + @index * RAND(CHECKSUM(NEWID())))
            ,@ran_no = (
                CASE 
                    WHEN convert(INT, 1 + @index * RAND(CHECKSUM(NEWID()))) NOT IN (
                            SELECT seq_no
                            FROM dbo.test
                            WHERE basket_seq = @i
                                AND flag = 'left'
                            )
                        THEN @ran_no
                    END
                )
            ,seq_no = @ran_no
        WHERE basket_seq = @i
            AND flag = 'left'
    END

    SET @i = @i + 1
END

Answer 1

Just check this link

 SELECT RAND(CAST(NEWID() AS VARBINARY))
   FROM dbo.SysObjects

For your update statement, use like this.

update  dbo.test
set seq_no =  RAND(CAST(NEWID() AS VARBINARY)) --directly given this function here, 
where basket_seq = @i  and flag = 'left'

Also as per MSDN link you can use Random function too for Sqlserver2000.

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

Answer 2

SELECT RandNumber -- you can set @ran_no with this value in your sql query.
FROM   (
           SELECT ABS(CAST(NEWID() AS BINARY(6)) %500) + 1 RandNumber  --To set max limit number you can replace 500 with any number.
           FROM   sysobjects
       )            SAMPLE
GROUP BY
       RandNumber

As you said, you want to set value in seq_no, in your query you have set @ran_no to seq_no so you should try like below.

SET    @ran_no        = (SELECT TOP 1 ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1 FROM   sysobjects)

To generate random number without repetition at each row. Try something like above. will help you.