title: Generate a random number which is not there in a table in sql server
body: <p>I am looking for generating a random number which the generated number is not there on another table.</p>
  
  <p>For Example: If a table called <code>randomNums</code> having the values <code>10,20,30,40,50</code>.</p>
  
  <p>I like to generate a number apart from the above values.</p>
  
  <p>I tried the following query.</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>;WITH CTE AS
  (
      SELECT FLOOR(RAND()*100) AS rn
  )
  SELECT rn FROM CTE
  WHERE rn NOT IN (SELECT num FROM randomNums);
  </code></pre>
  
  <p>But sometimes this query returns nothing.<br/>
  Because that time it generates the number which is there in the table <code>randomNums</code>.</p>
  
  <p>How to solve this issue?</p>
  
  <p><strong><a href="http://sqlfiddle.com/#!6/d7ce21/1" rel="nofollow noreferrer">Fiddle for reference</a></strong></p>