All Forums Database
tdreturn 25 posts Joined 10/12
19 Mar 2015
SET Vs MULTISET

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?

SmarakDas 51 posts Joined 02/12
19 Mar 2015

Hello Kamin,

SET Table with NUPI has the overhead of checking for Duplicate Rows. With a UPI or USI, this overhead is removed as the Uniqueness Check is restricted to the UPI/USI Column(s). In my opinion, SET Table with UPI/USI has no DML overhead as compared to MultiSet with UPI/USI.

Thanks,
Smarak

Fred 1096 posts Joined 08/04
21 Mar 2015

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.

RATHOD 8 posts Joined 04/15
06 May 2015

Hi All,
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.
Thanks All.
RATHOD.

You must sign in to leave a comment.