Sql-server – How to make a unique column with default value of UUID

From my post about this issue here (see #1):

DECLARE 
  @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%',
  @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%',
  @s NVARCHAR(MAX) = N'';

;WITH t AS (
    SELECT t.[object_id], [table] = t.name, [schema] = s.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE EXISTS (
        SELECT 1 FROM sys.columns
          WHERE [object_id] = t.[object_id]
          AND (system_type_id IN (35,99) -- text, ntext
          OR (system_type_id IN (167,175,231,239) -- (n)(var)(char)
            AND (max_length = -1 OR max_length >=32) -- max or >= LEN(@Search1)
             )))
)
SELECT @s = @s + N'SELECT N''' 
    + REPLACE([schema],'''','''''') + '.' 
    + REPLACE([table], '''','''''') + ''',* 
 FROM ' + QUOTENAME([schema]) + '.' + QUOTENAME([table]) + '
 WHERE ' + STUFF((SELECT '
 OR ' + QUOTENAME(name) + ' LIKE ' + CASE 
      WHEN system_type_id IN (99,231,239) 
      THEN 'N' ELSE '' END
      + '''' + @Search1 + '''' -- run again with @Search2
    FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND system_type_id IN (35,99,167,175,231,239)
    ORDER BY name
    FOR XML PATH(''), TYPE
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';

'
FROM t;

PRINT @s;
-- EXEC sp_executesql @s;

When you have a unique constraint that you want to apply to only a subset of rows, you can enforce this using a unique, filtered index. The index that seemed to work for you in this case is:

CREATE UNIQUE INDEX [UNQ_SampleTable_Code]
  ON dbo.[SampleTable]([Code])
  WHERE   ([Deleted] = 0);

This ensures that only one distinct value of Code can exist for rows where Deleted is 0, but duplicates can exist where Deleted is 1. Typically this will also help the performance of some queries, since you will often be interested in only the active rows (and not the soft deletes), but you may want to consider adding columns to the INCLUDE clause if this doesn't cover queries (SQL Server may choose to scan the clustered index, or a different index, if lookups are deemed too costly).