How to update many rows with random values

performance, random, sql, sql-server, sql-update

Solution

You can use a SQL Server trick to generate a random number: `rand(checksum(newid()))`.

Update Network_Info_Detail
    set ART = (rand(checksum(newid()))*4000)+2,
        NRT = (rand(checksum(newid()))*1000)+2;

Problem

I would like to perform update query to table and set 2 columns to random values. Here is an example: `Update Network_Info_Detail set ART = (rand()*4000)+2, NRT = (rand()*1000)+2` It's obvious that all rows will be updated with the same value randomly generated so I needed to create a cycle to generate a random value for each row. `DECLARE @size integer SET @size = (SELECT Count(*) from Network_Info_Detail) While @size > 1 BEGIN Update top (@size) Network_Info_Detail set ART = (rand()*4000)+2, NRT = (rand()*1000)+2 SET @size = @size - 1 END` This script updates rows with different numbers, but it's very slow. Is there a way to improve the execution time?

Original source