Random Data Using SQL Default Values

Wow, I decided to take a bit of time off over the winter and the next thing I know it's been two months since the last  SQL Banana update!

While faffing around over the last few weeks I came across a few code snippets I found quite useful.  Here's the first :)

Often I need to fill a table with some random dummy data.  Lot's of people have different ways of doing this, and I don't want to get into a discussion of what "random" actually means when working with computers, but 99% of the time this approach works just fine for me.

First create your table.  Set a default constraint to use the NEWID() function - this will generate a uniqueidentifier value.  The value will be unique every time it is generated.  Then use the CHECKSUM() function around it to compute it's hash value,  for our purposes effectively generating an int based on the uniqueidentifier value.

CREATE TABLE dbo.Dummy
(
Id INT IDENTITY (1,1) PRIMARY KEY,
IntData INT CONSTRAINT DummyDefault DEFAULT CHECKSUM(NEWID()))

Next, insert as much row data as required:

INSERT INTO dbo.Dummy DEFAULT VALUES
GO 500

This will run the insert 500 times, each time the identity column will increment, and a random integer will be inserted into the IntData field.

If you require varchar data then simply adjust the default constraint as required. EG:

CharData VARCHAR(MAX) CONSTRAINT TestCharDefault DEFAULT CAST(NEWID() as VARCHAR(MAX)) 

And that's it. Dummy data is now ready for use.