9nWxRWsQ

1update [user]
2set UFID = UFID + '_dup_removal'
3where ufid in (
4  select ufid 
5  from [user]
6  group by ufid
7  having count(ufid) > 1
8)
9	
10USE tempdb;
11
12IF OBJECT_ID('user') IS NOT NULL
13    DROP TABLE [user];
14
15CREATE TABLE [user] (
16    UFID varchar(max)
17)
18
19INSERT INTO [user] VALUES(1),(2),(3),(4),(4),(4),(3),(5);
20
21
22WITH ranked_users AS (
23    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY UFID ORDER BY (SELECT NULL))
24    FROM [user]
25)
26update ranked_users
27set UFID = UFID + '_dup_removal_' + CAST(RN AS varchar(10))
28where RN > 1;
29
30
31--CREATE UNIQUE INDEX IX_UFID ON [user](UFID) -- FAILS
32
33-- Solution 1: add a computed column with the size trimmed down 
34ALTER TABLE [user] ADD shortened_ufid AS CAST(UFID AS varchar(900))
35
36CREATE UNIQUE INDEX IX_shortened_UFID ON [user](shortened_ufid) 
37
38-- Solution 2: add a computed column with the hashed version of the data
39ALTER TABLE [user] ADD hashed_ufid AS CAST(HASHBYTES('SHA1', UFID) AS bigint)
40
41CREATE UNIQUE INDEX IX_hashed_UFID ON [user](hashed_UFID) 
42
43SELECT *
44FROM [user]
45GO
46	
47-- solution 3: use a trigger
48CREATE TRIGGER TR_no_dupes ON [user]
49FOR INSERT, UPDATE
50AS
51BEGIN
52
53
54    IF EXISTS (
55        SELECT 1
56        FROM [user] 
57        WHERE UFID IN (
58            SELECT UFID 
59            FROM inserted
60        )
61        GROUP BY UFID
62        HAVING COUNT(*) > 1
63    )
64    ROLLBACK;
65
66END