Question 1

In Microsoft SQL Server, when you create a new database without custom file settings, does the engine by default create one primary data file (.mdf) and one transaction log file (.ldf)?

Accepted Answer

Introduction / Context: Understanding how Microsoft SQL Server lays out files for a newly created database is essential for capacity planning, backup/restore strategies, and performance tuning. By default, SQL Server bases new databases on the model database and creates a single primary data file (.mdf) and a single transaction log file (.ldf) unless you explicitly specify additional files or a different layout. Given Data / Assumptions: No custom file specifications supplied in the CREATE DATABASE statement. Default behavior inherited from the model database. Standard on-premises SQL Server configuration. Concept / Approach: SQL Server databases have at least one data file in the PRIMARY filegroup (commonly .mdf) and at least one log file (.ldf). Additional secondary data files (.ndf) or extra log files are optional and must be explicitly defined. The default simplifies initial setup while allowing later growth or reconfiguration. Step-by-Step Solution: Create a database with: CREATE DATABASE MyDB;SQL Server allocates PRIMARY data file and log file paths from server defaults or model template.Confirm with: SELECT name, type_desc FROM sys.database_files WHERE database_id = DB_ID(\'MyDB\');You will see one ROWS (data) file and one LOG (transaction log) file. Verification / Alternative check: Script out a default-created database in SSMS; the file definitions show one .mdf and one .ldf. Adding files requires explicit syntax (e.g., FILEGROUP definitions or ADD FILE). Why Other Options Are Wrong: “Incorrect” conflicts with default behavior. Tying it to Express edition, AUTO_CLOSE, or model size misunderstands the default creation mechanism; those settings affect other behaviors, not the basic count of files created by default. Common Pitfalls: Assuming multiple log files improve performance (they usually do not). Confusing secondary .ndf files (optional) with mandatory .mdf/.ldf defaults. Final Answer: Correct

Question 2

In Transact-SQL (T-SQL) for Microsoft SQL Server, do parameters and variables begin with the percent sign (%) rather than the at sign (@)?

Accepted Answer

Introduction / Context: T-SQL has consistent identifier conventions for variables and parameters. Recognizing these is fundamental to writing correct stored procedures, functions, and scripts in SQL Server. Given Data / Assumptions: We are using Microsoft SQL Server’s T-SQL dialect. We consider local variables, procedure/function parameters, and system functions. No vendor-specific non-SQL Server syntax is mixed in. Concept / Approach: In T-SQL, both local variables and parameters are prefixed with the at sign (@). The percent sign (%) is used in pattern matching with LIKE and modulo arithmetic, not for variable/parameter names. System global variables do not exist; instead, SQL Server exposes system functions and metadata (e.g., @@VERSION is a function-like global). Step-by-Step Solution: Declare a local variable: DECLARE @i int = 5;Use a parameter in a stored procedure: CREATE PROC dbo.p @id int AS SELECT @id;Observe that both use @, not %.Use % for LIKE patterns (e.g., WHERE col LIKE @p + \'%\') or modulo (e.g., 7 % 3). Verification / Alternative check: Attempting to declare %i int will fail with a syntax error. Documentation and SSMS IntelliSense both show @ for variables and parameters. Why Other Options Are Wrong: Settings like ANSI_NULLS do not change identifier syntax. “Variables use @@” is incorrect; @@ introduces built-in global functions, not user variables. Splitting conventions between variables and parameters is false; both use @. Common Pitfalls: Confusing % in LIKE patterns with variable prefixes; conflating @@VERSION (a system function) with user variables. Final Answer: Incorrect

Question 3

In Microsoft SQL Server, can AFTER triggers be defined on views, or are AFTER triggers limited to tables while views support INSTEAD OF triggers?

Accepted Answer

Introduction / Context: SQL Server provides two main trigger types: AFTER and INSTEAD OF. These are used for enforcing business rules, auditing, or transforming data writes. Knowing which objects support which trigger types prevents compilation errors and inappropriate designs. Given Data / Assumptions: Object types considered: base tables and views. Trigger types considered: AFTER and INSTEAD OF. Standard SQL Server editions and default settings. Concept / Approach: AFTER triggers fire after the data modification statement (INSERT, UPDATE, DELETE) completes on a table. Views cannot host AFTER triggers. Instead, SQL Server allows INSTEAD OF triggers on both tables and views, with the common use case being to make inherently non-updatable views behave updatable by redirecting modifications to underlying tables. Step-by-Step Solution: Create a view: CREATE VIEW v AS SELECT ... FROM dbo.T;Attempt CREATE TRIGGER trg ON v AFTER INSERT ... → this fails.Create INSTEAD OF trigger: CREATE TRIGGER trg ON v INSTEAD OF INSERT AS INSERT INTO dbo.T ...;On a table, AFTER triggers are valid: CREATE TRIGGER trg ON dbo.T AFTER UPDATE AS ...; Verification / Alternative check: Refer to sys.triggers and sys.views; you will find INSTEAD OF triggers associated with views, while AFTER triggers associate with tables. Why Other Options Are Wrong: Claiming AFTER triggers work on views contradicts engine rules. BEFORE triggers do not exist in SQL Server. AFTER triggers are not limited to DELETE; they support INSERT and UPDATE as well. Common Pitfalls: Trying to enforce view logic using AFTER triggers; assuming cross-platform parity with systems that support BEFORE triggers. Final Answer: AFTER triggers apply only to tables; views allow INSTEAD OF triggers

Question 4

Within SQL Server’s transaction isolation options, is SERIALIZABLE the strictest standard isolation level in terms of locking and preventing phenomena?

Accepted Answer

Introduction / Context: Transaction isolation controls visibility and concurrency behavior. SQL Server supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. Choosing a level affects blocking, deadlocks, and data correctness. Given Data / Assumptions: We compare documented isolation levels. “Strictest” refers to locking/phenomena prevention in ANSI terms. Snapshot uses row versioning, not stricter locking. Concept / Approach: SERIALIZABLE prevents dirty reads, nonrepeatable reads, and phantom reads by effectively serializing concurrent transactions in conflicting ranges. It uses range locks to stop phantoms. SNAPSHOT avoids many conflicts via versioning, but it does not equate to stricter locking semantics than SERIALIZABLE. Step-by-Step Solution: Set isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Run concurrent range queries and inserts; observes blocking due to range locks.Contrast with SNAPSHOT: SET ALLOW_SNAPSHOT_ISOLATION ON; SNAPSHOT readers do not block writers but allow more concurrency via versions. Verification / Alternative check: Review phenomena allowed: only SERIALIZABLE disallows dirty, nonrepeatable, and phantom reads under locking semantics. Why Other Options Are Wrong: “Snapshot is stricter” is incorrect; it is different, not stricter, and does not use range locks. Repeatable Read does not prevent phantoms. Common Pitfalls: Assuming SNAPSHOT equals SERIALIZABLE; ignoring potential blocking overhead at SERIALIZABLE. Final Answer: Correct

Question 5

In SQL Server, does the system automatically create an index for every primary key and for every foreign key, or only guarantee automatic indexing for primary keys?

Accepted Answer

Introduction / Context: Keys and indexes are related but distinct. Primary keys enforce uniqueness and are automatically backed by a unique index. Foreign keys enforce referential integrity, but SQL Server does not automatically create an index for them. Given Data / Assumptions: Primary key defined via PRIMARY KEY constraint. Foreign key defined via FOREIGN KEY constraint. No explicit CREATE INDEX statements provided. Concept / Approach: When you add a PRIMARY KEY constraint, SQL Server creates a unique index (clustered by default if not otherwise specified). For FOREIGN KEY constraints, indexing is recommended for join and delete/update performance, but it is not automatic; you must create a supporting index explicitly. Step-by-Step Solution: Create table with PK: PK yields unique index (viewable in sys.indexes).Add FK referencing parent table; no index appears automatically on the FK column(s).Manually add: CREATE INDEX IX_Child_FK ON dbo.Child(ParentID); Verification / Alternative check: Query sys.indexes after adding PK and FK to observe index presence/absence. Execution plans for joins show benefits of indexing foreign keys. Why Other Options Are Wrong: Automatic indexes on FKs do not exist. Recovery model has nothing to do with automatic key indexing. Saying none are auto-indexed ignores PK behavior. Common Pitfalls: Assuming FKs are indexed; suffering slow deletes/updates due to missing FK indexes. Final Answer: Automatic index for primary keys; foreign keys are not auto-indexed

Question 6

Does SQL Server support exactly three recovery models named Simple, Full, and Bulk, or is the correct name “Bulk-logged” rather than “Bulk”?

Accepted Answer

Introduction / Context: Recovery models define how SQL Server logs transactions and what restore strategies are possible. Precise terminology matters because documentation, scripting, and compliance requirements reference the official names. Given Data / Assumptions: We are discussing database-scoped recovery models. SQL Server supports three models. We care about their official names. Concept / Approach: The three supported recovery models are: SIMPLE, FULL, and BULK_LOGGED. BULK_LOGGED is often abbreviated verbally, but “Bulk” by itself is not the official or accepted model name. BULK_LOGGED allows minimal logging for certain bulk operations while still permitting point-in-time recovery under constraints. Step-by-Step Solution: Check current model: SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME();Alter model: ALTER DATABASE MyDB SET RECOVERY BULK_LOGGED;Note the keyword is BULK_LOGGED, not BULK. Verification / Alternative check: SSMS database properties show the three choices exactly as Simple, Full, and Bulk-logged. Why Other Options Are Wrong: “Correct — Simple, Full, and Bulk” misnames the third model. The claim that only two models exist is false. Recovery model is per-database, not per server; compatibility level does not define recovery model. Common Pitfalls: Assuming “Bulk” is valid syntax; attempting ALTER DATABASE ... SET RECOVERY BULK will fail. Final Answer: Incorrect — the correct name is Bulk-logged, not Bulk

Question 7

Is the blanket claim “Nonclustered indexes are faster than clustered indexes for data retrieval” valid for SQL Server, or does performance depend on workload and query patterns?

Accepted Answer

Introduction / Context: Index selection in SQL Server is workload-dependent. Clustered and nonclustered indexes offer different benefits. Absolutes like “nonclustered is faster” are misleading and can lead to suboptimal designs. Given Data / Assumptions: A table can have one clustered index that defines the physical order of rows. Nonclustered indexes store keys plus row locators (RID or clustering key). Queries vary: point lookups, range scans, covering reads, aggregations. Concept / Approach: Clustered indexes excel at range queries on the clustering key and can reduce lookups when the clustering key participates in predicates and joins. Nonclustered indexes can be faster for selective point lookups, especially when they are covering (include all needed columns). Neither is universally “faster.” Proper design considers selectivity, covering, write overhead, and fragmentation. Step-by-Step Solution: Evaluate workload: predicates, joins, order by, group by.Choose a clustering key that supports common range/group operations.Add nonclustered indexes to cover selective queries (INCLUDE columns as needed).Validate with execution plans and wait statistics. Verification / Alternative check: Compare logical reads with SET STATISTICS IO ON and observe plans using the clustered index vs. a covering nonclustered index. Why Other Options Are Wrong: “Always faster” is false; range scans are often better on clustered indexes; heaps change lookup mechanics but do not make NCI universally faster; MAXDOP is irrelevant to this absolute claim. Common Pitfalls: Over-indexing; ignoring INCLUDE columns; poor clustering key selection causing excessive key lookups. Final Answer: Incorrect — performance depends on access patterns and design

Question 8

Trigger support in SQL Server: does the platform provide INSTEAD OF and AFTER triggers but not BEFORE triggers?

Accepted Answer

Introduction / Context: Different RDBMS engines implement different trigger types. In SQL Server, understanding which trigger kinds are available helps you implement business rules and updatable views correctly. Given Data / Assumptions: Object types: tables and views. Trigger types considered: AFTER, INSTEAD OF, BEFORE. Standard SQL Server behavior. Concept / Approach: SQL Server supports AFTER triggers on tables and INSTEAD OF triggers on both tables and views. BEFORE triggers are not part of SQL Server’s feature set (some other platforms have them). INSTEAD OF triggers are commonly used to make complex views updatable by re-routing DML to base tables. Step-by-Step Solution: Create AFTER trigger on a table: valid.Create INSTEAD OF trigger on a view: valid.Attempt a BEFORE trigger: results in syntax error. Verification / Alternative check: Check documentation or use SSMS GUI — BEFORE is not an available option; sys.triggers shows AFTER or INSTEAD_OF types only. Why Other Options Are Wrong: Claims that BEFORE triggers exist or that only one trigger type exists are incorrect. Compatibility level does not introduce BEFORE triggers. Common Pitfalls: Porting code from other databases that use BEFORE triggers without redesigning logic for SQL Server. Final Answer: Correct — SQL Server supports INSTEAD OF and AFTER; no BEFORE triggers

Question 9

Does Microsoft SQL Server implement the Transact-SQL language (commonly abbreviated as T-SQL) for querying and procedural logic?

Accepted Answer

Introduction / Context: Each major RDBMS adds extensions to the SQL standard. Microsoft SQL Server’s dialect is Transact-SQL (T-SQL), used across on-premises SQL Server, Azure SQL Database, and Azure SQL Managed Instance with minor differences by version. Given Data / Assumptions: We refer to Microsoft’s SQL Server family. T-SQL covers DDL, DML, and procedural constructs. Basic ANSI SQL is a subset within T-SQL. Concept / Approach: T-SQL extends the SQL standard with procedural programming (BEGIN…END, variables, control flow), error handling (TRY…CATCH), system functions, windowing enhancements, and engine-specific features (e.g., OUTPUT clause, MERGE behavior specifics). Understanding T-SQL is core to development and administration in SQL Server. Step-by-Step Solution: Write DDL: CREATE TABLE, CREATE INDEX;Write DML: SELECT/INSERT/UPDATE/DELETE enriched with T-SQL features (TOP, OUTPUT);Use procedural logic: DECLARE @i int; WHILE; TRY…CATCH;Invoke built-ins: e.g., STRING_SPLIT, JSON_VALUE (version-dependent). Verification / Alternative check: Official documentation and SSMS templates use T-SQL syntax and terminology consistently. Why Other Options Are Wrong: Azure SQL and on-prem both use T-SQL. DDL/DML division does not change the dialect. T-SQL is not deprecated. Common Pitfalls: Assuming portability of T-SQL code to other vendors without changes; relying on non-portable features. Final Answer: Correct

Question 10

Under pessimistic locking in SQL Server, are locks typically acquired when data is read (and held as needed) rather than waiting until the user issues an update before any locks are taken?

Accepted Answer

Introduction / Context: Pessimistic concurrency assumes conflicts are likely, so it protects data by locking. Understanding when locks are acquired helps explain blocking, deadlocks, and query behavior under different isolation levels. Given Data / Assumptions: We consider classic locking isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE). We exclude versioning levels (READ_COMMITTED_SNAPSHOT, SNAPSHOT) which reduce reader-writer blocking. Default SQL Server behavior applies. Concept / Approach: Under pessimistic locking, the engine acquires shared locks on reads and update/intent/exclusive locks on modifications. In READ COMMITTED, shared locks are taken during the read and released after the statement. In REPEATABLE READ, read locks persist longer, and in SERIALIZABLE, range locks prevent phantoms. Locks do not wait solely until an update occurs; reads also take locks as needed to ensure isolation semantics. Step-by-Step Solution: Run a SELECT under READ COMMITTED: observe shared locks with sys.dm_tran_locks.Run concurrent UPDATE: note potential blocking due to existing shared locks.Escalate isolation to REPEATABLE READ or SERIALIZABLE: observe longer-lived or range locks. Verification / Alternative check: Use SET TRANSACTION ISOLATION LEVEL and DMVs to view lock types and lifetimes during reads vs. updates. Why Other Options Are Wrong: Asserting “no locks until update” contradicts locking rules. READ UNCOMMITTED minimizes read locks, but that is not “pessimistic” and still does not defer all locking to updates. SNAPSHOT uses versioning, not no-lock semantics. Common Pitfalls: Confusing READ UNCOMMITTED (dirty reads) with no locking at all; assuming readers never block writers under pessimistic levels. Final Answer: Incorrect — pessimistic locking acquires locks during reads as required

Question 11

SQL Server security model: Is the following claim accurate?  "Selecting Windows Authentication only (disabling Mixed Mode) results in better security."

Accepted Answer

Introduction / Context: Authentication mode in Microsoft SQL Server determines how clients prove their identity. You can enable only Windows Authentication or use Mixed Mode (Windows Authentication plus SQL logins). Security guidance typically favors Windows-only authentication because it centralizes identity and policy enforcement in the operating system and Active Directory rather than keeping another password store inside SQL Server. Given Data / Assumptions: Windows-only mode disables SQL logins (such as sa). Active Directory domain policies are in place (account lockout, password history, multifactor integration, Kerberos, and auditing). Transport encryption and least-privilege are assumed as supporting controls. Concept / Approach: Windows Authentication leverages Kerberos/NTLM, centralized password policies, account lockouts, smart cards/MFA, and group-based authorization. Mixed Mode adds SQL logins that are managed in SQL Server and are not governed by domain password policies unless explicitly configured via CHECK_POLICY and related options. Reducing credential stores generally reduces attack surface. Step-by-Step Solution: Identify authentication choices: Windows-only vs. Mixed Mode.Evaluate controls: domain policies, MFA, Kerberos tickets, auditing—all apply to Windows logins.Assess attack surface: disabling SQL logins removes risks like weak sa passwords and credential spraying against SQL accounts.Conclusion: Windows-only typically yields stronger security posture. Verification / Alternative check: Review security baselines and CIS/Microsoft guidance; confirm no legacy apps require SQL logins. Where exceptions exist, mitigate with strong, rotated, and disabled-by-default SQL logins. Why Other Options Are Wrong: Incorrect: Ignores the added risk of SQL logins. Domain-only caveat: Windows Auth works securely in workgroups too (with local accounts), though domain adds benefits. TLS dependency: TLS protects transport; it does not negate identity benefits. Password length only: Security posture is broader than password length. Common Pitfalls: Leaving the sa login enabled; weak SQL login policies; overlooking service accounts and application pools; failing to audit login failures. Final Answer: Correct

Question 12

Core purpose of SQL Server indexes: Evaluate the statement:  "SQL Server indexes are special data structures used to improve performance."

Accepted Answer

Introduction / Context: Indexes in SQL Server organize data to accelerate lookups, joins, sorts, and predicates, much like a book index. They can dramatically reduce I/O and CPU for read-heavy workloads and can also improve write performance in some patterns by supporting seek access and narrowing locks. Understanding their role is foundational in database tuning. Given Data / Assumptions: We refer to the general case: clustered and nonclustered B-tree indexes as primary structures. Specialized indexes (columnstore, XML, spatial, full-text) exist but share the performance goal. Appropriate statistics and cardinality estimation are available. Concept / Approach: Indexes provide ordered access paths and mapping from key values to data locations. They reduce scans by enabling seeks, support efficient JOIN/ORDER BY/GROUP BY, and help the optimizer choose better plans. Trade-offs include additional storage, maintenance overhead on data modification, and potential write amplification. Step-by-Step Solution: Create a relevant index (e.g., CREATE INDEX ix ON dbo.Sales(CustomerId, OrderDate)).Re-execute queries: observe plan changes from scans to seeks.Measure I/O and CPU reductions via execution plans and SET STATISTICS IO/TIME.Validate that DML costs (INSERT/UPDATE/DELETE) are acceptable. Verification / Alternative check: Compare execution plans with and without indexes; use Query Store to confirm performance deltas; validate with realistic workloads. Why Other Options Are Wrong: Incorrect: Contradicts the core design of indexing. SELECT-only: Indexes also help JOIN, GROUP BY, window functions, and sometimes constraints. SSD-only / row-count-only: Benefits are logical and apply regardless of media or table size (though magnitude varies). Common Pitfalls: Over-indexing; redundant indexes; missing covering columns; stale statistics; ignoring write overhead. Final Answer: Correct

Question 13

Optimistic concurrency in SQL Server: Judge the claim:  "With optimistic locking, an update lock is placed on the row when the row is read."

Accepted Answer

Introduction / Context: Optimistic concurrency assumes that most transactions do not conflict. Instead of locking rows on read, the system detects conflicts at write time. SQL Server implements optimistic models via row versioning (e.g., READ COMMITTED SNAPSHOT and SNAPSHOT isolation) and application-side concurrency tokens (rowversion/timestamp or original value checks). Given Data / Assumptions: No explicit locking hints like UPDLOCK, XLOCK are used on the SELECT. We refer to optimistic patterns, not pessimistic locking. Isolation may be READ COMMITTED SNAPSHOT or SNAPSHOT, or application-enforced checks. Concept / Approach: In optimistic concurrency, readers do not take update locks. They either read versioned rows (row-versioning isolation) or read current data and carry an etag/rowversion. At update time, the engine or application verifies that data has not changed since read; if it has, the update is rejected or retried. Placing an update lock on read describes a pessimistic approach (UPDLOCK or SELECT FOR UPDATE semantics), not optimistic. Step-by-Step Solution: Reader performs SELECT without UPDLOCK under snapshot-style isolation → no update lock taken.On UPDATE, engine checks rowversion or detects write-write conflict; conflicting transaction rolls back.Therefore, the claim that an update lock is placed on read contradicts optimistic design. Verification / Alternative check: Use sys.dm_tran_locks and Extended Events to observe locks; compare behavior with and without UPDLOCK under different isolation levels. Why Other Options Are Wrong: Correct choices rely on pessimistic locking or explicit hints. RCSI-only: Even in RCSI, readers do not take update locks. UPDLOCK: That is a pessimistic override, not optimistic default. Common Pitfalls: Mixing optimistic and pessimistic assumptions; forgetting to include concurrency checks on update; assuming SNAPSHOT prevents all conflicts (it detects, not prevents). Final Answer: Incorrect

Question 14

Using reserved words as identifiers in SQL Server: Evaluate the statement:  "An SQL Server reserved word must be placed in brackets; otherwise SQL Server treats it as a keyword rather than an identifier."

Accepted Answer

Introduction / Context: SQL Server has a list of reserved keywords (e.g., SELECT, FROM, TABLE). If you want to use such a word as the name of a table, column, or other object, you must delimit it so the parser treats it as an identifier and not as part of SQL syntax. Given Data / Assumptions: Default T-SQL settings; QUOTED_IDENTIFIER may be ON or OFF. Example identifiers like [Table], [Key], [User]. We focus on standards-compliant and robust practices. Concept / Approach: SQL Server supports two delimiter styles for identifiers: square brackets [name] and double quotes "name" (with QUOTED_IDENTIFIER ON). Using brackets is the most common T-SQL approach. Without delimiters, a reserved word cannot be parsed as an identifier and will cause errors or ambiguous parsing. While best practice is to avoid reserved words entirely, bracketed or quoted identifiers are the supported escape mechanism. Step-by-Step Solution: Try CREATE TABLE select (id int); → parse error.Use CREATE TABLE [select] (id int); → succeeds (not recommended naming).Alternatively, with QUOTED_IDENTIFIER ON: CREATE TABLE "select" (id int);Conclusion: Reserved words must be delimited to be used as identifiers. Verification / Alternative check: Test statements in SSMS; review the list of reserved keywords in Microsoft documentation and note parsing failures without delimiters. Why Other Options Are Wrong: Incorrect: Contradicts parser behavior. Collation / QUOTED_IDENTIFIER caveats: Case or quoted-identifier settings do not make undelimited reserved words valid identifiers. System views only: The rule applies universally. Common Pitfalls: Building ORMs or code generators that emit un-delimited reserved names; changing QUOTED_IDENTIFIER unexpectedly. Final Answer: Correct

Question 15

Backup types in SQL Server: Judge the statement:  "A differential backup makes a copy of the entire database."

Accepted Answer

Introduction / Context: SQL Server supports several backup types: full, differential, and transaction log. Knowing what each captures is essential for designing backup and restore strategies that balance speed, storage, and recovery point objectives. Given Data / Assumptions: A prior full backup exists (required baseline for differentials). Differentials are cumulative since the last full backup. Standard SQL Server backup semantics apply. Concept / Approach: A full backup copies the entire database at the time of the backup. A differential backup copies only the extents changed since the most recent full backup (tracked via the differential changed map). A transaction log backup copies the log records to support point-in-time recovery in FULL or BULK_LOGGED recovery models. Therefore, a differential is not a full copy of the database; it is a smaller, incremental capture of changes since the last full. Step-by-Step Solution: Take FULL backup at T0.Make changes to data (INSERT/UPDATE/DELETE).Take DIFFERENTIAL at T1 → contains pages changed since T0.Restore flow: FULL (T0) + latest DIFFERENTIAL (T1) [+ LOG backups if needed]. Verification / Alternative check: Inspect msdb backup history and differential sizes; observe faster differentials when few changes occurred. Why Other Options Are Wrong: Correct (option b) misstates differential behavior. Small DB / DBCC: Size or DBCC checks do not change backup semantics. Recovery model: It affects log backups, not differential meaning. Common Pitfalls: Confusing differential with incremental log backups; forgetting that differentials grow larger over time until the next full backup. Final Answer: Incorrect

Question 16

Default transaction isolation in SQL Server: Assess the statement:  "READ COMMITTED is the default isolation level."

Accepted Answer

Introduction / Context: Transaction isolation level governs how concurrent transactions interact. In SQL Server, the default isolation level for new sessions is READ COMMITTED unless explicitly changed at the session/database level. Understanding this baseline is crucial for diagnosing blocking, deadlocks, and consistency anomalies. Given Data / Assumptions: No SET TRANSACTION ISOLATION LEVEL overrides have been issued for the session. Database options like READ_COMMITTED_SNAPSHOT may be ON or OFF; this changes implementation, not the named level. We consider standard SQL Server behavior. Concept / Approach: Under READ COMMITTED, statements cannot read data modified by other transactions that have not yet committed. With READ_COMMITTED_SNAPSHOT ON, SQL Server implements this using row versioning (nonblocking reads); otherwise it uses shared locks on read. The default level name remains READ COMMITTED in both cases. Step-by-Step Solution: Open a new session; query sys.dm_exec_sessions to confirm isolation_level.Observe behavior: dirty reads are prevented; nonrepeatable/phantom reads can occur.Enable READ_COMMITTED_SNAPSHOT and repeat: semantics stay READ COMMITTED, implementation switches to versioning. Verification / Alternative check: Use DBCC USEROPTIONS to display the current session’s isolation level. Review database properties for snapshot-based implementation. Why Other Options Are Wrong: Incorrect: Conflicts with default server behavior. Recovery model / tempdb: Recovery model and database choice do not redefine the default isolation level. RCSI-only: RCSI is an implementation detail for READ COMMITTED, not a different default level. Common Pitfalls: Assuming SNAPSHOT is the default; forgetting to measure blocking differences when switching RCSI on. Final Answer: Correct

Question 17

Ways to create or modify tables in SQL Server: Evaluate the claim:  "There is only one way to create and modify tables in SQL Server: writing SQL code using CREATE or ALTER."

Accepted Answer

Introduction / Context: SQL DDL statements (CREATE/ALTER) are the authoritative way to define schema. However, SQL Server also provides graphical and programmatic alternatives that ultimately generate DDL under the hood. The statement that there is only one way is therefore too restrictive. Given Data / Assumptions: Tools like SQL Server Management Studio (SSMS) and Azure Data Studio exist. Object-relational mappers (ORMs) and migration frameworks can emit DDL. SMO (SQL Server Management Objects) and DACPAC/SSDT pipelines can deploy schema changes. Concept / Approach: While the database engine ultimately executes DDL, administrators and developers can author those changes via GUIs, scripts, automated migrations, or DevOps pipelines. SSMS designers allow table creation and alteration through dialogs that produce DDL. Migration tools generate and run versioned CREATE/ALTER scripts. Thus, “only one way” is incorrect. Step-by-Step Solution: Open SSMS → Object Explorer → New → Table → Save: engine executes CREATE TABLE generated by the tool.Run Entity Framework migrations → emits DDL for schema drift.Use SSDT/DACPAC publish profiles → deploys compiled model to target. Verification / Alternative check: Inspect the generated T-SQL from GUI actions; examine migration scripts in source control to see CREATE/ALTER emitted automatically. Why Other Options Are Wrong: Correct choices with platform caveats are irrelevant; alternatives exist across editions and platforms. System vs. user tables: Both are created/altered via DDL; system tables are not altered by users. Common Pitfalls: Relying solely on GUI without scripting repeatable deployments; neglecting source control for schema. Final Answer: Incorrect

Question 18

Index types in SQL Server: Judge the statement:  "SQL Server supports two kinds of indexes: clustered and nonclustered."

Accepted Answer

Introduction / Context: At the core of SQL Server’s traditional rowstore storage engine are two primary B-tree index organizations: clustered and nonclustered. Many specialized index types exist today, but they are generally built on or alongside these fundamental structures or serve different storage engines (e.g., columnstore). Given Data / Assumptions: We refer to the foundational B-tree index categories in the rowstore engine. Specialized indexes (columnstore, XML, spatial, full-text) are recognized as additional features, not replacing the core dichotomy. Heaps are tables without a clustered index. Concept / Approach: A clustered index defines the table’s physical row order; there can be at most one. Nonclustered indexes maintain a separate key order and reference the base table via a row locator (RID for heaps, clustering key for clustered tables). These two kinds underpin most tuning, covering, and seek strategies. While modern SQL Server versions add columnstore and others, the canonical answer for “kinds of indexes” in the classic sense remains clustered vs. nonclustered. Step-by-Step Solution: Create clustered index on primary key → defines row order.Create nonclustered index to accelerate predicates/joins → uses row locator to fetch rows.Recognize specialized indexes as complementary to (not replacing) the two main B-tree kinds. Verification / Alternative check: Examine sys.indexes; note type values for clustered (1) and nonclustered (2) in rowstore, alongside other types for specialized indexes. Why Other Options Are Wrong: Incorrect ignores the conventional classification. Version/heap/compatibility caveats do not change the fundamental distinction. Common Pitfalls: Confusing heaps with an index type; assuming columnstore replaces B-trees universally; overusing nonclustered indexes without evaluating key/include design. Final Answer: Correct

Question 19

Stored procedure languages in SQL Server 2000: Evaluate the statement:  "The .NET languages are the most popular languages for writing stored procedures in SQL Server 2000."

Accepted Answer

Introduction / Context: SQL Server 2000 predates the Common Language Runtime (CLR) integration in the database engine. CLR stored procedures (using .NET languages like C#) were introduced later in SQL Server 2005. In SQL Server 2000, stored procedures were written in Transact-SQL (T-SQL) or as extended stored procedures in unmanaged DLLs, not in .NET languages. Given Data / Assumptions: Product version is SQL Server 2000. No CLR integration is available inside the database engine in this version. Extended stored procedures (xp_*) are C/C++ DLLs—unmanaged, not .NET. Concept / Approach: The assertion that .NET languages were commonly used in 2000 is historically inaccurate. T-SQL was the standard stored procedure language. Developers could build external middle-tier services in .NET after its release, but that is separate from in-database stored procedures. Therefore, the statement is false for SQL Server 2000 specifically. Step-by-Step Solution: Check version capabilities: SQL 2000 supports T-SQL procedures.Note introduction of CLR integration: SQL Server 2005 (CREATE ASSEMBLY, CREATE PROCEDURE EXTERNAL NAME ...).Conclude that .NET procedures were not a feature of SQL Server 2000. Verification / Alternative check: Review official feature lists per version; confirm absence of CREATE ASSEMBLY in SQL 2000 syntax. Why Other Options Are Wrong: Service packs/editions did not add CLR integration to 2000. Extended stored procedures used native code, not managed .NET. Common Pitfalls: Confusing application-tier .NET code with server-side stored procedures; assuming backward availability of features across versions. Final Answer: Incorrect

Question 20

Meaning of the prefix "dbo" in SQL Server: Judge the statement:  '"dbo" stands for database object.'

Accepted Answer

Introduction / Context: In SQL Server, object names are often schema-qualified (schema.object). The default schema for many operations is dbo. Misunderstanding what “dbo” stands for can cause confusion around ownership, permissions, and name resolution. Given Data / Assumptions: We refer to standard SQL Server nomenclature. Objects like dbo.TableName appear throughout databases. Schemas and users/roles are part of the security model. Concept / Approach: The identifier “dbo” is an abbreviation for database owner, not database object. Historically, dbo was the owner principal for the database. Today, dbo is also a schema name mapped to that owner context. Qualifying an object with dbo (e.g., dbo.Customers) places it in the dbo schema, which affects default resolution and can avoid ownership-chaining issues. It does not mean “database object.” Step-by-Step Solution: Observe object naming: schema.object, e.g., dbo.Sales.Check security: the dbo principal has full permissions within the database.Understand schema behavior: default schema for many logins is dbo; two users can have tables with the same base name in different schemas. Verification / Alternative check: Query sys.schemas and sys.database_principals to see dbo entries; review documentation on ownership and schemas. Why Other Options Are Wrong: Correct claims conflate schema name with a generic phrase; dbo is specific to ownership. Master/Azure caveats: Meaning is consistent across databases and deployment models. Common Pitfalls: Assuming dbo is merely a prefix; neglecting to set default schema for users; mixing owner and schema concepts. Final Answer: Incorrect

SQL Server 2000 Questions