CODEWITHSUNDEEP
sqlsql-servert-sql
RazorKillBen
RazorKillBen

Reputation: 573

Can SQL Server automatically assign a random int to an IDENTITY column?

I have a table of around 50,000 rows. The data is removed daily and the latest data of around 50,000 rows is imported in. This is a daily procedure.

The data needs to be sorted into a randomised order in the output, so the way we did this previously in Access was to set the identity to 'random'. This displayed a random integer, so when the data was sorted via this column, it'd be jumbled up nicely.

How can I replicate this with SQL Server? I can't seem to work out how this is possible using the seed and increment functions and I am aware that assigning a random number is fine until it pulls a duplicate (however small the odds). I'd really like to avoid having to run another procedure over the top of it to 'insert' the random numbers if possible but will give in if it's the only way.

Upvotes: 0

Views: 486

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Why not just add this to the queries you want?

order by newid()

I don't see a reason to overwrite the identity for something like this.

If you like, you can use newid() as the default for the "identity" column rather than identity(). This would give a "random" id for the purposes of sorting.

Upvotes: 2

Related Questions