Search test library by skills or roles
⌘ K

Adaface Sample SQL Server Questions

Here are some sample SQL Server questions from our premium questions library (10273 non-googleable questions).

Skills

🧐 Question

Medium

Backup Strategy
Backups
Troubleshooting
Solve
As a DBA, you receive an alert notifying you that the production database has gone offline due to a severe issue. Fortunately, you have a proper backup strategy in place. The backups are performed as follows:
            
            1. Full database backup every Sunday at 2:00 AM.
            2. Differential backup every day at 2:00 AM, except Sunday.
            3. Transaction log backup every hour.
Today is Wednesday, and the failure occurred at 10:15 AM. You have the following backup files available:
            
            1. Full backup: Full_Backup_Sun.bak taken on Sunday 2:00 AM.
            2. Differential backups: Diff_Backup_Mon.bak, Diff_Backup_Tue.bak, Diff_Backup_Wed.bak taken at 2:00 AM on their respective days.
            3. Transaction log backups: Hourly backups from Sunday 3:00 AM until Wednesday 10:00 AM, like TLog_Backup_Wed_09.bak, TLog_Backup_Wed_10.bak.
Given the RPO (Recovery Point Objective) of 15 minutes, which of the following sequences of restore operations would ensure minimal data loss?
A: Full_Backup_Sun.bak, Diff_Backup_Wed.bak, then all Transaction Log backups from Wednesday.
            
            B: Full_Backup_Sun.bak, Diff_Backup_Tue.bak, then all Transaction Log backups from Tuesday and Wednesday.
            
            C: Full_Backup_Sun.bak, Diff_Backup_Wed.bak, then Transaction Log backups from Wednesday 2:00 AM to 10:00 AM.
            
            D: Full_Backup_Sun.bak, then all Transaction Log backups from Sunday to Wednesday 10:00 AM.
            
            E: Full_Backup_Sun.bak, Diff_Backup_Mon.bak, Diff_Backup_Tue.bak, Diff_Backup_Wed.bak, then Transaction Log backups from Wednesday 2:00 AM to 10:00 AM.

Medium

Optimizing Query Performance
Indexing
Join Optimization
Execution Plans
Solve
You are managing a SQL Server database for a large e-commerce platform. The database contains the following tables:
 image
Users often run a query to retrieve all orders from a specific date along with customer details and a breakdown of each order. Lately, this query has been performing poorly, especially on days with a high volume of orders.
            
            Given this schema, which of the following changes would MOST LIKELY enhance the performance of this query?
A: Create a non-clustered index on Orders(OrderDate, OrderID) and a clustered index on OrderDetails(OrderID).
            B: Create a clustered index on Orders(CustomerID, OrderDate) and a non-clustered index on OrderDetails(ProductName).
            C: Increase the size of the OrderDetails(ProductName) column and add more RAM to the SQL Server machine.
            D: Create a clustered index on Orders(OrderDate) and a non-clustered index on OrderDetails(OrderID, Quantity).
            E: Partition the Orders table on OrderDate and create a non-clustered index on OrderDetails(DetailID, Price).

Medium

Transaction Isolation
Transaction Isolation Levels
Snapshot Isolation
Solve
You are managing a SQL Server instance that is experiencing performance degradation. After some analysis, you realize that the TempDB is under heavy stress due to numerous long-running transactions. Users have reported that some SELECT queries on a large table, named SalesData, are slower than expected.
            
            You consider implementing Snapshot Isolation to mitigate blocking issues. You're aware that Snapshot Isolation uses TempDB to store row versions.
            
            Given the situation, which combination of actions will help alleviate the stress on TempDB and enhance the performance of SELECT queries on SalesData?
A: Move TempDB to a faster storage subsystem and enable Snapshot Isolation for SalesData.
            B: Increase the number of TempDB data files, shrink TempDB size, and enable Snapshot Isolation for the database.
            C: Implement Read Committed Snapshot Isolation (RCSI) for the database and partition the SalesData table.
            D: Reduce the TempDB size, implement table partitioning on SalesData, and enable Read Uncommitted isolation level for the SELECT queries.
            E: Create a non-clustered index on frequently queried columns of SalesData and enable row versioning for the entire database.

Medium

Transaction Log Management
Performance Tuning
Log Management
Solve
You are a DBA at a large company managing an SQL Server database which is crucial for daily operations. The database is configured with the Full recovery model. The database is experiencing considerable transaction log growth during business hours, which is impacting the disk space and performance.
            
            The following operations are performed on this database:
            
            1. A large ETL process that runs every night, which transforms and loads data into several tables.
            2. A data archiving job that runs every night, which removes old data from several tables.
            3. Frequent read/write operations during the day as part of normal business operations.
            
            Given this scenario, which of the following strategies could help manage the transaction log growth effectively?
A: Switch to the Simple recovery model.
            B: Schedule frequent log backup and cleanups during business hours.
            C: Shrink the transaction log file size during business hours.
            D: Increase the database file size.
🧐 Question🔧 Skill

Medium

Backup Strategy
Backups
Troubleshooting

3 mins

SQL Server
Solve

Medium

Optimizing Query Performance
Indexing
Join Optimization
Execution Plans

3 mins

SQL Server
Solve

Medium

Transaction Isolation
Transaction Isolation Levels
Snapshot Isolation

3 mins

SQL Server
Solve

Medium

Transaction Log Management
Performance Tuning
Log Management

3 mins

SQL Server
Solve
🧐 Question🔧 Skill💪 Difficulty⌛ Time
Backup Strategy
Backups
Troubleshooting
SQL Server
Medium3 mins
Solve
Optimizing Query Performance
Indexing
Join Optimization
Execution Plans
SQL Server
Medium3 mins
Solve
Transaction Isolation
Transaction Isolation Levels
Snapshot Isolation
SQL Server
Medium3 mins
Solve
Transaction Log Management
Performance Tuning
Log Management
SQL Server
Medium3 mins
Solve

Trusted by recruitment teams in enterprises globally

Amazon Morgan Stanley Vodafone United Nations HCL PayPal Bosch WeWork Optimum Solutions Deloitte Microsoft NCS Doubtnut Sokrati J&T Express Capegemini

We evaluated several of their competitors and found Adaface to be the most compelling. Great library of questions that are designed to test for fit rather than memorization of algorithms.


Swayam Narain, CTO, Affable

hashtag image heart icon Swayam
customers across world
Join 1500+ companies in 80+ countries.
Try the most candidate friendly skills assessment tool today.
g2 badges
Ready to streamline your recruitment efforts with Adaface?
Ready to streamline your recruitment efforts with Adaface?
logo
40 min tests.
No trick questions.
Accurate shortlisting.
ada
Ada
● Online
Previous
Score: NA
Next
✖️