DSXchange

What is the difference between SPARSE and Normal lookup and what situation we are supposed to use them.

The "normal" lookup takes the complete contents of the link and loads it to a virtual dataset in memory. Each row being looked up is already in memory, so is quite fast.

"Sparse" lookups actually do a lookup/select for each row separately.

Each is used in a different scenario. If your data has 100 records and your lookup data is 10,000,000 rows you had best use a Sparse lookup; if the row counts are reversed you had best use a normal lookup.

Another aspect is that a normal lookup takes a snapshot of the lookup contents at the beginning of the run; if the reference data changes during the runtime of the job then these changes will not be reflected in the lookup data. Sparse lookups, on the other hand, will reflect such runtime data changes as soon as they occur.

More pedantically, as soon as they have been committed.

Ray - I didn't specify "commited" intentionally, because a lookup can also be against non-database sources; so to be complete one would need to say committed or written (and SYNCd) or otherwise made visible.