Searched it for a while, thought it might be worth sharing.
Following is the T-SQL script to alter a table to apply a UNIQUE constraint on a certain column:
ALTER
TABLE [TableName] WITH NOCHECK
ADD CONSTRAINT UniqueConstraintName UNIQUE ([ColumnName])