I understand that a SET table has an overheard compared to a MULTISET table. The question I have is if we have a SET table with a Unique Index on it, does it help to make it a MULTISET or it does not matter?
Some duplicate row checking must still be done even with a unique index, since SET table semantics call for duplicate rows to be quietly eliminated (no error thrown) in multi-row operations such as INSERT/SELECT.
Checking is done against rows with the same RowHash. Having UPI means that usually there would be no more than one row to check, so the overhead is minimal. That's not true for USI.
I am confused with the usage of multiset table in real time.
what exactly i mean is
where do we require the duplicate rows and what is the use of complete duplicate row in real time.
why we store complete duplicate records and uses of it???
In which environment the complete duplicate record is useful??
please make me clear with this concept with any real time scenario usage of complete duplicate records.