How to populate empty columns with unique values in SQL Server?

Question

I have a table which have a row containing user emails and I want to add a constraint to make this row unique. The problem is that there are some (around 200) columns with empty values.

I need to generate dummy values and insert them to the empty columns.

How can I do it?

Thanks

Answer 1

Description

You can use the T-SQL function newid()

newid() Creates a unique value of type uniqueidentifier.

Sample

UPDATE MyTable set Column = newid() where Column is null

More Information

• MSDN - NEWID (Transact-SQL)

Answer 2

If you use SQL Server 2008 - consider unique index by email which filters out null values, smth like that:

CREATE UNIQUE INDEX [UX.Email, not null] on dbo.YourTable(email)
WHERE email is not null

OR

For versions of SQL Server prior to 2008 you can use the value of your primary key identity of row, if you have one

update yourtable set email = id where email is null

If you have no such a key, at least you can use NEWID() function instead of id