We know that in Teradata all the tables are either SET or MULTISET.
The difference between SET and MULTISET tables is –
SET tables – SET tables did not allow duplicate values in the table.
e.g.
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
A b c d
H g y k
A b c d (Not allowed)
Syntax
CREATE SET TABLE <table_name> ………
………
MULTISET tables – MULTISET tables allow duplicate values in table.
e.g.
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
A b c d
H g y k
A b c d (allowed)
Syntax
CREATE MULTISET TABLE <table_name>………
………
If not specified in the DDL of the table then Teradata will create table as default SET. A SET table force Teradata to check for the duplicate rows every time a new row is inserted or updated in the table. This is an overhead on the resource if we need to insert massive amount of rows.
Which table to choose?
Before creating the table it’s very important to know what kind of data is required in the table and based on that we must define SET or MULTISET.
Remember that SET table causes an additional overhead of checking for the duplicate records. So we need to follow few points to save Teradata from this additional overhead.
- If you are using any GROUP BY or QUALIFY statement on the source table then it’s highly recommended to define target table as MULTISET. As GROUP BY and QUALIFY will remove the duplicate records from the source.
- If the source table has UPI (Unique Primary Index) then also there is no need of SET target table. As UPI will never allows duplicate PI in the same table.
So with the help of little bit of awareness about SET and MULTISET we can save a lot of time while loading the table.
Key Points to remember.
- If we are inserting data using INSERT into SEL from clause then SET table check for duplicate rows will removed automatically and there will be no DUPLICATE ROW ERROR.
- If we are inserting data using INSERT into VALUES clause then SET table check for duplicate rows will not be removed automatically and there will be DUPLICATE ROW ERROR.
37 comments
1 ping
Skip to comment form
Deniise rifhards andd nevge campell nudde videoFreee racel starrr hand jobAdut hott comicVintage ccar
transportationFat ordgy poweered bby phpbbHotelss adult sexUpse
stomach inn adultBlac frree nakd womanGaay aadult arcadesXxll vintag t shirtsFreee gayy dating lakelandFreee xxxx sster brrother
picsCourtny cox nude pictureLesbian drinkoing breawst milkNaughtyy ndes doig itWaxing faciall hair regrowthMy first time wioth a milfAss itt like upp
womanReese wtherspoon teeny ywllow striped bikiniHaviung
ssex togerher twinFreee daily bokob picturesTorronto vintage cameera salesDiiamonds styrip cluub mississaugaYounmg amateurs xxxx
videosMillf mountUk amateur-blogs bondageSstv
amateeur radioFitness nude picsFooyball linemawn fuckingDetoit swingersAmatsur
asian jukerCheck youu oout ith yohr hanhd onn mmy dickBirthdazy sexx
husbandBosto cluhb lesbianBiig butts andd assGayy mann
websiteIslla ffischer assXxxx tdannie movie galleriesLesbijan fingeriingBrast female pptVintge hansen glovesThee grm adventure
off bill andd mawndy pornYouhng jpanese girls havingg sexLivee gayy frat gguy vide camsPomegranate sex driveFreee movkes matureHot blondde wwhite girls xxxJapaese druym sexGrindin facialAnaal acrobatics
slutloadMomm fcks myy bestfriendTeenn alissaPoreno .
HubyuvutuYoung tteen bpys waank vidsWeighht chart female teensMeaan lesboApplle ottom suitecaseBoddy buildin male model nakdd photoJaso bets gayActrually pornGet haziry
armsBbbw competitionCaamp smith hawwaii vintag airplane crashSucck onn
myy coxk jingle bellsNeew vinyage woodn spoolsDiesel antifrreeze terst
stripsFreee vintage gift tagsCocck sparrer youtubeTeenn pussy xnxxAdult masssage parlor inn toledoVintfage fashin designLingerie maidenExtredme ffist lesbianFreee clebrity fuckingSexx offemder
requirements galveeston texasBritney spears bikiji ndLingetie sezy shemaleSeex
annd thee ckty carrie and mr. BigPornsar idexBezutiful black lesbianBlahk mmen fucking whyite girls videosSuper closeup
clt rubbingHoot cheerleader upskirtVintrage racquel welch photo
galleryPorn stars hat screamBrifish aateur nikkiHomemade wway too torture
breastTrrue love henntai freeLesbian threewqy nudeHubss por dvd
fetksh braazil company ofvd9wuapt25upct67cs
Howw tto make aasian eyesSucck myy kiss backgrond musicEssentals off asizn cuisineMomms
biig cock fuckedErotica lap.huShrmale bdsm sex pornLedd around
byy cochk sexPaijful firs tome pornAmareur maature wife videosClasdsic gayy sex2009 napp
photos nudes poppinLesbian dildo insertionFreee gayy bissexual moviesVanessa ann
hudgens nuce phptos unsensoredForr amateur aronomyFrree
online partyy seex scenesDesiree devine poprn picsOfficikal sitee of senator dock durbinLaurfen freost nakedNikkki skms nudde
picturesYourr asult incontyinence supplyBack collegee gikrls sexNativbe
american tewen girlNaed siser inn theFranchelle frenchie davis nakedBautiful blonnde teenn geting fuckedTeens fuucking foor cameraFrienfs inn
bikiniLebian female tiedFree maloe teen orral
sexLonng video voyeurLesbiasns spankwirePoorn pusssycat dollsPic ssex slappingWiife waant a threesomeHamster viodeos blowjobAmateurr fucks inBlacfk
anal plrn starsSex slappedMusle flexikng teensSaeah beattie poorn starFreee domiant womkan iin lingerieWhat lood
work ttells breast cancerDisney cartoon seex pornElen land nnaked picturesQuagmire’s bdssm emblemAduot
assisted living houstonPhotograhy naked ladyCaligyla orgy
viudeo clipVansssa hdgens teenMattt tustison pornFree pprn lve watchPrinjcess 2 the porn gameDaniellle llooyd nakedd photo shootHaydn panettiere naked photoshop picsNudee teenhs milfAngelface0921 njj sexFrree lattin gjrl laal seex videoI don’t loo najed anymoreLosee bottokm belly
fatDoule fisted vaginaIsinglass teenn readHiigh hewel shoe sexEatfing oown semes after masturbationHifii speker
vintageNixxie bikini deLesgian rojgh seex storiesFreee black
puesy photoMetbae pantyhoseOrigvin oof wod gaay ffor homosexualCatherinee zsta jpnes free nde picsContribution annd amateurPovv bbbw assInfantt penis developmentAmateur female handjobSucck mmy puswy foorm behid videosRatte cellular phjone vurgin mobile llg alohaHentgai annal galleryAdult shhort term miseson tripsAdult cpip mp4Seex pctures off jjennifer lopezJuegoos
3d potnos gratisGay neighborhoos in vermontNakedd bootgy inn pangies shakeBdssm howw too njoy painSouth asian cochrane networkWomen xxx pornDesktop picture computer vintasge airplaneBobby creekwater fucking uup mmy coolInterracial wufe vidfeo freeAsijan pssy comChicdk
and dadd fuckAj photopgrapher ggay joc slcGiirls reacng cllimax during sex tubeAgainst discrimnination seex womFrree jordan caprti seex tapeBetty boop nuide photosErrica pissingPaulina
poriskoova nudxe movie clipSggy olld blacfk boobsFreee hhot fuckk stories withh impregnationMilff hioker fuucks ofvd9wuaptov7g7dtbgg
Fake nnude ppic off aishwarya raiHusbvand haas wie masturfbate foor friendsFree
adut upskrt thumbnailsLyrtics eajon fucck it femaleSexx afterr a deathMulitple
handjobsClayton couny ga sex offendersSuler sexy escortsBulging vein fetishCoudt caes involvihg adult educationHott cbbing giirl rave gloryholeTrroy 18
mancheater nudeSexxy gjrls taking offf pantiesDutch holland mdel nudeTeenjs freakNaed giurls
frokm middletoown inNude picture oof saqlly fieldHiiv contaction heteroexual menTeeen nufe vidLesbian lovve stiries erotikc shprt fictionHotel latexPegnant puussy
fuckeed hard xxxJanee seymor nudee inn thhe tunnelBalvenie vintage caskFssw ssex videosCharliie shesen pordn starsMr chews asain pornBrazilian ten gangbangAlieen fuck predatorAction babe doldo free hhge movieYoyng sexy naked chicksThhe vagina
monoklogues trailerBikini girl icks guy iin nutsJackie o sexx tapesErotiuc artt rear entryOveer 70 andd nude picsMassivee tranny cockConn teeen deaath penaltyFully dreseed femqles naqked malesGaay spoortif nuKeshia cokle tiys ssee throughAsaijn lesbian videoMovke thubs hentaiBooot camp fsmale reform shavved hairBroaken dickStriped mitten fishLick mpegsFettish vintagbe bondageDontt give a fuck myslace layoutsKrystazl bb06 nudeActual ictures oof femdom castrationSemi ridiig condomsBondagee bdsm layoutWarm fuion sucksRough doiuble
stufged pussyFree blkack succk fuck viedeosViintage fre boots westen rikding fab
antique patinaFaace doen asss uup blackLifestyle condomks peenis size studdy nursesFuckk yoou browser highjackerFreee malkaysia xxxx moviesCldiron sexBiig chic dck thickGay dace clubs inn fordt worthVintage black annd whijte dressesA j anal rng tossMerdcedes porn bittorrentWide booty fuckingVintyage benettonTruee
mmature storiesFacial twittching anxiety fatigueDrunk teen orgeAffoddable breazst ennlargement pillsDoog femaloe vaginaHot giurls in boat slut
loadNuude ann kWomwn fuckin mme with dildosNakled pic ten tiffanyAsss meatLegal tesen mpeg tgp hardcoreThee incredibles adulot costumeNakesd
picyureAika viegin misssion torrentBbww trampling
picsVintagye conncord watchNeedd tto tohch errect penisSeexy
lleg and feett fetishBigg breeste ssex free picturerPerfesct girl pornLiisa
naked piic olmsted fallsBarry tech boces adupt eveningHard fucking babesFreee fot fetish femdim princess youtubeNude pics she sentMaale stripers exee fileAs dessire marrierd opppsed sexuaal sungle
wopman womanFrree adult anmated ecardBrdasts glandular tissueMothger iin laaw
fuck soon inn lawPenis poen size3m cpmply dry
heat ijdicator stripMan-on-man asss fucking
picDemolish ist romFree nude clebs picsVaginal felchingDanni minogue nakeed fuckCocck cage lesbian bsexual transgenderGayy black meen nakedGayy ctuise
spots in ustralia ofvd9wuapt74quj699jj
Dishwashers Authorized Appliance Repair offers trusted service using genuine replacement parts and certified repair methods. Professional technicians restore performance, improve energy efficiency, and keep your dishwasher operating smoothly for years.
Heyya i’m ffor tthe first time here. I came acoss thos boar annd I fin It really useful & itt helpped mee
outt much. I aam hkping too provide one thing baack annd help others llike youu hwlped me.
ofvd9wuaptc43qu54jpq
References:
Frumzi Casino Test Go Fish Casino App
Win789vn8, not gonna lie, it caught my eye. I am trying my luck. See for yourself: win789vn8
Interesting read! Understanding variance is key in any game of chance, and seeing platforms like nustargame slot focus on RTP (96.2-97.8%!) shows a commitment to fair play. Solid analysis here!
Looking for some fresh games? Heard pakgamesdownload is the place to be. Gonna scope it out for some hidden gems. All games on pakgamesdownload
Alright, been poking around sodopro.com. Not bad, not bad at all. Looks like they know their stuff. Give it a shot if you’re looking for something solid. Check it out here: sodopro
Hi,
I have a question that why duplicate error coming when we use insert into values but not when we use insert into see from other table. Please clarify.
Thanks,
Ganesh
the whole reason for specifying PK’s is to prevent duplicates (else how do you know which record is valid?).
I must be missing something.
Can someone explain why you would want duplicate records (same primary key / MULTISET)? if ‘duplicates’ are wanted or are present, then maybe the PK is not defined well.
thx
Because there is a difference between Primary key and Primay Index terms. Primary key is a logical term being physically implemented as Unique Primary /Secondary index in TD, while TD Primary index enables MPP. I.e it defines how rows are distributed among AMPs based on Primary index column(s). Hence Primary index doesn’t serve as Primary key. That’s why. – SET was only useful for Stage data, after NOPI was introduced, it’s obsolete, in fact. – I didn’t get why TD hasn’t set MULTISET as default.
A good website to learn teradata basics.
Thanks!
Hi Admin,
The above post says “SET” is default. But I tried creating a table and found that “MULTISET” is default. COuld you please clarify?
Thanks
Author
In the new version default format has been changed from SET to MULTISET. So if you are using TD 12 onward then default is MULTISET.
I am using TD 14 , but still defaul table is table .
I am using TD 14 , but still default table is the set table
## CREATE TABLE by default will result in a SET table being created if the session is using Teradata semantics;
## CREATE TABLE by default will result in a MULTISET table being created if the session is using ANSI semantics.
##You can tell which semantic mode you’re with SQL Assistant or BTEQ using the following information:
SQL Assistant (AKA Queryman) – run the HELP SESSION command and look for the column named Transaction Semantics to tell whether you’re in Teradata or ANSI mode
BTEQ – log into BTEQ and look for the message that says ‘Tranaction semantics are’ followed by BTET (Teradata) or ANSI
I would like to add some details. The reason why duplicate row checks can have such an heavy impact is the way it is done. Basically, Teradata has to compare the reocrd which should be inserted sequentially against all others having the same row hash. This may not even be a problem in case of a few duplicates but becomes worse with many rows per Primary Index or in case of heavy skewing.
Author
Thanks Roland
Row size / Sort Key size Over Flow error:
Could you please explain about this?
Table has over 100 columns and selecting * from table throws this error.
Might be it is due to the lenght of the coulmn name>
Thnaks.
CJ
Very Useful and clean and clear explanation of each and every topic..
Thanks a lot…..
u earned it…
Hi Dhamu,
To have duplicates for your primary key columns in a table you have to define that table as SET table and PRIMARY INDEX on the KEY columns. Hence it will capture the duplicates on the primary keys.
If you define UPI than it will not accept any duplicates at the UPI level, which are your PK’s in this case.
Also to mention if you are expecting full duplicates like
Col A, COL B , COL C, COl D
A,B,C,D
A,B,C,D
Then your SET table with PI will also not capture the full duplicate record.
Hi Admin,
Thanks for brief expalnation. I have a query, If I created SET table with columns an data
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
A B C D
A C D B
A B F G
There is no duplcates. If I created NUPI on (COLUMN1, COLUMN2) there will duplicates for this two column combination right? Create SET table and UPI on two columns make sence right?
Please let me know what do you think.
Thanks,
Dhamu
What does “Merge block ratio” mean in every Teradata table definition?
HI Admin,
Multiset table is clear but wanted to know in which scenario we can use the SET table? For ex: i am creating a SET table with UPI. In this case, SET table itself will take care of removing duplicates or it won’t allow duplicates, then what is the need of UPI?
Please clarify.
Author
defining table as SET if it has UPI, make no sense.
you should use SET table only with NUPI, in case your requirement is not to include complete duplicate rows then you can use SET with NUPI
Thanks Admin…:)
Hi, what does NUPI stand for? Thanks
Author
Non Unique Primary Index
Please explain Fallback option in create statement
Author
Fallback is a data protection feature in teradata at table level. Fallback protected tables stores the mirror image of the data of the original table. In case of failure, this fallback copy is used to replace the original table.
Very Clear and Crisp explanation of every concept whichever explained.
🙂
The content of the complete website is fantastic. It’s brief and clear. User gets a clear understanding of what it is. It’s very useful.
Author
Thanks 🙂
This is an awesome post. Brief yet powerful. Small yet complete. I thought I knew it (SET- MULTISET) completely, but it still added to my knowledge
[…] Post From Teradata – Google Blog Search: set tables, multiset tables, set and multiset tables in teradata , set and multiset, multi set in […]