title: SQL Server random using seed
body: <p>I want to add a column to my table with a random number using seed.
  If I use RAND:</p>
  
  <pre><code>select *, RAND(5) as random_id from myTable
  </code></pre>
  
  <p>I get an equal value(0.943597390424144 for example) for all the rows, in the random_id column. I want this value to be different for every row - and that for every time I will pass it 0.5 value(for example), it would be the same values again(as seed should work...).</p>
  
  <p>How can I do this?</p>
  
  <p>(
  For example, in PostrgreSql I can write</p>
  
  <p><code>SELECT setseed(0.5);
   SELECT  t.* , random() as random_id
      FROM myTable t</code></p>
  
  <p>And I will get different values in each row.
  )</p>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>After I saw the comments here, I have managed to work this out somehow - but it's not efficient at all.
  If someone has an idea how to improve it - it will be great. If not - I will have to find another way.</p>
  
  <p>I used the basic idea of the example in <a href="http://technet.microsoft.com/en-us/library/ms177610.aspx" rel="nofollow">here</a>.</p>
  
  <p><strong>Creating a temporary table</strong> with blank seed value:</p>
  
  <pre><code>select * into t_myTable  from (
  select t.*, -1.00000000000000000 as seed
         from myTable t
         ) as temp
  </code></pre>
  
  <p><strong>Adding a random number</strong> for each seed value, one row at a time(this is the bad part...):</p>
  
  <pre><code>USE CPatterns;
  GO
  DECLARE @seed float;
  DECLARE @id int;
  DECLARE VIEW_CURSOR CURSOR FOR
  select id
  from t_myTable t;
  OPEN VIEW_CURSOR;
  FETCH NEXT FROM VIEW_CURSOR
  into @id;
  set @seed = RAND(5);
  
  WHILE @@FETCH_STATUS = 0
     BEGIN
        set @seed = RAND();
           update t_myTable set seed = @seed where id = @id
  
        FETCH NEXT FROM VIEW_CURSOR
           into @id;
  
     END;
  CLOSE VIEW_CURSOR;
  DEALLOCATE VIEW_CURSOR;
  GO
  </code></pre>
  
  <p><strong>Creating the view</strong> using the seed value and ordering by it</p>
  
  <pre><code>create view my_view AS 
  select row_number() OVER (ORDER BY seed, id) AS  source_id ,t.*
         from t_myTable t
  </code></pre>