sql server 2008 - Get a random value from a range in MS SQL? -

let suppose table can have values 000 999 (three digits , less 1000)

some of values filled. let's suppose table has

000,002,005,190 (001,004,003,006,..189,191,..,999 can inserted table)

and these values randomly allocated 000 , 002 in table 001 not in table yet.

how can values can insert table yet.

declare @t table (value char(3))  insert @t values ('000'),('002'),('005'),('190')   ;with rncte (     select -1 + row_number() on (order type, number, name) rn     master.dbo.spt_values ) select right('000' + cast( rn varchar(11)),3) rncte not exists    (   select 1 @t                          value = rn                     ) , rn < 1000 

edit

this query works generating complete list of possible numbers system table (master.dbo.spt_values) guaranteed contain more 1000 rows inside cte rncte. -1 added row_number have values start @ 0 rather 1.

the outer query 0 pads numbers display, returning not in source data , less 1000.