MSBI Interview Questions

» July 4, 2010 | Labels: SQL Server, SSIS | 6 comments

There are basically 3 Cache Modes available in SSIS Lookup Transformation as follows:

Full Cache Mode: In this cache mode, SSIS queries the database before the start of the data flow task execution (pre-execute phase) and loads all the data from lookup / reference table into SSIS lookup cache. Following are few considerations while using Lookup in Full Cache Mode:

Size of available memory / cache should be taken into consideration before choosing this option.
Use this mode when the reference table is small OR
Use this mode when only a subset of reference table is loaded into lookup (using filter conditions in SQL command).

Partial Cache Mode: In this cache mode, SSIS queries the database against new rows coming in from the source and if there is a match then that row is cached into SSIS Lookup Cache for rows coming subsequently in the data flow which might have a match. When the cache becomes full, then SSIS removes few of the rows from cache based on the usage / match statistics for those rows and loads the new matching rows into the Lookup Cache. Following are few considerations while using Lookup in Partial Cache Mode:

Use this mode when the source data set or data to be processed is small OR
Use this mode when the reference table is large and only few of the records from the reference table will be used / will match against the source data rows.

No Cache Mode: In this cache mode, SSIS does not cache any rows into Lookup Cache (except for cases like two subsequent source data rows have a match with same lookup values etc). For every row coming from the source, the database is queried to get the matching value / data from the reference table. Here are few considerations while using Lookup in No Cache Mode:

Use this mode when less number of rows are being processed OR
Use this mode when the reference table is very frequently modified (Insertion / Updation / Deletion of records).

Few of the major differences between SSRS 2005 and SSRS 2008 are as follows:

SSRS 2005 report server requires IIS, whereas SSRS 2008 comes with a build-in web server and hence does not require IIS.
SSRS 2005 is multiple services architecture (Windows Service & IIS Service), whereas SSRS 2008 is a single service architecture (Windows Service) due to the presence of an inbuilt web server.
SSRS 2005 deployment & configuration involves lot of effort and a little bit of complexity due to the dependency on IIS, whereas SSRS 2008 deployment is much simpler especially with the help of Reporting Services Configuration Manager.
SSRS 2005 has less visualization components, whereas SSRS 2008 has a very rich set of visualization components like Dials, Gauges, Sparklines etc.

For more details, see the following articles:

HAVING clause can be used only with a GROUP BY clause, where as a WHERE clause can be used with constructs like SELECT, UPDATE, DELETE etc.
HAVING clause is applied as a filter to the data / output resulting from the GROUP BY clause, where as a WHERE clause is applied to every row in the SELECT, UPDATE, DELETE etc constructs.
In queries where both HAVING and WHERE clauses are used, WHERE clause is applied first (to every row in the SELECT statement to filter the records before they are fed to GROUP BY clause for aggregation) and then the HAVING clause is applied (to filter the aggregated result from GROUP BY clause).

TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.
TRUNCATE removes all the records from a table without making a log entry for individual row deletions (Does make log entries about data page deallocation etc, which can be used for ROLLBACK of a TRUNCATE command), whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.
TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (Optional) is used or not used respectively.
TRUNCATE cannot be used on a table if it satisfies one of the following conditions:

TRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITY.