I am having a problem creating an index. When I check the table for dupes I don't find any. When I try to create the key I get an error that there are duplicated values. I think that this may be because the key is too long I don't see any other reason. The data looks OK.
Each client should only have one record for a date (the 1st of the month) and a paysource.
create unique index UniqueArId on AR(CLIENTID, [DATE], PaysourceUUID)
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '400007'.
The statement has been terminated.
Int datetime uniqueid
400007 2005-04-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-05-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-06-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
402161 2004-01-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-02-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-03-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
Proglem creating index
scottd_arch
from AR
group by CLIENTID, [DATE], PaysourceUUID
having count(*) > 1
order by dupes desc;
zaxxys
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
Jonathan Lo