Just a quickie Random Number solution. If anyone tried this before: SELECT *, RAND() FROM myTable; you will be surprised that the random number is all the same/identical.
There are ways around it, depending on what you want to do. 1. If you just want the random number for the purpose of sorting, then this will do nicely: SELECT TOP 5 * FROM myTable ORDER BY NEWID();
2. If you need an INT random number for each row, then try this: SELECT *, CHECKSUM(NEWID()) AS rnd FROM myTable; *Note: The random number will range from -2,147,483,648 to 2,147,483,647. If you do not want the negative number, ABS() it.
3. If you need a FLOAT random number >0 and <=1 (the normal random number). This MAY be useful, although I think there is a more performance friendly way. SELECT *, ABS(CONVERT( INT, CONVERT( BINARY(4), NEWID() ) ) / 2147483647.0) AS rnd FROM myTable; -OR- SELECT *, ABS( CHECKSUM(NEWID()) / 2147483647.0) AS rnd FROM myTable; *Note: I have not determine for sure which is more efficient, but guts feeling tells me the data-type CONVERSION should be faster than the CHECKSUM function.
Hope these quickies help someone. have a nice day.