SQL Server Interview and Assessment Free Questions

SQL Server Interview and Assessment Questions

• Question: Which databases could not be backed up using the native TSQL backup command? 
• Answer: Resource DB 

• Question: Can we perform tail log backup if .MDF file is corrupted and no bulk logged operation happened? 
• Answer: Yes 

• Question: I have my PROD SQL Server 2016 and all system databases located in E drive and we want to move the Resource database to M drive. Is this possible? 
• Answer: No

• Question: What are the phases of the SQL Server restoration process? 
• Answer: Redo committed transaction  and then Undo un-committed transaction 

• Question: Which database state should enable you to shutdown and restart the database if the database is in suspect mode?
• Answer: EMERGENCY

• Question: Revisit A transactional database is facing a performance issue. Which areas of a bottleneck as a DBA would you investigate? 
• Blocking 
• Database Code 
• Database configuration 
• All of the above
• Answer: All of the above

• Question: When are the statistics created on a table or index? Choose the appropriate answer. 
• Statistics are created when the table is created if AUTO_UPDATE_STATISTICS in ON at the database level. 
• Statistics are created due to index creation. 
• User-defined statistics are created as well. 
• All of the above
• Answer: All of the above

• Question: Which of the below is checkpoint behavior? 
• t is automatic at the instance level based on the recovery interval setting. 
• Writes log records from buffer to Disk. 
• Can be issued explicitly using the CHECKPOINT statement. 
• All of the Above
• Answer: All of the above

• Question: Revisit Choose the best option Which of the below queries will show the last restarted time of the SQL server? Choose all that apply. 
• SELECT sqlserver_start_time FROM sys.dm_os_sys_info; 
• SELECT start_time from sys.traces WHERE is_default=1 
• SELECT crdate FROM sys.sysdatabases WHERE name = 'TEMPDB' 
• All of the above 
• Answer: All of the above

• Question: Which are applicable for indexed views in SQL 2017? Choose one. 
• Creating a unique clustered index on a view improves query performance 
• All DML operations on the base table reflect on Indexed views. 
• The query optimizer may use indexed views to speed up the query execution 
• All of the Above
• Answer: All of the above

• Question: As a DBA, you are monitoring the OLTP database. You observed that the wait type CXPACKET is high. What are the steps you would take to minimize this wait type? 
• Identify the top running queries with this wait type. 
• Review the execution plan of the query. 
• Use MAXDOP HINT with Value 1 in query. 
• All of the above
• Answer: All of the above 

• Other SQL Server Features
• Question: Which are features not supported in memory tables? 
• Answer: Linked Servers, Mirroring, Replication 

• Question: Revisit Choose the best option You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following requirements: Changes made by the stored procedure must be discarded If any error occurs a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure What should you include in the design? 
• An explicit transaction that has XACT ABORT disabled. 
• An implicit transaction that has error handling enabled 
• An explicit transaction that has error handling enabled 
• An implicit transaction that has XACTABORT enabled 
• Answer: An explicit transaction that has error handling enabled 

• Question: Revisit Choose the best option What is the right sequence of steps to create a partition? 
• • Option1
• step 1 Create a partition schema 
• step 2 Create a partition function 
• step 3 Create a table on partition schema 
• step 4 Create indexes on the partitioned table 
• • Option 2
• Step 1 Create a partition function 
• Step 2 Create a partition schema 
• Step 3 Create an index 
• • Option 3
• Step 1 Create a partition function 
• Step 2 Create a partition schema 
• Step 3 Create a table on partition schema 
• Step 4 Create indexes on the partitioned table 
• • Option 4
• Step 1 Create a partition range 
• Step 2 Create a partition schema 
• Step 3 Create a table on partition schema 
• Step 4 Create indexes on the partitioned table
• Answer: Option 3

• Question: You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments, You have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted. You need to grant the correct permission to the service account that backs up the HRDB database. Which permission should you grant? 
• Answer: View Definition

• Question: What are the components of SQL Server Architecture? 
• SQL OS 
• Query Processor 
• Relational Engine 
• Storage Engine
• All of the Above 
• Answer: All of the Above

• Question: Revisit All the below options can export data from the SQL server except 
• DB Tuning Wizard 
• BCP 
• IMPORT EXPORT WIZARD 
• SSIS
• Answer: DB Tuning Wizard

• Question: What are pseudo tables created when the UPDATE command is issued in the SQL server? 
• Answer: Inserted Table & Deleted Table also called Magic tables

• Question: You manage an SQL Server 2016 instance that contains a database named DB1. Users report that some queries to DB1 take longer than expected. Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file. Which method should you use? 
• Answer: Examine the Data File I/O pane in Activity Monitor.

• Question: Revisit Choose the best option Which of the below statement can be used to estimate TEMPDB space for the database in SQL Server? 
• Answer: DBCC CHECKDB WITH NO INFOMSGS, ESTIMATEONLY 

• Question: Revisit Choose the best option SQL 2019 can be installed on which of the following platforms? 
• Windows 
• Linux 
• Docker 
• All of the above
• Answer: All of the above'

• Question: Which process makes sure that enough memory is available in the buffer pool?
• Answer: Lazy Writer

• Question: How will you find the corrupted page details from a Database which is in suspect mode? 
• Select * from msdb..supect_pages 
• Read SQL error log to find the corrupted Page ID 
• Run DBCC CHECKDB with table results 
• All of the Above
• Answer: All of the above

• Question: Which page holds the most critical information about the database? 
• GAM
• SGAM 
• Data Page 
• Boot Page
• Answer: Boot Page

• Question Azure managed instance currently does not support which feature? 
• Built-in SSAS service. 
• Multiple data files per database 
• One log file per database 
• Supports Azure Resource Manager deployment
• Answer: Built-in SSAS service

• Question: The sys.dm_db_file_space_usage DMV can be used to monitor?
• Answer: Returns space usage information for each data file in the database.

• Question: Which of the following statement is true for the index? Select the most appropriate one. 
• Dropping clustered index takes a long time on the huge table which also has a non-cluster index on it. 
• After dropping the clustered index, we need not rebuild the non-cluster index 
• Creating a clustered index on a heap does not impact the non-cluster indexes on the same table. 
• Non-cluster index on HEAP does not point to RID
• Answer: Dropping clustered index takes a longer time on the huge table which also has a non-cluster index on it.

• Question: As a project DBA, you are requested to install SQL Server 2017 on Azure Virtual Machine. After installation, you find the collation level set is incorrect as per the application requirement. What are the two actions which will solve the problem? 
• Answer: Rebuild the master database specifying new collation using the setup command.  OR Uninstall and re-install SQL server with correct collation setting. 

• Question: Your network contains an Active Directory domain that has two groups named Group1 and Group2. The domain contains two SQL Server instances named SQL Dev and SQL.Prod. Each SQL Server instance has access to various storage media. The SQL Server instances have a database that contains a table named Table 1. Table 1 contains a column named Column 1. The value for Column1 can be either Value 1 or Value2. You need to recommend a solution to ensure that users in Group 1 can retrieve only rows from Column1 that contain the value of Value 1. What should you recommend? 
• A dynamic management view 
• Filegroups 
• Snapshot isolation 
• User-defined views
• Answer: User-defined views

• Question: You have a server that has SQL Server 2016 installed. The server contains 100 user databases. You need to recommend a backup solution for the user databases. The solution must meet the following requirements: Perform a transaction log backup every hour. Perform a full backup of each database every week. Perform a differential backup of each database every day. Ensure that new user databases are added automatically to the backup solution. What should you recommend? 
• Policy-Based Management
• A Data Definition Language (DDL) trigger
• SQL Server Agent jobs
• A maintenance plan
• Answer: Policy-Based Management

• Question: Select applicable DMVs which can be used to analyze latches and wait for stats. Choose two options that apply 
• sys.dm_os_wait_stats 
• sys.dm_os_waiting 
• sys.dm_os_latch_stats 
• sys.stats
• Answer: sys.dm_os_wait_stats 

• Question: You have enabled query store for SQL 2019 database called DB1, as DBA you want to reduce the retention period of persisted runtime statistics and inactive queries to 14 days instead of the default 30 days, which command will you run? 
• ALTER DATABASE DB1SET QUERY STORE (QUERY_CAPTURE_MODE = ALL) 
• ALTER DATABASE DB1SET QUERY STORE (MAX_STORAGE SIZE_MB = 50) 
• ALTER DATABASE DB1 SET QUERY_STORE (CLEANUP_POLICY = (STALE QUERY_THRESHOLD DAYS = 14) 
• ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE MODE = NONE)
• Answer: ALTER DATABASE DB1 SET QUERY_STORE (CLEANUP_POLICY = (STALE QUERY_THRESHOLD DAYS = 14) 

• Question: The application team is planning to load more than 10 million records to sales, order, and purchase tables in the ERP database in SQL. What are the best practices you would advocate to load the data faster? Choose the best option Disable the updated statistics and create statistics on tables, 
• Change to bulk recovery model and load the data in batches, 
• Take log backups frequently after batch load.
• Rebuild indexes after the load is completed. 
• All of the above
• Answer: All of the above

• Question What SQL Agent role requires deleting local job history? O 
• DatabaseMailUser Role 
• SQLAgentOperator Role 
• Odb_ssisoperator 
• dc_operator
• Answer: SQLAgentOperator Role

• Question: The client has development and production SQL databases of 80 hosted in 20 SQL instances. He is looking for a solution to monitor these databases without purchasing third-party monitoring software. As a DBA, you are planning to recommend Utility Control Point which comes with SQL 2016. Which of the below is applicable? 
• Need to consider a central database to store the collected information called PWD
• Create SQL agent jobs to collect data on each individual SQL instance, 
• Produces the CPU and Space utilization of SQL databases. 
• All of the Above
• Answer: All of the Above

• Question: Revisit Choose the best options, There is a database that is publishing to one subscriber some database objects. After some time, you notice the replication stops, and the log reader agent issues an error message that cannot execute the sp_replcmd". Which steps below can you select to isolate the issue? 
• Add verbose log to the log reader agent to see the complete error message. 
• Check the Log reader agent history. 
• Open the replication monitor and run tracer tokens. 
• Run the sp_replemds manual and see the error message.
• Answer: Add a verbose log to the log reader agent to see the complete error message

• Question: How do you see the cluster quorum configuration on your SQL database server in Windows 2016 Choose two options that apply 
• Answer: Either Open Failover cluster manager - Manage Cluster > Cluster Name -> Look at Summary  OR Open command prompt and run the command c:\Cluster quorum 

• Question: During the creation of a memory-optimized table what happens? 
• Memory Optimized table created using DDL 
• Code generation and compilation happens 
• Table DLL produced 
• Table DLL loaded into Memory. 
• All of the above
• Answer: All of the above

• Questions: There has been slow performance reported on a principal server in SQL 2016 which has an OLTP database mirrored to another region in High Protection mode. Choose one of the options below. 
• Identify the log running queries which are active and report to developers with an SQL plan to tweak 
• If index rebuilds and consistency checks are running at peak time, try to reschedule them after peak hours 
• Change the mode to HIGH-PERFORMANCE MODE till all the peak OLTP transactions have been completed and then switch it back to HIGH SAFETY mode during off-peak hours. 
• All of the above
• Answer: All of the above

• Question: There is a database of 30 TB and one large table of 5 TB with 8 TB of indexes with 60 partitions. What will strategy to update statistics for this table in SQL Server? Choose the two options.
• Run UPDATE STATISTICS WITH FULL SCAN on table. 
• Run UPDATE STATISTICS with low scan percentage 
• Tum on INCREMENTAL STATS on this table. 
• Run update statistics only on the partition which has recent data 
• Answer: Option 2 and 4

• Question: Which windows cluster services process is used to check node availability for both OS and SQL? 
• IsAlive 
• Looks Alive 
• HeartBeat
• None of the above
• Answer: IsAlive

• Question: Revisit Choose the best option In Always on the configuration in two node SQL 2017 instance hosted windows 2016 VM in same Azure region, what are the options you can configure? 
• Asynchronous-commit mode 
• Synchronous-commit mode
• Automatic Failover 
• All of the above
• Answer: All of the above

• Question: Revisit Choose the best option(s) An application database with File Table objects is planned for migration within their data center. During the planning phase, the customer is requesting for HA solution in SQL 2017 after the migration is completed with HA of 99.99%. Out of the below options choose any Two. 
• Answer: Propose WESC with SQL cluster OR Propose for Always ON 

• Question: Which SQL Server service is not cluster aware? 
• SQL Server Integration Services 
• SQL server database Engine
• SQL Server Reporting services 
• SQL Server Analysis Services 
• Answer: SQL Server Integration Services 

• Question: Where to set up sync with a backup option in transactional replication? 
• Publisher
• Subscriber 
• Distributor 
• Source Database
• Answer: Publisher

• Question: Transactional replication can be used to push changes made in an Azure SQL Managed Instance to which of the following targets? 
• SQL Server on-premise 
• Azure SQL Database 
• SQL Server on Azure VM 
• All of the above
• Answer: All of the above

• Question: What isolation levels will provide completely read-consistent views of a database to all transactions?
• Answer (SQL Server 2000): Only the SERIALIZABLE isolation level will provide a completely read-consistent view of a database to a given transaction. 

• Question: For one of the reporting databases, the users are reporting data inaccuracy when pulling up reports. To reduce this, what is the isolation that you would set with optimum concurrency? 
• READ COMMITTED 
• SERIALIZABLE 
• SNAPSHOT WITH READ COMMITTED 
• SNAPSHOT
• Answer: SERIALIZABLE 

• Question: Two application users run ad-hoc queries to pull some reports from the OLTP database. Both the sessions are referencing the same tables in the predicate. They end up in a deadlock. If both sessions have a HIGH deadlock priority set, how does the engine choose the victim? Select the option which is the most appropriate. 
• Engine chooses the session as a victim which has the lowest completion time. 
• Engine chooses the session which has the highest completion time to execute. 
• Engine chooses the session which has the lowest roll-back time as a victim. 
• Engine chooses the session which has the highest roll-back time to execute. 
• Answer: The engine chooses the session which has the lowest roll-back time as the victim. 

• Question: Within the READ_COMMITTED isolation level, during a read operation how long are locks held/retained?
• Answer: When SQL Server executes a statement at the read committed isolation level, it acquires short-lived share locks on a row-by-row basis. The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row. 

• Question: Within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking, how long are locks held/retained for?
• Answer: Within either of these isolation levels, locks are held for the duration of the transaction, unlike within the READ_COMMITTED isolation level as noted above.

• Question: Can locks ever be de-escalated?
• Answer: No, locks are only escalated, never de-escalated.

• Question: What are the different types of lock modes in SQL Server5?
• Answer:
• Shared
• Update
• Exclusive
• Schema (modification and stability)
• Bulk Update
• Intent (shared, update, exclusive)
• Key Range (shared, insert, exclusive)

• Question: Name as many of the lockable resources as possible in SQL Server.
• Answer:
• RID (single row on a heap)
• KEY (single row (or range) on an index)
• PAGE
• EXTENT
• HOBT (heap or b-tree)
• TABLE (entire table, all data and indexes)
• FILE
• APPLICATION
• METADATA
• ALLOCATION_UNIT
• DATABASE

• Question: The business users are running validation queries with NOLOCK hints so that the data load operations are not blocked. What does the NOLOCK hint do? Choose the best possible option. 
• It does not issue any locks. 
• It is similar to the READ COMMITTED isolation level. 
• It allows dirty page reads. 
• None of the above
• Answer: It allows dirty page reads. 

• Question: What requirements must be met for a BULK-UPDATE lock to be granted?
• Answer: Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.

• Question: What is the least restrictive type of lock?
• Answer: Shared lock is the least restrictive type of lock is a shared lock. 

• Question: What is the most restrictive type of lock?
• Answer: schema-modification is the most restrictive type of lock a shared lock.

• Question: What is a deadlock and how is it different from a standard block situation?
• Answer: A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource that the other tasks are trying to lock. 

• Question: Which 2 isolation levels support optimistic/row-versioned-based concurrency control?
• Answer: First is the READ COMMITTED isolation level.  This is the only level that supports both a pessimistic (locking-based) and optimistic (version-based) concurrency control model. The second is the SNAPSHOT isolation level that supports only an optimistic concurrency control model.

• Question: What database options must be set to allow the use of optimistic models?
• Answer: READ_COMMITTED_SNAPSHOT option for the read committed optimistic model. ALLOW_SNAPSHOT_ISOLATION option for the snapshot isolation level

• Question: What is the size of a lock structure?
• Answer:  96 bytes

• Question: From the list below, which lock mode increases the concurrency of the application queries? 
• Answer: Schema lock