Page 1 of 141
NYC CERTIFIED IT ADMINISTRATOR (DATABASE) FINAL EXAM
QUESTIONS WITH DETAILED- VERIFIED ANSWERS- ALREADY
GRADED A+ || NEWEST EXAM 2025-2026
Information Technology – Database Administration
This exam covers the NYC Certified IT Administrator (Database)
curriculum: SQL Server installation, configuration, and patching;
database architecture, relational design, and schema management;
data modeling and normalization; backup and restore strategies (full,
differential, transaction log); disaster recovery and business continuity;
high availability (Always On, failover clustering, replication, log shipping);
performance monitoring and optimization (indexing, query tuning,
execution plans); security administration (logins, roles, permissions,
authentication, encryption); troubleshooting, root cause analysis, and
incident response; ETL concepts and SSIS; reporting and SSRS basics;
data warehousing (star schema, snowflake, fact/dimension tables);
Windows Server basics; storage management and capacity planning;
automation (SQL Agent jobs, scripts, maintenance plans); ACID
properties and transaction management; migration and deployment;
documentation, change control, and compliance; data privacy and
security policies; concurrency control and locking; scenario‑based
problem solving.
Question 1
The database administrator for a New York City agency is building a new
SQL Server 2022 instance to host a critical financial application. The
, Page 2 of 141
agency requires that the entire database be encrypted at rest to protect
against unauthorized access to the underlying data files in the event the
storage media is stolen. The administrator needs to select the correct
encryption technology that operates at the database file level without
requiring changes to the application code. Which feature should be
used?
A) Column‑level encryption using ENCRYPTBYPASSPHRASE.
B) Transparent Data Encryption (TDE).
C) Always Encrypted.
D) BitLocker Drive Encryption.
CORRECT ANSWER: B
RATIONALE: TDE encrypts the entire database at the page level,
preventing access to the data files (.mdf, .ldf) without the certificate. It is
transparent to the application, requiring no code changes. Column‑level
encryption (A) and Always Encrypted (C) protect only selected columns
and may require application changes. BitLocker (D) encrypts the entire
drive but is not a database‑native feature.
Question 2
A senior database administrator for a government agency is planning the
physical layout of a new SQL Server instance that will host several large
databases. The administrator wants to isolate the transaction log files
from the data files on separate physical drives to maximize performance
and recoverability. Which of the following statements best describes the
primary reason for placing transaction log files on a dedicated disk
array?
A) To reduce the chance of index fragmentation.
B) To improve query performance for read‑only workloads.
, Page 3 of 141
C) To prevent a single disk failure from losing both data and log files, and
to improve write throughput for sequential log operations.
D) To enable automatic compression of the log files.
CORRECT ANSWER: C
RATIONALE: Separating the transaction log from data files provides two
benefits: (1) it improves write performance because the log is written
sequentially, and (2) it improves recoverability; if the drive containing
data files fails, the log files on a separate drive may still be intact,
allowing point‑in‑time recovery. Options A and B are not directly related
to log file placement, and option D is incorrect.
Question 3
An IT administrator in the New York City Department of Citywide
Administrative Services (DCAS) is responsible for managing SQL Server
backups. The current backup strategy is a full backup every Sunday
evening, differential backups nightly, and transaction log backups every
hour. The differential backup runs take an increasingly long time as the
week progresses. Which of the following modifications would most
effectively reduce the duration of the differential backups while still
maintaining a recoverability objective of 15 minutes?
A) Increase the frequency of full backups to daily and eliminate
differential backups.
B) Replace differential backups with a copy‑only backup strategy.
C) Implement a filegroup backup strategy for the largest tables.
D) Replace the nightly differential backups with a CHECKPOINT
command executed every six hours.
CORRECT ANSWER: A
, Page 4 of 141
RATIONALE: Differential backups store all changes since the last full
backup, so they grow in size throughout the week. Switching to daily full
backups would eliminate the need for large differential backups, though
it requires more storage. Log backups (hourly) would still meet the
15‑minute RPO. Option B (COPY_ONLY) would not reduce differential
backup times. Option C (filegroup backups) adds complexity. Option D
does not produce a backup.
Question 4
A database administrator is analyzing a query that joins five large tables.
The execution plan shows a “Hash Join” operation on a table that has a
recent, updated histogram, but the estimated number of rows is
significantly lower than the actual number returned, causing a memory
grant spill to tempdb. The administrator suspects that the statistic
histogram is not properly representing the distribution of values in the
joining column. Which of the following actions should be taken first to
resolve the incorrect cardinality estimation without causing excessive
recompilation overhead?
A) Create a filtered statistic on the joining column with the FULLSCAN
option.
B) Rewrite the query using a MERGE JOIN hint to bypass the hash
operation.
C) Increase the max degree of parallelism (MAXDOP) setting for the
instance.
D) Add a NOEXPAND hint to the query.
CORRECT ANSWER: A
RATIONALE: When a histogram does not properly reflect the distribution
of values in a column, creating a filtered statistic with FULLSCAN (or
updating statistics with FULLSCAN) can improve cardinality estimation
for queries that use that column. This directly addresses the root cause
NYC CERTIFIED IT ADMINISTRATOR (DATABASE) FINAL EXAM
QUESTIONS WITH DETAILED- VERIFIED ANSWERS- ALREADY
GRADED A+ || NEWEST EXAM 2025-2026
Information Technology – Database Administration
This exam covers the NYC Certified IT Administrator (Database)
curriculum: SQL Server installation, configuration, and patching;
database architecture, relational design, and schema management;
data modeling and normalization; backup and restore strategies (full,
differential, transaction log); disaster recovery and business continuity;
high availability (Always On, failover clustering, replication, log shipping);
performance monitoring and optimization (indexing, query tuning,
execution plans); security administration (logins, roles, permissions,
authentication, encryption); troubleshooting, root cause analysis, and
incident response; ETL concepts and SSIS; reporting and SSRS basics;
data warehousing (star schema, snowflake, fact/dimension tables);
Windows Server basics; storage management and capacity planning;
automation (SQL Agent jobs, scripts, maintenance plans); ACID
properties and transaction management; migration and deployment;
documentation, change control, and compliance; data privacy and
security policies; concurrency control and locking; scenario‑based
problem solving.
Question 1
The database administrator for a New York City agency is building a new
SQL Server 2022 instance to host a critical financial application. The
, Page 2 of 141
agency requires that the entire database be encrypted at rest to protect
against unauthorized access to the underlying data files in the event the
storage media is stolen. The administrator needs to select the correct
encryption technology that operates at the database file level without
requiring changes to the application code. Which feature should be
used?
A) Column‑level encryption using ENCRYPTBYPASSPHRASE.
B) Transparent Data Encryption (TDE).
C) Always Encrypted.
D) BitLocker Drive Encryption.
CORRECT ANSWER: B
RATIONALE: TDE encrypts the entire database at the page level,
preventing access to the data files (.mdf, .ldf) without the certificate. It is
transparent to the application, requiring no code changes. Column‑level
encryption (A) and Always Encrypted (C) protect only selected columns
and may require application changes. BitLocker (D) encrypts the entire
drive but is not a database‑native feature.
Question 2
A senior database administrator for a government agency is planning the
physical layout of a new SQL Server instance that will host several large
databases. The administrator wants to isolate the transaction log files
from the data files on separate physical drives to maximize performance
and recoverability. Which of the following statements best describes the
primary reason for placing transaction log files on a dedicated disk
array?
A) To reduce the chance of index fragmentation.
B) To improve query performance for read‑only workloads.
, Page 3 of 141
C) To prevent a single disk failure from losing both data and log files, and
to improve write throughput for sequential log operations.
D) To enable automatic compression of the log files.
CORRECT ANSWER: C
RATIONALE: Separating the transaction log from data files provides two
benefits: (1) it improves write performance because the log is written
sequentially, and (2) it improves recoverability; if the drive containing
data files fails, the log files on a separate drive may still be intact,
allowing point‑in‑time recovery. Options A and B are not directly related
to log file placement, and option D is incorrect.
Question 3
An IT administrator in the New York City Department of Citywide
Administrative Services (DCAS) is responsible for managing SQL Server
backups. The current backup strategy is a full backup every Sunday
evening, differential backups nightly, and transaction log backups every
hour. The differential backup runs take an increasingly long time as the
week progresses. Which of the following modifications would most
effectively reduce the duration of the differential backups while still
maintaining a recoverability objective of 15 minutes?
A) Increase the frequency of full backups to daily and eliminate
differential backups.
B) Replace differential backups with a copy‑only backup strategy.
C) Implement a filegroup backup strategy for the largest tables.
D) Replace the nightly differential backups with a CHECKPOINT
command executed every six hours.
CORRECT ANSWER: A
, Page 4 of 141
RATIONALE: Differential backups store all changes since the last full
backup, so they grow in size throughout the week. Switching to daily full
backups would eliminate the need for large differential backups, though
it requires more storage. Log backups (hourly) would still meet the
15‑minute RPO. Option B (COPY_ONLY) would not reduce differential
backup times. Option C (filegroup backups) adds complexity. Option D
does not produce a backup.
Question 4
A database administrator is analyzing a query that joins five large tables.
The execution plan shows a “Hash Join” operation on a table that has a
recent, updated histogram, but the estimated number of rows is
significantly lower than the actual number returned, causing a memory
grant spill to tempdb. The administrator suspects that the statistic
histogram is not properly representing the distribution of values in the
joining column. Which of the following actions should be taken first to
resolve the incorrect cardinality estimation without causing excessive
recompilation overhead?
A) Create a filtered statistic on the joining column with the FULLSCAN
option.
B) Rewrite the query using a MERGE JOIN hint to bypass the hash
operation.
C) Increase the max degree of parallelism (MAXDOP) setting for the
instance.
D) Add a NOEXPAND hint to the query.
CORRECT ANSWER: A
RATIONALE: When a histogram does not properly reflect the distribution
of values in a column, creating a filtered statistic with FULLSCAN (or
updating statistics with FULLSCAN) can improve cardinality estimation
for queries that use that column. This directly addresses the root cause
