Like the Incore Lookup Function, the Dynamic SQL Lookup Function is used on SQL sources like an RDBMS. However, Incore lookups aren't suitable for large data sets. That's because when the Incore lookup is initialized, the whole data set is read in, taking up memory. Dynamic SQL runs a SQL query with each lookup which reduces the memory footprint.
The overall performance of Dynamic SQL will be slower because the lookup data set isn't available in quick-access RAM during the transformation. So, use Dynamic SQL only if the Incore lookup isn't feasible.
Another difference is Dynamic SQL doesn't provide functions for writing as in Incore. Incore as Add and Write functions for adjusting the lookup data and saving it.
A Lookup Function in Map Designer allows you to supplement source data with reference data. Use Incore where possible, but if performance tanks, try Dynamic SQL.