Question 1

What is the difference between DELETE, TRUNCATE and DROP?

Accepted Answer

In short: DELETE (DML) removes rows one by one, supports WHERE, fires triggers, logs each row and keeps the identity counter. TRUNCATE (DDL) removes all rows by deallocating pages, is minimally logged and much faster, resets IDENTITY, doesn t fire triggers and isn t allowed on tables referenced by a foreign key. DROP removes the whole table definition, data, indexes and permissions. In SQL Server all three can be rolled back inside an explicit transaction.

Question 2

What are the different types of joins?

Accepted Answer

In short: INNER JOIN (matching rows in both), LEFT/RIGHT OUTER JOIN (all rows from one side + matches, NULLs otherwise), FULL OUTER JOIN (all rows from both), CROSS JOIN (Cartesian product), SELF JOIN (table joined to itself, e.g. employee–manager). T-SQL also has CROSS APPLY/OUTER APPLY for correlated table expressions. Physical algorithms: nested loops, merge, hash.

Question 3

What is the difference between WHERE and HAVING?

Accepted Answer

In short: WHERE filters individual rows before grouping and can t use aggregates. HAVING filters groups after GROUP BY and can use aggregates ( HAVING COUNT(*) 5 ). Put non-aggregate conditions in WHERE for performance — fewer rows get grouped.

Question 4

What is a primary key vs a unique key vs a foreign key?

Accepted Answer

In short: Primary key: uniquely identifies each row, NOT NULL, one per table, creates a clustered index by default. Unique key: enforces uniqueness on other columns, allows one NULL in SQL Server, many per table, creates a non-clustered index by default. Foreign key: a column referencing another table s primary/unique key to enforce referential integrity (with cascade options).

Question 5

What is normalisation? Explain 1NF, 2NF and 3NF.

Accepted Answer

In short: Organising tables to reduce redundancy and update anomalies. 1NF: atomic columns and no repeating groups. 2NF: 1NF and every non-key column depends on the whole primary key (no partial dependency — relevant for composite keys). 3NF: 2NF and no transitive dependency (non-key columns depend only on the key, e.g. move DeptName out of Employees into Departments). Data warehouses deliberately denormalise for read speed.

Question 6

Clustered vs non-clustered index?

Accepted Answer

In short: A clustered index sorts and stores the table s data rows in key order — the leaf level is the data; only one per table. A non-clustered index is a separate structure with key columns and a pointer (clustered key or RID) back to the row; up to 999 per table. Clustered is ideal for range queries on the key; non-clustered for selective lookups on other columns, ideally covering with INCLUDE columns.

Question 7

Write a query to find the second (or Nth) highest salary.

Accepted Answer

sql Copy -- Handles ties correctly; change 2 to N SELECT DISTINCT Salary FROM ( SELECT Salary, DENSE_RANK () OVER ( ORDER BY Salary DESC ) AS rnk FROM dbo.Employees ) t WHERE rnk = 2 ; -- Alternative SELECT MAX (Salary) FROM dbo.Employees WHERE Salary ( SELECT MAX (Salary) FROM dbo.Employees); -- OFFSET/FETCH SELECT DISTINCT Salary FROM dbo.Employees ORDER BY Salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY ;

Question 8

How do you find and delete duplicate rows?

Accepted Answer

sql Copy -- find SELECT Email, COUNT ( * ) AS Cnt FROM dbo.Customers GROUP BY Email HAVING COUNT ( * ) 1 ; -- delete all but one (keep lowest Id) WITH d AS ( SELECT * , ROW_NUMBER () OVER ( PARTITION BY Email ORDER BY CustomerId) AS rn FROM dbo.Customers ) DELETE FROM d WHERE rn 1 ; Then add a UNIQUE constraint so it can t happen again.

Question 9

What are the ACID properties of a transaction?

Accepted Answer

In short: Atomicity — all statements in a transaction succeed or none do (rollback on failure). Consistency — a transaction moves the database from one valid state to another, respecting constraints, keys and rules. Isolation — concurrent transactions don t see each other s uncommitted work, controlled by isolation levels and locks/row versions. Durability — once committed, changes survive crashes because the log is hardened to disk (write-ahead logging). Example: a money transfer debits one account and credits another inside one transaction so it can never half-complete.

Question 10

Explain ROW_NUMBER, RANK and DENSE_RANK.

Accepted Answer

In short: All are window ranking functions. For salaries 100, 90, 90, 80: ROW_NUMBER gives 1,2,3,4 (unique, arbitrary order among ties), RANK gives 1,2,2,4 (ties share a rank, gaps follow), DENSE_RANK gives 1,2,2,3 (ties share, no gaps). Use ROW_NUMBER for deduplication and paging, DENSE_RANK for Nth highest with ties.

Question 11

What is a CTE? When would you use a recursive CTE?

Accepted Answer

In short: A Common Table Expression ( WITH name AS (...) ) is a named temporary result set for a single statement — improves readability, can be referenced multiple times in that statement, and supports recursion. A recursive CTE has an anchor member UNION ALL a recursive member referencing the CTE; used for hierarchies (org charts, category trees, bill of materials) and generating sequences. Default max recursion 100 ( OPTION (MAXRECURSION n) ).

Question 12

Stored procedure vs function?

Accepted Answer

In short: Procedures can return multiple result sets, output parameters and an integer return code; can modify data, use transactions and TRY/CATCH; are called with EXEC and can t be used inside SELECT. Functions return a scalar or a table, must be side-effect free (no INSERT/UPDATE to tables), can t use TRY/CATCH or transactions, and can be used in SELECT/WHERE/JOIN/APPLY. Prefer inline TVFs for reusable query logic.

Question 13

What are the transaction isolation levels in SQL Server?

Accepted Answer

In short: READ UNCOMMITTED (dirty reads possible; NOLOCK ), READ COMMITTED (default; no dirty reads), REPEATABLE READ (no non-repeatable reads), SERIALIZABLE (no phantoms; range locks), SNAPSHOT (row versioning, consistent view as of transaction start, readers don t block writers), and READ COMMITTED SNAPSHOT (database setting making read committed use versioning per statement; default in Azure SQL Database). Higher isolation = more consistency, more blocking.

Question 14

What is a deadlock and how do you handle it?

Accepted Answer

In short: Two (or more) transactions each hold locks the other needs, so neither can proceed. SQL Server s lock monitor detects it and kills the cheapest transaction (error 1205). Investigate with the system_health Extended Events session (deadlock graphs). Prevent: access tables in a consistent order, keep transactions short, add indexes so fewer rows are locked, avoid user interaction inside transactions, use RCSI/SNAPSHOT for readers; and retry 1205 in the application.

Question 15

Temp table vs table variable vs CTE?

Accepted Answer

In short: Temp tables ( #t ) live in tempdb for the session/proc, have statistics, support any indexes, and are rolled back with transactions — good for larger intermediate data. Table variables ( @t ) are scoped to the batch, have no column statistics (estimates improved by 2019 deferred compilation), aren t affected by rollback, best for small sets. CTEs aren t stored at all — they re inlined into the single statement.

Question 16

What makes a query non-SARGable? Give examples.

Accepted Answer

In short: Using functions, calculations or conversions on an indexed column in WHERE/JOIN prevents index seeks: WHERE YEAR(OrderDate) = 2026 , WHERE ISNULL(Col, 0) = 5 , WHERE Col + 1 = 10 , LIKE %abc , implicit conversions (nvarchar parameter vs varchar column). Rewrite as ranges or move the calculation to the parameter side: OrderDate = 2026-01-01 AND OrderDate 2027-01-01 .

Question 17

What is parameter sniffing and how do you fix it?

Accepted Answer

In short: When a parameterised query/proc compiles, the optimizer sniffs the first parameter values to estimate row counts and caches that plan. If data is skewed, later calls with different values reuse a plan that s bad for them (e.g. a nested-loop plan built for a customer with 5 orders reused for one with 5 million). Fixes: OPTION (RECOMPILE) for infrequent queries, OPTIMIZE FOR UNKNOWN /specific value, local variables (generic estimate), separate procedures for different data shapes, Query Store plan forcing, and SQL Server 2022 s Parameter Sensitive Plan optimisation.

Question 18

How do you approach tuning a slow query?

Accepted Answer

In short: 1) Reproduce and measure ( SET STATISTICS IO, TIME ON , actual execution plan, Query Store history). 2) Read the plan: scans on large tables, key lookups, bad estimates, spills, implicit conversions, parallelism issues. 3) Fix the query: SARGable predicates, remove SELECT * , avoid scalar UDFs/cursors (set-based), rewrite correlated subqueries, correct data types. 4) Fix indexes: covering/composite indexes in the right column order, filtered indexes, remove unused. 5) Update statistics; handle parameter sniffing. 6) Check blocking/waits ( sys.dm_exec_requests , sp_WhoIsActive ). 7) Validate the improvement and watch write overhead.

Question 19

A report query that used to take 2 seconds now takes 2 minutes. Nothing in the code changed. What do you check?

Accepted Answer

In short: Something changed in data or plans. Check Query Store for a plan regression (different plan since date X) and force the old plan as a stopgap; check statistics staleness after big data loads (update them); parameter sniffing (different first-call parameters); data growth crossing a threshold; new blocking ( sp_WhoIsActive , wait stats); index changes or dropped indexes; server pressure (memory grants, tempdb spills, CPU). Then fix permanently with indexes/query rewrite.

Question 20

Write a query to get each department's top 3 earners.

Accepted Answer

sql Copy WITH ranked AS ( SELECT e. * , DENSE_RANK () OVER ( PARTITION BY DeptId ORDER BY Salary DESC ) AS rnk FROM dbo.Employees e ) SELECT d.DeptName, r.FullName, r.Salary, r.rnk FROM ranked r JOIN dbo.Departments d ON d.DeptId = r.DeptId WHERE r.rnk = 3 ORDER BY d.DeptName, r.rnk; Use ROW_NUMBER for exactly 3 rows per department regardless of ties; DENSE_RANK to include ties.

Question 21

Users report the Orders screen freezes at month-end when the finance job runs. How do you investigate and fix?

Accepted Answer

In short: Likely blocking: the finance job holds long locks (large UPDATEs, SERIALIZABLE, or lock escalation) while the screen s SELECTs wait. Investigate with sp_WhoIsActive / sys.dm_exec_requests (blocking_session_id, wait types like LCK_M_S) during the window and the job s plans. Fixes: enable READ COMMITTED SNAPSHOT so readers don t block on writers, batch the job s updates into small transactions, add indexes so it locks fewer rows, schedule heavy parts off-hours, or read reports from a readable secondary/replica.

Question 22

What is the difference between a clustered and a nonclustered index?

Accepted Answer

In short: A clustered index defines the physical order of the table: its B+ tree leaf level contains the actual data rows, so there can be only one. A nonclustered index is a separate B+ tree whose leaf rows contain the index key plus a pointer (the clustering key or RID) to the data row; there can be many. Queries needing columns not in a nonclustered index do key lookups into the clustered index.

Question 23

What is a page and an extent in SQL Server?

Accepted Answer

In short: A page is the basic 8 KB unit of storage and I/O, holding rows (≈8,060 bytes max in-row data) with a header and a row offset array. An extent is 8 contiguous pages (64 KB); SQL Server allocates space in extents. Special pages (GAM, SGAM, PFS, IAM) track which extents and pages are free or belong to which object.

Question 24

What are the transaction isolation levels in SQL Server?

Accepted Answer

In short: READ UNCOMMITTED (dirty reads possible), READ COMMITTED (default; no dirty reads), REPEATABLE READ (rows you read can t change), SERIALIZABLE (no phantoms — range locks) plus row-versioning options READ COMMITTED SNAPSHOT (statement-level consistent versions, readers don t block writers) and SNAPSHOT (transaction-level consistent snapshot with update-conflict detection).

Question 25

What is an execution plan and how do you read it?

Accepted Answer

In short: The step-by-step strategy the optimizer chose to run a query (operators like seeks, scans, joins, sorts). Read it right to left following the data flow, check the most expensive operators, compare estimated vs actual row counts, and look for scans on large tables, key lookups repeated many times, sorts/hash spills, implicit conversion and missing-index warnings. Use SET STATISTICS IO to measure logical reads before and after a change.

Question 26

How does SQL Server process a query internally?

Accepted Answer

Connection and TDS packets → parser (syntax) → algebrizer (binding names and types) → plan cache lookup → cost-based optimizer using statistics to choose a plan (cached) → executor runs the operators → storage engine reads pages via the buffer pool (from disk if not cached), taking locks or reading row versions per isolation level → rows streamed back to the client.

Question 27

What are statistics and why do they matter?

Accepted Answer

In short: Objects with a histogram (up to 200 steps) and density information about column value distributions, used by the optimizer to estimate how many rows each operation returns. Good estimates → good plans (right join type, memory grant, index choice). Stale or missing statistics lead to bad plans; they re auto-created/updated, and can be updated manually (`UPDATE STATISTICS ... WITH FULLSCAN`) after large data changes.

Question 28

What is parameter sniffing? How do you fix it?

Accepted Answer

In short: SQL Server compiles a parameterised query or procedure using the parameter values of the first execution and reuses that plan for all later values. If data is skewed, the plan can be terrible for other values. Fixes: a covering index that suits all values, OPTION (RECOMPILE) , OPTIMIZE FOR , splitting logic, forcing a good plan with Query Store, or SQL Server 2022 s Parameter Sensitive Plan optimisation.

Question 29

What is write-ahead logging and how does SQL Server recover after a crash?

Accepted Answer

In short: Every modification is written to the transaction log (with an LSN) before the data page changes reach disk, and a commit waits until its log records are hardened. After a crash, recovery runs analysis (find dirty pages and open transactions), redo (replay logged changes since the last checkpoint) and undo (roll back uncommitted transactions), returning the database to a consistent state.

Question 30

What causes blocking and how do you troubleshoot it?

Accepted Answer

In short: A session holding locks (usually a long or open transaction, or one scanning many rows because of missing indexes) blocks others needing incompatible locks. Find it with sys.dm_exec_requests ( blocking_session_id ), sp_WhoIsActive , and the head blocker s query/plan; fix by shortening transactions, adding indexes, committing promptly from the app, and enabling RCSI so readers don t block writers.

Question 31

Why can NOLOCK be dangerous?

Accepted Answer

In short: READ UNCOMMITTED doesn t take shared locks, so a query can read uncommitted data that later rolls back, read the same row twice or miss rows when pages split during an allocation-order scan, and still be blocked by schema locks. It s fast but wrong . Use RCSI or SNAPSHOT for non-blocking consistent reads instead.

Question 32

A query was fast yesterday and is slow today with no code change. How do you investigate?

Accepted Answer

In short: Check Query Store for a plan change (regressed query) — often due to a statistics update, parameter sniffing with a different first value, or an index change. Compare old and new plans (estimated vs actual rows, seek vs scan, join types). Quick fix: force the good plan in Query Store; real fix: update statistics, add/adjust a covering index, or rewrite non-SARGable predicates. Also rule out blocking ( LCK_M_* waits) and resource pressure (waits, memory, I/O).

Question 33

Users get "Transaction was deadlocked … chosen as the deadlock victim". What do you do?

Accepted Answer

In short: Capture the deadlock graph from the system_health Extended Events session, identify the statements, objects and lock types. Typical fixes: access tables in a consistent order, keep transactions short, add indexes so fewer rows are locked (especially on foreign keys and WHERE columns), use UPDLOCK for read-then-write, enable RCSI to remove reader/writer conflicts — and add retry logic for error 1205 in the application.

Question 34

Azure SQL Database vs Managed Instance vs SQL Server on a VM?

Accepted Answer

In short: Azure SQL Database is a fully managed single database (or elastic pool) — best for new cloud apps, with database-scoped features. Managed Instance is a fully managed SQL Server instance in your VNet with near-complete compatibility (SQL Agent, cross-database queries, CLR, linked servers) — best for lift-and-shift migrations. SQL Server on an Azure VM is IaaS with full control and 100 % compatibility but you manage patching, backups and HA.

Question 35

What is the difference between DTU and vCore models?

Accepted Answer

In short: DTU bundles CPU, memory and I/O into one measure with fixed tiers (Basic, Standard, Premium) — simple but less flexible. vCore lets you choose cores, memory and storage independently, pick hardware generations and tiers (General Purpose, Business Critical, Hyperscale), use serverless compute, and save with Azure Hybrid Benefit and reserved capacity. Most new deployments use vCore.

Question 36

How do you connect an App Service to Azure SQL without storing a password?

Accepted Answer

In short: Enable a managed identity on the App Service, set an Entra admin on the SQL server, create a contained database user FROM EXTERNAL PROVIDER for the identity and grant roles, and use a connection string with Authentication=Active Directory Default (or Managed Identity). Locally the same code uses your developer login through DefaultAzureCredential.

Question 37

What are failover groups and how are they different from active geo-replication?

Accepted Answer

In short: Active geo-replication replicates a single database to up to four readable secondaries in other regions, and failover is per database with connection strings pointing to specific servers. Failover groups manage a group of databases (or a whole managed instance) together and provide stable listener endpoints for read-write and read-only traffic that follow the primary after failover, so applications don t need connection-string changes. Failover can be automatic or manual.

Question 38

How does backup and restore work in Azure SQL Database?

Accepted Answer

In short: Backups are automatic: weekly full, differential every 12–24 hours and log backups every 5–10 minutes, stored in geo-redundant (or zone/locally redundant) storage. You can restore to any point in time within the retention period (1–35 days) as a new database, keep long-term retention backups up to 10 years, and geo-restore to another region if the primary region is unavailable.

Question 39

What is a partition key in Cosmos DB and how do you choose it?

Accepted Answer

In short: The property used to distribute items across logical and physical partitions; all items with the same value form a logical partition (max 20 GB). Choose a key with high cardinality and evenly spread reads/writes that is present in most queries (e.g. customerId, tenantId, deviceId) — so queries hit one partition and throughput isn t limited by a hot partition. It can t be changed later without migrating data.

Question 40

What are Request Units (RU/s) and consistency levels in Cosmos DB?

Accepted Answer

In short: RUs are the normalised cost of operations (CPU, IO, memory): a 1 KB point read ≈ 1 RU, writes and queries cost more; you provision RU/s (manual/autoscale) or pay per RU in serverless. Consistency levels trade latency/availability for freshness: Strong, Bounded staleness, Session (default, read-your-writes), Consistent prefix and Eventual.

Question 41

You need to migrate a 500 GB on-prem SQL Server database with SQL Agent jobs and cross-database queries to Azure with minimal downtime. What do you choose?

Accepted Answer

In short: Azure SQL Managed Instance, because it supports SQL Agent and cross-database queries. Assess with the migration tools, provision MI in a VNet connected by VPN/ExpressRoute, use the online migration (Database Migration Service or the MI link/log replay) to keep the target in sync, test the application against MI, then cut over during a short window by stopping writes, completing the sync and switching connection strings.

Question 42

An Azure SQL database is at 100 % CPU after a release. What do you do?

Accepted Answer

In short: Check Query Store/Query Performance Insight for the top CPU queries and regressed plans; force the last good plan (or rely on automatic tuning) as an immediate fix; fix the query/indexes (missing index, non-SARGable predicate, parameter sniffing); temporarily scale up the tier if users are impacted; add caching or read replicas for heavy reads; set alerts on CPU and log IO for early warning.

Question 43

What is ETL? Explain each step.

Accepted Answer

In short: ETL is the process of moving data from source systems into a target (usually a data warehouse). Extract : read data from databases, files, APIs — full or incremental. Transform : cleanse, validate, standardise, deduplicate, look up surrogate keys, apply business rules, aggregate. Load : write into dimension and fact tables (inserts, updates, SCD handling), then log and reconcile. Goal: consistent, integrated, historical data for reporting.

Question 44

What is the difference between ETL and ELT?

Accepted Answer

In short: ETL transforms data in a separate engine before loading (SSIS, Informatica), good when the target is limited or data must be cleaned before landing. ELT loads raw data into a scalable target first and transforms it there with SQL/Spark (Synapse, Fabric, Snowflake, Databricks + dbt), keeping raw history and using elastic compute. Modern cloud architectures mostly use ELT with a medallion design.

Question 45

What is a data warehouse? How is it different from an OLTP database?

Accepted Answer

In short: A data warehouse is a subject-oriented, integrated, time-variant, non-volatile store of data for analysis (Inmon s definition). OLTP databases serve day-to-day transactions: many small reads/writes, normalised, current data. Warehouses serve analytical queries over large volumes: denormalised star schemas, history, bulk loads, read-optimised (columnstore). Running analytics on OLTP harms the business application.

Question 46

What are fact and dimension tables?

Accepted Answer

In short: Fact tables store measurable business events (quantity, amount) at a defined grain plus foreign keys to dimensions. Dimension tables store descriptive attributes (customer name, city; product category; date parts) used to filter and group facts. Example: FactSales(DateKey, ProductKey, CustomerKey, Qty, Amount) with DimDate, DimProduct, DimCustomer.

Question 47

What is the difference between a star schema and a snowflake schema?

Accepted Answer

In short: In a star schema, each dimension is a single denormalised table directly joined to the fact — fewer joins, faster and simpler for BI tools. In a snowflake schema, dimensions are normalised into multiple related tables (Product → Subcategory → Category) — less redundancy but more joins and complexity. Kimball recommends star schemas; Power BI performs best with them.

Question 48

Explain Slowly Changing Dimensions Types 1, 2 and 3.

Accepted Answer

In short: They control how dimension attribute changes are stored. Type 1 overwrites the old value (no history — corrections). Type 2 inserts a new row with a new surrogate key and validity columns (ValidFrom, ValidTo, IsCurrent), preserving full history so old facts keep pointing to the old version. Type 3 adds a column for the previous value (only one level of history). Type 0 never changes; Type 6 combines 1, 2 and 3.

Question 49

What is a surrogate key and why use it in a warehouse?

Accepted Answer

In short: A system-generated integer key (IDENTITY/sequence) used as the dimension s primary key instead of the source s natural key. Reasons: integrates multiple sources with overlapping IDs, enables SCD Type 2 (several rows per business key), protects from source key changes/reuse, smaller and faster joins, supports special members like Unknown (-1).

Question 50

How do you implement an incremental load?

Accepted Answer

In short: Detect changes since the last successful run using a watermark (ModifiedDate/rowversion stored in a control table), CDC or Change Tracking, or hash comparison. Extract only those rows into staging, then upsert into the target (MERGE or UPDATE + INSERT; SCD logic for dimensions), handle deletes (soft-delete flags, CDC delete records, or periodic full compare), and update the watermark only after the load succeeds so failures don t skip data.

Question 51

How do you handle errors and bad data in ETL?

Accepted Answer

In short: Validate early; redirect bad rows to error tables with the reason instead of failing the whole batch (unless the threshold is exceeded); use an Unknown dimension member for missing lookups (late-arriving dimensions) and fix later; log every run in an audit table (counts, durations, status); send alerts; make loads idempotent and restartable; reconcile counts and totals with the source; keep raw data for reprocessing.

Question 52

The nightly ETL that used to take 1 hour now takes 5 hours and misses the SLA. How do you troubleshoot?

Accepted Answer

In short: Find which step slowed (audit log durations per task). Check data volume growth (switch full loads to incremental), source query plans and missing indexes, blocking on target tables during loads, row-by-row operations (OLE DB Command, cursors, SCD wizard on big dimensions → replace with set-based MERGE), blocking SSIS transformations (sort/aggregate), lookups in full-cache mode with huge reference tables, index maintenance on targets (disable/rebuild), log growth, server resources (memory, tempdb), and parallelism between independent loads.

Question 53

Business reports show sales totals 3% higher than the ERP. How do you find the cause?

Accepted Answer

In short: Reconcile step by step: compare counts and sums source → staging → warehouse for the same period and filters. Typical causes: duplicate extraction (overlapping incremental windows, reruns without idempotency), join fan-out (fact joined to a dimension with duplicate business keys, SCD2 lookup not restricted to the valid row), different business rules (cancelled orders, returns, tax/currency, time zones/cut-off), late-arriving corrections not applied. Fix the logic, add automated reconciliation checks and uniqueness tests.

Question 54

What is SSIS and what is it used for?

Accepted Answer

In short: SQL Server Integration Services is Microsoft s ETL and workflow tool shipped with SQL Server. It extracts data from many sources (SQL, Oracle, flat files, Excel, XML, APIs), transforms it in a high-performance in-memory pipeline, and loads it into targets such as data warehouses. Also used for data migration, file automation and database maintenance. Packages are built in Visual Studio and deployed to the SSISDB catalog.

Question 55

What is the difference between Control Flow and Data Flow?

Accepted Answer

In short: Control Flow orchestrates the workflow: tasks (Execute SQL, Data Flow, File System, Script, Send Mail), containers and precedence constraints determining execution order. Data Flow is inside a Data Flow Task and moves rows from sources through transformations to destinations in memory buffers. Control Flow deals with tasks; Data Flow deals with rows.

Question 56

What are the different types of containers?

Accepted Answer

Sequence Container (groups tasks, scopes variables, shares transactions, lets you enable/disable a group), For Loop Container (repeats while a condition holds — counter-based), Foreach Loop Container (iterates over a collection: files in a folder, rows of an ADO recordset, items, nodes, blobs), and the Task Host container that wraps every single task.

Question 57

What is a precedence constraint?

Accepted Answer

In short: A connector between control-flow executables that controls whether the next task runs, based on the outcome of the previous one: Success, Failure or Completion, optionally combined with an expression (e.g. only if @RowCount 0 ). With multiple constraints into one task, choose logical AND (all) or OR (any).

Question 58

What is the difference between variables and parameters?

Accepted Answer

In short: Parameters (introduced in 2012) pass values into a package or project from outside before execution — server names, folders, dates — and are set from SSISDB environments or dtexec . They are read-only during execution. Variables store values during execution (row counts, file names, result sets), can be changed by tasks/scripts, and are scoped to package, container or task.

Question 59

What are synchronous and asynchronous transformations? Give examples of blocking transformations.

Accepted Answer

In short: Synchronous (non-blocking) transformations process rows in the same buffer and output exactly one row per input row — Derived Column, Data Conversion, Conditional Split, Lookup, Multicast, Row Count. Asynchronous transformations create new buffers. Semi-blocking ones (Merge, Merge Join, Union All, Pivot) can output before reading all input; fully blocking ones (Sort, Aggregate, Fuzzy Grouping/Lookup) must read all input first, consuming memory and slowing large flows.

Question 60

Explain the Lookup transformation cache modes.

Accepted Answer

In short: Full cache loads the whole reference query into memory before processing (fastest, needs memory, reference must be stable). Partial cache starts empty and queries the database on misses, caching the results (good for large references with few used keys). No cache queries the database for every row (slowest; use when the reference changes during the load). A Cache Connection Manager can pre-build a cache from any source and reuse it across lookups/packages.

SQL Server / BI / ETL Developer — Interview Questions