Question 1

In Microsoft SQL Server transaction processing, which isolation level places and then holds shared locks on every row it reads so that the same rows cannot be modified by other transactions until the current transaction completes? Select the correct…

Accepted Answer

Introduction / Context: SQL Server supports several transaction isolation levels that balance concurrency against consistency. The question asks which level holds locks on all rows that are read so the same rows cannot be changed by others during the transaction, thereby preventing non-repeatable reads. Given Data / Assumptions: Platform: Microsoft SQL Server. Isolation levels considered: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Goal: Identify the level that keeps shared locks on read rows until transaction end. Concept / Approach: At REPEATABLE READ, SQL Server acquires shared (S) locks on rows it reads and holds them until the transaction completes. This prevents other transactions from updating those rows, eliminating non-repeatable reads. However, it does not inherently protect against phantoms; that requires range locks (key-range locking) at SERIALIZABLE. Step-by-Step Solution: 1) READ UNCOMMITTED allows dirty reads and does not hold S locks → not correct.2) READ COMMITTED releases S locks after each statement → repeatable reads not guaranteed.3) REPEATABLE READ holds S locks on all rows read until commit → prevents non-repeatable reads.4) SERIALIZABLE adds range locks to also prevent phantoms, but the prompt focuses on holding locks on rows that are read, which REPEATABLE READ already guarantees. Verification / Alternative check: Check documentation: REPEATABLE READ guarantees that if a row is read once, it cannot be modified by others until the transaction ends. New rows that satisfy the same predicate can still appear (phantoms), which is why SERIALIZABLE exists. Why Other Options Are Wrong: READ UNCOMMITTED: no S locks; allows dirty/non-repeatable reads. READ COMMITTED: releases S locks after statement; repeatability not guaranteed. SERIALIZABLE: stricter than necessary for the condition described (adds range locks for phantom prevention). Common Pitfalls: Confusing prevention of non-repeatable reads (REPEATABLE READ) with phantom prevention (SERIALIZABLE). Also assuming READ COMMITTED holds locks until commit—it does not. Final Answer: REPEATABLE READ

Question 2

In SQL Server 2000 backup strategy, which option creates a copy of all changes made since the last full (complete) backup? Choose the most accurate backup type used for faster restores between full backups.

Accepted Answer

Introduction / Context: Differential, full, and transaction log backups are core parts of SQL Server's backup chain. The question targets which method copies changes since the last full backup to speed up recovery without taking another full backup. Given Data / Assumptions: Version context: SQL Server 2000 (concept still applies today). Last complete (full) backup exists. We need the backup type that captures only extents changed since that full backup. Concept / Approach: A differential backup records all extents that have changed since the most recent full backup, using the differential change map. It grows as more changes occur after the full backup. Restores require the last full backup plus the most recent differential. Step-by-Step Solution: 1) Full backup copies the entire database at a point in time.2) Differential backup captures only the changes since that full backup.3) Transaction log backup captures log records since the last log backup, not since the last full; it is different in scope.4) Therefore, the correct choice is Differential backup. Verification / Alternative check: Restore sequence example: Restore last full backup, then restore the latest differential to bring the database near current without replaying many logs. Why Other Options Are Wrong: Complete backup: not "since full"; it is the full itself. Transaction Log: captures since last log backup, not since last full. None of the above: incorrect because differential is valid. Common Pitfalls: Confusing log backups (since last log backup) with differentials (since last full). Assuming differentials are cumulative per differential—they are cumulative since full, not incremental between differentials. Final Answer: Differential backup

Question 3

In Microsoft SQL Server, what are the valid ways to create and modify tables in a database? Consider both T-SQL statements and graphical administrative tools.

Accepted Answer

Introduction / Context: Database schema changes in SQL Server can be performed via T-SQL scripts or through graphical tools. Knowing both methods helps teams standardize deployments and support ad-hoc admin tasks. Given Data / Assumptions: Platform: SQL Server (e.g., 2000 era Enterprise Manager / modern SSMS). Task: Create or modify tables. Options: T-SQL and GUI. Concept / Approach: CREATE TABLE and ALTER TABLE are the canonical ways to define and evolve schema in source-controlled scripts. The GUI (Enterprise Manager / SSMS) issues the same T-SQL under the hood and can be convenient for quick changes and learning. Step-by-Step Solution: 1) Confirm that T-SQL statements (CREATE/ALTER) directly create/modify objects.2) Confirm that the graphical tool provides designers to create/alter tables, which generate T-SQL commands.3) Therefore, both methods are valid ways to perform the operation. Verification / Alternative check: Open a table designer in the GUI and save; the tool shows a generated script. Likewise, running CREATE/ALTER in a query window has the same effect. Why Other Options Are Wrong: Option A: Too restrictive—ignores GUI capability. Option B: Too restrictive—ignores T-SQL. Option D: Contradicts fundamental SQL Server behavior. Common Pitfalls: Relying solely on GUI without keeping scripts for version control. Making breaking changes in production without reviewing the generated T-SQL. Final Answer: Both of the two methods above will work.

Question 4

In SQL Server indexing, which index type has a leaf (bottom) level that does not store the actual table data but instead stores pointers (row locators) to the data rows?

Accepted Answer

Introduction / Context: Understanding how clustered and nonclustered indexes store information at the leaf level is essential for choosing the right indexing strategy for performance and storage. Given Data / Assumptions: Leaf level behavior distinguishes index types. We are considering SQL Server's B-tree index structures. Terminology: leaf level = bottom level of the index. Concept / Approach: A clustered index's leaf level contains the data rows themselves (the table is ordered as the clustered index). A nonclustered index's leaf level contains key values plus row locators (RID or clustering key) that point to the underlying data. Step-by-Step Solution: 1) Define clustered index: data stored at leaf in index order.2) Define nonclustered index: separate structure; leaf stores pointers to rows.3) The question describes an index whose leaf does not contain data → nonclustered. Verification / Alternative check: Query execution plans often show a Key Lookup/RID Lookup when a nonclustered index is used and additional columns are fetched from the base table via the locator. Why Other Options Are Wrong: Clustered: leaf contains data, not just pointers. Primary/Secondary: terminology not specific to SQL Server leaf-level storage. Common Pitfalls: Assuming “primary” means clustered; in SQL Server a primary key may be implemented as clustered or nonclustered. Final Answer: Nonclustered

Question 5

In SQL Server indexing, which index type stores the actual data rows at the leaf (bottom) level and keeps those rows physically ordered the same as the index key?

Accepted Answer

Introduction / Context: Choosing between clustered and nonclustered indexes requires knowing where the data lives in the B-tree and how row order is maintained. Given Data / Assumptions: We compare clustered vs nonclustered leaf storage. We care about physical order of rows on disk/pages. SQL Server implementation is assumed. Concept / Approach: In a clustered index, the table data itself is the leaf level of the index, ordered by the clustering key. In a nonclustered index, the leaf contains only keys and locators; data resides elsewhere. Step-by-Step Solution: 1) If an index stores rows at the leaf → clustered.2) If leaf stores locators that point to rows → nonclustered.3) The prompt states rows are stored at leaf and ordered the same as the index → clustered. Verification / Alternative check: Creating a clustered index on a heap converts it into a clustered table; scanning shows order by clustering key. Why Other Options Are Wrong: Nonclustered: leaf has pointers, not rows. Primary/Secondary: naming does not define storage semantics. Common Pitfalls: Assuming a primary key must be clustered; SQL Server allows a nonclustered primary key. Final Answer: Clustered

Question 6

In SQL Server recovery planning, under which recovery model are all database changes fully logged, enabling point-in-time recovery using transaction log backups?

Accepted Answer

Introduction / Context: Recovery models control logging behavior, log truncation, and restore capabilities. This question asks which model fully logs all changes and supports point-in-time recovery. Given Data / Assumptions: Recovery models considered: FULL, SIMPLE, BULK_LOGGED. Requirement: all changes fully logged and restorable to a specific time. Log backups are taken regularly. Concept / Approach: FULL recovery model logs every change. With a sequence of full/differential plus transaction log backups, you can restore to a particular point in time by replaying log records up to a marked stop time. Step-by-Step Solution: 1) FULL: every operation fully logged; requires log backups to truncate.2) SIMPLE: logging still occurs but log is truncated automatically and no log backups → no point-in-time restore.3) BULK_LOGGED: minimal logging for certain bulk operations; point-in-time may be limited during those operations.4) Therefore, the model that always fully logs and supports point-in-time is FULL. Verification / Alternative check: Test a restore chain with FULL model: full backup → several log backups; restore to a specific time using STOPAT. Why Other Options Are Wrong: Differential recovery: not a recovery model. SIMPLE: no log backups, cannot do point-in-time. BULK_LOGGED: may prevent point-in-time during bulk operations. Common Pitfalls: Thinking “simple = no logging”; SQL Server always logs, but SIMPLE truncates log and disallows log backups. Mislabeling “differential” as a model (it is a backup type). Final Answer: Full recovery

Question 7

In the ANSI/SQL Server isolation hierarchy, which is the strictest isolation level that prevents dirty reads, non-repeatable reads, and phantom rows by using key-range locking?

Accepted Answer

Introduction / Context: Different isolation levels trade concurrency for consistency. The strictest level prevents not just dirty and non-repeatable reads but also phantoms. Given Data / Assumptions: Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Requirement: eliminate dirty reads, non-repeatable reads, and phantom inserts. Concept / Approach: SERIALIZABLE enforces serial transaction execution semantics by acquiring key-range locks, which block inserts or deletes in the qualified key ranges during the transaction, thereby preventing phantoms. Step-by-Step Solution: 1) READ UNCOMMITTED: allows dirty reads → not strictest.2) READ COMMITTED: no dirty reads but allows non-repeatable reads.3) REPEATABLE READ: prevents non-repeatable reads but allows phantoms.4) SERIALIZABLE: prevents dirty, non-repeatable, and phantom reads. Verification / Alternative check: Documentation lists SERIALIZABLE as the top level; query hints like HOLDLOCK (equivalent to SERIALIZABLE) show range locking behavior. Why Other Options Are Wrong: REPEATABLE READ lacks range locks. READ COMMITTED releases S locks per statement. READ UNCOMMITTED allows dirty reads. Common Pitfalls: Confusing REPEATABLE READ with phantom prevention. Assuming SERIALIZABLE always means poor performance; it depends on workload and index design. Final Answer: SERIALIZABLE.

Question 8

In SQL Server's trigger architecture, which trigger types are supported by the engine and available for tables and views as appropriate?

Accepted Answer

Introduction / Context: Triggers in SQL Server fire in response to data modification events. Knowing which trigger types are supported helps in enforcing complex constraints and audit logic. Given Data / Assumptions: Platform: SQL Server. Events: INSERT, UPDATE, DELETE. Objects: tables and, for INSTEAD OF, also views. Concept / Approach: SQL Server supports AFTER triggers (fire after the DML statement succeeds) and INSTEAD OF triggers (replace the DML operation; useful on views). It does not support BEFORE triggers like some other RDBMS engines. Step-by-Step Solution: 1) Verify AFTER triggers exist and are common for auditing/validation.2) Verify INSTEAD OF triggers exist and are often used on views to map writes to base tables.3) Confirm no BEFORE trigger type in SQL Server.4) Conclude that supported types are INSTEAD OF and AFTER. Verification / Alternative check: Attempt to create a BEFORE trigger yields an error; documentation shows only AFTER and INSTEAD OF. Why Other Options Are Wrong: INSTEAD OF only / AFTER only: incomplete. BEFORE only: unsupported. Common Pitfalls: Porting code from engines that support BEFORE triggers without redesign. Overusing triggers where constraints or procedures would be clearer. Final Answer: INSTEAD OF and AFTER only

Question 9

In SQL Server recovery models, which model is commonly described as having “no logging,” even though SQL Server always writes to the transaction log internally? Select the closest correct choice based on standard terminology.

Accepted Answer

Introduction / Context: Many study guides casually state that one recovery model does “no logging,” which is misleading. This question checks both conceptual understanding and precise terminology. Given Data / Assumptions: Recovery models: FULL, BULK_LOGGED, SIMPLE. We seek the model popularly (but imprecisely) said to do “no logging.” Important nuance: SQL Server always uses the transaction log. Concept / Approach: In SIMPLE recovery, the log is automatically truncated after each checkpoint and you cannot take transaction log backups. Although logging still happens, you cannot perform point-in-time recovery because the log backup chain does not exist. Textbooks sometimes summarize this as “no logging,” which refers to the inability to back up and replay the log, not the absence of log records. Step-by-Step Solution: 1) FULL: fully logged; supports log backups and point-in-time restore.2) BULK_LOGGED: minimal logging for certain bulk operations; still allows log backups with caveats.3) SIMPLE: logging occurs but log is truncated and cannot be backed up.4) Therefore, the choice commonly (if imprecisely) called “no logging” is SIMPLE recovery. Verification / Alternative check: Attempting BACKUP LOG in SIMPLE returns an error. DMVs show log records still exist during activity. Why Other Options Are Wrong: Full: fully logged. Bulk-logged: minimal logging only for specific operations; still relies on the log. Differential: a backup type, not a model. Common Pitfalls: Believing SIMPLE truly disables the log; it does not. Assuming BULK_LOGGED means “no logging” for everything; only certain bulk operations are minimally logged. Final Answer: Simple recovery

Question 10

When you define constraints in SQL Server, on which key type does the engine automatically create a unique index by default (often clustered unless specified otherwise)?

Accepted Answer

Introduction / Context: Indexes often accompany constraints in SQL Server to enforce uniqueness and performance. Understanding which constraints auto-create indexes helps with design and troubleshooting. Given Data / Assumptions: A table has constraints such as PRIMARY KEY and FOREIGN KEY. We want to know which constraints automatically create indexes. Default behaviors may be overridden (e.g., nonclustered primary key). Concept / Approach: Creating a PRIMARY KEY constraint automatically creates a unique index on the key columns (clustered by default if the table does not already have a clustered index). FOREIGN KEY constraints do not automatically create an index, though adding one is typically recommended for referential integrity checks and join performance. Step-by-Step Solution: 1) Define PRIMARY KEY: must be unique and not null → enforced via an automatically created unique index.2) Define FOREIGN KEY: enforces referential integrity; no automatic index is created.3) Therefore, only PRIMARY KEY triggers automatic index creation. Verification / Alternative check: Script out a table with a primary key; SQL Server shows a corresponding unique index. Adding a foreign key does not show a new index unless explicitly created. Why Other Options Are Wrong: Foreign key only: false. Both primary and foreign keys: overstates behavior. Never automatically: contradicts primary key behavior. Common Pitfalls: Forgetting to index foreign keys, which can slow deletes/updates on the referenced table and joins. Assuming the primary key must be clustered—this is configurable. Final Answer: primary keys only.

Question 11

Microsoft SQL Server — Concurrency Control Factors Which of the following is NOT a determinant of how SQL Server performs concurrency control and locking during reads and writes?

Accepted Answer

Introduction: Concurrency control in Microsoft SQL Server governs how multiple sessions safely read and modify the same data at the same time. This question checks whether you can distinguish true engine-level factors that shape locking and blocking from settings that either apply to narrower features or influence different parts of the system. Given Data / Assumptions: SQL Server uses locks, latches, and row versioning depending on isolation level and options. Applications may use cursors with specific concurrency settings. Developers can add locking hints to override the optimizer’s default choices. Deadlock priority influences victim selection, not core lock acquisition rules. Concept / Approach: Identify which items directly configure or override the engine’s concurrency behavior (isolation level, hints, cursor concurrency) versus items that are either consequences of the optimizer or unrelated policy levers. The phrase “lock level” is not a user-configurable concurrency policy; SQL Server chooses lock granularity automatically (with optional hints), so “lock level” itself is not the determining factor. Step-by-Step Solution: 1) Transaction isolation level (e.g., READ COMMITTED, SNAPSHOT) dictates read/write rules, phantom visibility, and whether row versioning is used.2) Locking hints like WITH (NOLOCK), HOLDLOCK, UPDLOCK can override default behavior on a statement, directly affecting concurrency.3) Cursor concurrency options (e.g., READ_ONLY, SCROLL_LOCKS, OPTIMISTIC) define how cursor reads interact with locks and versions.4) “Lock level” is not a configuration switch; lock granularity (row, page, table) is chosen by the engine’s lock manager and can be nudged by hints, but “lock level” itself is not a standalone determining factor.5) Deadlock priority affects who gets chosen as a victim after a deadlock is detected, not general lock acquisition policy. Verification / Alternative check: Inspect SQL Server documentation: concurrency behavior is specified under isolation levels, hints, and cursor options. No knob called “lock level” exists as a general policy control. Why Other Options Are Wrong: Transaction isolation level: Directly defines concurrency semantics. Cursor concurrency setting: Controls how cursors lock or version rows. Locking hints: Explicitly override default concurrency behavior per statement. Deadlock priority setting: Influences victim choice, not base concurrency policy. Common Pitfalls: Confusing engine-selected lock granularity with a user-facing “lock level” setting, and assuming deadlock priority changes isolation semantics. Final Answer: Lock level

Question 12

Microsoft SQL Server — Authentication Modes Which authentication mode is NOT offered by SQL Server for client connections?

Accepted Answer

Introduction: Authentication modes control how users and applications prove their identity to SQL Server. Knowing the supported modes is essential for secure deployments and compliance with organizational standards. Given Data / Assumptions: SQL Server integrates tightly with Windows security. SQL Server also supports its own logins when configured appropriately. The question asks which mode is not provided. Concept / Approach: SQL Server supports two modes: Windows Authentication mode and Mixed Mode. Windows Authentication mode accepts only Windows principals. Mixed Mode accepts both Windows logins and SQL Server logins. There is no configuration that allows only SQL Server logins while excluding Windows accounts entirely. Step-by-Step Solution: 1) Identify supported modes: Windows Authentication, Mixed Mode.2) Evaluate options against supported modes.3) Conclude that “SQL Server only security” is not a valid mode. Verification / Alternative check: During setup or via server properties, administrators can toggle between Windows Authentication and Mixed Mode only. Why Other Options Are Wrong: Windows-only security: This is Windows Authentication mode, fully supported. Both SQL Server and Windows-only security / Mixed security: These both describe Mixed Mode, which is supported. Kerberos-only without Windows accounts: Kerberos operates within Windows domain auth and is not a separate SQL-only mode. Common Pitfalls: Assuming there is a “SQL Server only” mode; SQL logins require Mixed Mode, which still allows Windows logins. Final Answer: SQL Server only security

Question 13

SQL Server 2000 Stored Procedures — Parameter Prefix In Transact-SQL, which symbol indicates a parameter within a stored procedure definition or call?

Accepted Answer

Introduction: T-SQL uses a distinct prefix to denote variables and parameters. Recognizing this prefix is fundamental to writing and reading stored procedures in SQL Server 2000 and later versions. Given Data / Assumptions: Question concerns SQL Server 2000 syntax (still the same convention today). Focus is on the symbol used for parameters. Concept / Approach: In Transact-SQL, both variables and parameters are identified with the at sign character. This provides syntactic clarity and separates parameter names from column names and keywords. Step-by-Step Solution: 1) Recall T-SQL convention: parameter names start with @.2) Compare with other symbols which have other meanings or none in this context (%, #, &, : ).3) Select the symbol that matches T-SQL parameter notation. Verification / Alternative check: Open any CREATE PROCEDURE example in Books Online; parameters are declared as @param datatype. Why Other Options Are Wrong: #: Used for temporary table names (e.g., #Temp). %: Wildcard in LIKE patterns, not parameter prefix. &: No parameter meaning in T-SQL. : Not used for parameters in T-SQL (PL/SQL uses : in binds). Common Pitfalls: Confusing temporary table prefix # with parameters, or mixing Oracle bind syntax with T-SQL. Final Answer: @

Question 14

SQL Server 2000 Stored Procedures — Storage and Invocation Which statement best describes how stored procedures are managed and used in SQL Server?

Accepted Answer

Introduction: Stored procedures encapsulate SQL and control logic at the database tier. Understanding where they live and how applications call them is foundational for architecture, performance, and security. Given Data / Assumptions: Target platform: SQL Server 2000 (behavior conceptually similar today). Focus: storage location and invocation model for stored procedures. Concept / Approach: In SQL Server, stored procedures are database objects. They are authored and stored in the database catalog and executed by clients via RPC-style calls or EXEC statements. They are compiled to execution plans which can be reused from cache. Step-by-Step Solution: 1) Evaluate statements that match SQL Server's object model.2) Recognize that procedures are not stored on client machines by requirement.3) Note that BEFORE/INSTEAD OF/AFTER are trigger types, not procedure types.4) Select the statement consistent with database-resident procedures callable by applications. Verification / Alternative check: Check system catalog views (e.g., sys.objects). Stored procedures appear as database objects and are invoked using EXEC or through application frameworks. Why Other Options Are Wrong: Never kept in database: False; they are stored in the database. Stored on users' computers: Not required or typical. Support BEFORE/INSTEAD OF/AFTER types: Those are trigger types, not procedure types. Only scripts, not compiled/cached: SQL Server caches execution plans for procedures. Common Pitfalls: Mixing up triggers with stored procedures and assuming code must live on clients. Final Answer: Stored procedures are kept inside the database and can be invoked by application programs.

Question 15

SQL Server Triggers — INSTEAD OF vs AFTER What is the SQL Server program unit that fires instead of executing the original DML statement, effectively replacing it?

Accepted Answer

Introduction: SQL Server supports two trigger timings for DML: INSTEAD OF and AFTER. Knowing their semantics is crucial when enforcing complex business rules or redirecting writes to views. Given Data / Assumptions: Target: SQL Server 2000 semantics (still valid). Original DML can be INSERT, UPDATE, or DELETE. Concept / Approach: An INSTEAD OF trigger intercepts a DML statement and executes in its place. The original action does not occur unless the trigger code issues it explicitly. By contrast, an AFTER trigger fires only after the DML succeeds and is used for auditing, cascading logic, or post-conditions. Step-by-Step Solution: 1) Identify trigger type that replaces the original statement.2) Recognize that SQL Server has no BEFORE trigger timing.3) Choose INSTEAD OF as the mechanism that runs in lieu of the DML statement. Verification / Alternative check: Create a view with an INSTEAD OF INSERT trigger; observe that inserts are handled by trigger logic and the base insert does not occur automatically. Why Other Options Are Wrong: BEFORE trigger: Not supported in SQL Server DML timing semantics. AFTER trigger: Runs after the statement completes; it does not replace it. BEGIN trigger / SCHEDULED trigger: Not valid SQL Server trigger types. Common Pitfalls: Expecting Oracle-style BEFORE triggers in SQL Server, or assuming AFTER can cancel the base operation. Final Answer: INSTEAD OF trigger.

Question 16

Transact-SQL (T-SQL) — Procedural Extensions in SQL Server 2000 Which language name refers to SQL Server's dialect that adds variables, parameters, IF, WHILE, and other procedural features to standard SQL?

Accepted Answer

Introduction: Vendors extend SQL with procedural features for flow control, variables, and robust error handling. Microsoft's dialect for SQL Server is widely known and appears throughout documentation and tooling. Given Data / Assumptions: Platform: SQL Server 2000 and later. Focus: name of Microsoft's SQL dialect. Concept / Approach: Transact-SQL (often written as T-SQL) is the procedural extension layer on top of ANSI SQL for SQL Server. It introduces DECLARE, SET, IF, WHILE, TRY...CATCH (later versions), and rich metadata access with system views. Step-by-Step Solution: 1) Associate vendor-specific dialects with products: T-SQL for SQL Server, PL/SQL for Oracle, PL/pgSQL for PostgreSQL.2) Identify the option that names Microsoft's dialect.3) Select TRANSACT SQL as the correct answer. Verification / Alternative check: Open Books Online or SSMS templates; syntax is labeled T-SQL and uses constructs like DECLARE @i int; IF @i > 0 … Why Other Options Are Wrong: COBOL / Visual Basic / VB.NET: General-purpose languages, not SQL Server's SQL dialect. PL/pgSQL: PostgreSQL's procedural language. Common Pitfalls: Confusing PL/SQL or PL/pgSQL with T-SQL due to similar naming. Final Answer: TRANSACT SQL

Question 17

SQL Server Triggers — AFTER Timing What is the SQL Server trigger type that executes only after the referenced INSERT, UPDATE, or DELETE has successfully completed?

Accepted Answer

Introduction: AFTER triggers are commonly used for auditing, denormalized updates, and enforcing post-conditions once a DML operation has succeeded. Differentiating them from INSTEAD OF triggers is vital for correct design. Given Data / Assumptions: SQL Server supports AFTER and INSTEAD OF DML triggers. BEFORE timing is not available in SQL Server. Concept / Approach: An AFTER trigger runs after the base DML completes. The inserted and deleted pseudo-tables contain the final row versions, enabling audit and business rule checks that rely on successful persistence. Step-by-Step Solution: 1) Determine whether the trigger runs before, after, or instead of DML.2) AFTER matches the requirement ”after the SQL command has been processed”.3) Select AFTER trigger. Verification / Alternative check: Create an AFTER UPDATE trigger and verify it fires only when the update succeeds and is not rolled back in compilation. Why Other Options Are Wrong: INSTEAD OF: Replaces the base DML, does not run after it. BEFORE: Not supported. BEGIN / QUEUE trigger: Not valid SQL Server trigger types. Common Pitfalls: Expecting a BEFORE trigger timing like Oracle or PostgreSQL; SQL Server provides INSTEAD OF for pre-logic. Final Answer: AFTER trigger.

Question 18

SQL Server Cursors — Concurrency Option with Update Locks Which cursor concurrency option places update locks on rows as they are read to prevent concurrent changes?

Accepted Answer

Introduction: SQL Server cursors can run under different concurrency models that trade off blocking, correctness, and the probability of update conflicts. Understanding these options helps you predict locking behavior when iterating through result sets. Given Data / Assumptions: Options include READ_ONLY, SCROLL_LOCK(S), OPTIMISTIC, and variations. Goal: find which option acquires update locks while reading. Concept / Approach: SCROLL_LOCKS cursors acquire update locks on fetched rows to prevent other sessions from updating them until the cursor operation is finished, thereby avoiding lost updates in pessimistic fashion. OPTIMISTIC variants do not lock at read time; they detect conflicts at update time using timestamps or checksums. READ_ONLY cannot update and therefore does not lock for updates. READCOMMITTED is an isolation level, not a cursor concurrency option. Step-by-Step Solution: 1) Map each option to its locking semantics.2) Identify the pessimistic model that locks rows on read.3) Choose SCROLL_LOCK (SCROLL_LOCKS) as the correct answer. Verification / Alternative check: Consult cursor declaration syntax: DECLARE cur CURSOR FOR … FOR UPDATE OF … WITH SCROLL_LOCKS acquires update locks during fetch. Why Other Options Are Wrong: READ_ONLY: No updates and no update locks. OPTIMISTIC: Uses versioning or values checks, not locks at read time. READCOMMITTED: Isolation level, not a cursor concurrency option. OPTIMISTIC with checksum: Still optimistic, checks at update time. Common Pitfalls: Confusing isolation levels with cursor concurrency options, and overlooking that optimistic models defer conflict detection. Final Answer: SCROLL_LOCK

Question 19

SQL Server Recovery Models — Logging Behavior Under which recovery model are all changes logged except those operations that would create very large log entries (e.g., bulk operations)?

Accepted Answer

Introduction: Recovery models determine how transactions are logged and what restore strategies are available. Choosing the proper model balances performance, log growth, and recoverability requirements. Given Data / Assumptions: Bulk operations can generate massive log records. The question asks which model minimally logs those bulk operations while still logging everything else. Concept / Approach: Bulk-logged recovery provides minimal logging for qualifying bulk operations (e.g., BULK INSERT, SELECT INTO, certain index operations) while fully logging other transactions. This reduces log size during heavy loads at the cost of losing point-in-time recovery if a bulk operation occurred since the last log backup. Step-by-Step Solution: 1) Compare recovery models: FULL logs everything; SIMPLE truncates log on checkpoint; BULK-LOGGED minimally logs bulk operations.2) Identify the one that exempts only very large operations from full logging.3) Select bulk-logged recovery. Verification / Alternative check: Review restore capabilities: after bulk operations in bulk-logged model, you cannot do point-in-time restore across those operations. Why Other Options Are Wrong: Differential recovery: Not a recovery model; differential refers to a backup type. Full recovery: Everything is fully logged. Simple recovery: Truncates log regularly; not “all changes except big ones.” Point-in-time only recovery: Not a defined model. Common Pitfalls: Confusing backup types (full, differential) with recovery models, and assuming simple equals minimal logging for bulk operations. Final Answer: Bulk-logged recovery

Question 20

SQL Server Cursors — Optimistic Concurrency Which cursor concurrency model acquires no locks when reading and waits until update time to check for conflicts?

Accepted Answer

Introduction: Optimistic concurrency aims to maximize concurrency by avoiding locks during reads and validating at write time that no conflicting change occurred. This model is common in high-read, lower-conflict workloads. Given Data / Assumptions: We are choosing among cursor concurrency options. Goal: identify the model that avoids locking until the user updates data. Concept / Approach: In SQL Server cursors, OPTIMISTIC uses either a rowversion value or a checksum snapshot of columns to detect if a row changed since it was fetched. If it changed, the update is rejected at update time; otherwise, it proceeds. No update locks are taken during fetch, so readers do not block writers during enumeration. Step-by-Step Solution: 1) Map options to semantics: OPTIMISTIC defers conflict detection, SCROLL_LOCKS locks early, READ_ONLY cannot modify, PESSIMISTIC is not a T-SQL cursor keyword.2) Select OPTIMISTIC as the model with no read-time locks.3) Note that OPTIMISTIC with checksum is a stricter variant but still optimistic. Verification / Alternative check: Check DECLARE CURSOR WITH OPTIMISTIC or OPTIMISTIC TYPE—behavior is to validate at UPDATE/DELETE time, not FETCH time. Why Other Options Are Wrong: READ_ONLY: No updates allowed at all. SCROLL_LOCK: Takes update locks on fetch. PESSIMISTIC: Not a SQL Server cursor keyword; describes locking philosophy only. READ WRITE with HOLDLOCK: A statement hint pattern, not a cursor concurrency option. Common Pitfalls: Believing READ COMMITTED isolation equals optimistic cursor behavior; they are distinct concerns. Final Answer: OPTIMISTIC

SQL Server 2000 Questions