Random DEFAULT string for a column using CASE and NEWID
Bill Leung, 2012-06-14 (first published: 2008-01-16)
The script is self-explanatory. It can be expanded to more random strings by using a different modulo. Using a RAND() is no good if you were to load data with a single SELECT INTO or bcp because the same random seed is used.
CREATE TABLE TESTB
( COL1 INT
, COL2 CHAR(08) DEFAULT CASE CAST( ASCII (SUBSTRING( CAST( NEWID() AS VARCHAR(64)), 1, 1)) AS INT) % 2
WHEN 0 THEN 'OPTION 1'
ELSE 'OPTION 2' END