Jeff, thanks for the great articles (part 1 and part 2 and look forward to part3).

Is there a way to pick a random value (like a color) from a subquery? here is what I tried but getting the same value on every run:

SELECT TOP (100)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID,--Sequential number from 1 to ..

(SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()) as RandomColor,

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

but if I run this piece alone, I am getting different colors:

SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()