What is seed in RAND function in SQL Server
Microsoft docs explains this in details here: apparently RAND() returns a really random result only if it is called without specifying a seed value (because in this case seed itself is assigned randonly).

So calling RAND() without seed should always return a different value, while calling RAND(seed) should always return the same result if called with the same seed.

Here is an excerpt from Microsoft docs:


  Arguments seed Is an integer expression (tinyint, smallint, or int)
  that gives the seed value. If seed is not specified, the SQL Server
  Database Engine assigns a seed value at random. For a specified seed
  value, the result returned is always the same.
  
  Remarks 
  Repetitive calls of RAND() with the same seed value return the
  same results.