Sql update row with random number

I've just tried this approach on a table with , rows and it ran in 11 seconds. Well, it's close. But it will always update the same number of rows.

I think I need a loop to update a random number of rows. Yeah, the performance is fine, I got k rows in 25s. This isn't the kind of thing that will be in prod code. OK, so add a line or two beforehand to randomly determine how many rows to update, between some range, and use that absolute figure instead of the "50 percent". Show 2 more comments. David Pfeffer David Pfeffer OK, but I've heard that when you do a getdate as part of a where in a select that getdate will run once for every row in the select.

Is that not correct? In this case, nearly every function will be run deterministically because update is atomic. Therefore, even if a normally non-deterministic function is called, the schema-bound nature of it is negated by the fact that the underlying data cannot change during the query execution. I think this has to be done iteratively unless you can force the query plan compiler to treat the function as non-deterministic despite the fact that it won't want to do so.

RAND persists within a query. Quassnoi Quassnoi k 88 88 gold badges silver badges bronze badges. Um, no. Hogan Hogan Nope, I ran the update with your where and it updated all the rows every time I ran it. This is not related to the OPs question. A filtered index worked great for me as I was filtering based on payment statuses. When I say the performance increase was massive I mean it was essentially instantaneous when updating a small number of rows.

With the right indexes I was able to achieve an update that took the same amount of time as the 'inner' query does by itself:. HOW TO? Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How can I fill a column with random numbers in SQL? I get the same value in every row Ask Question. Asked 10 years, 11 months ago. Active 3 years ago. Viewed k times.

Improve this question. NibblyPig NibblyPig Add a comment. Active Oldest Votes. Improve this answer. Andomar Andomar k 44 44 gold badges silver badges bronze badges. This is not working for me at all. Does the column have to be INT? Error every time. Reaching for the crazy pills Add some sample data to your question, should help with getting something to address your specific problem. You don't need the "ABS Checksum ".

Just "order by newid " will do the trick — jean. Show 2 more comments. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.

Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta.