SQL Server 2016 Interview Questions
SQL Server 2016 is one of the most exciting database releases from Microsoft in recent years. It’s expected that SQL Server will become the most popular enterprise database once again, as Business Intelligence continues to expand and developers lose interest in NoSQL.
The questions below were designed to help you practice for job interviews, whether they are technical tests or traditional face to face meetings. You’ll find individual questions, followed by several possible answers. Just click the “show answer” link to reveal the correct choice.
Anyone applying for a new role that requires experience in SQL 2016 should be aware that SQL 2016 comes with a large number of new features. These include Always Encrypted, Dynamic Data Masking, JSON Support, PolyBase, Query Store and many more. I have included questions on some of these topics to help you practice for the more challenging interview questions that may crop up.
Good luck and I hope you find the questions useful.
Interview Questions
1. A member of staff has requested that you install a SQL tool for data mining. They want to use the tool to identify rules and patterns in the company data and determine why things happen and predict what will happen in the future. Which installation component should you install to meet their requirements?
- Database Engine
- Analysis Services
- Reporting Server
- SQL Agent
Show AnswerAnswer:
Analysis Services
Comments:
Analysis Services (SSAS) provides a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining
2. Someone from your organisation is complaining that they can’t connect to the HR SQL Server using SQL Management Studio. However, they can open the Management Studio ok and other people can connect to the server fine. What might be the problem?
- SQL Client tools have not been installed on the users machine
- None of these
- The main server is probably down and needs rebooting
- The SQL Agent is not running
Show AnswerAnswer:
The SQL Agent is not running
Comments:
The SQL Agent must be running on the users machine to connect to an instance of SQL Server. This is a local service, which can be configured to start automatically. If it has stopped, just start the service from the Control Panel - Services console.
3. You want administer the SQL services that automatically start when a user starts their PC. SQL 2016 comes with a built in tool which allows you to changes services accounts and auto-start services. Which tool is this?
- SQL Server Surface Area Configuration
- SQL Management Studio
- Notification Services Command Prompt
- SQL Server Configuration Manager
Show AnswerAnswer:
SQL Server Configuration Manager
Comments:
The SQL Server Configuration Manager tool enables you to specify SQL Server Services options and whether the service starts automatically or manually. You can also stop and start services using this tool.
4. The tempbd plays an even more important role than in previous versions of SQL Server. This includes DBCC operations, event notifications, large object variables and parameters. Its important to ensure that certain settings are enabled for the tempdb database. Which two are likely settings that you should configure?
- Auto growth disabled, Full Recovery model selected
- Auto growth enabled, Simple Recovery model selected
- Auto growth enabled, Compatibility level set to SQL 7
- Auto growth enabled, Full Recovery model selected
Show AnswerAnswer:
Auto growth enabled, Simple Recovery model selected
Comments:
You also want to ensure that enough space has been preallocated to the tempdb to ensure that most operations are handled. You do this by setting the file size to an appropriate value. Autogrow should be enabled be default but autogrow operations are expensive and time consuming so its best to preallocate space
5. You just opened SSMS to connect to the company server to do a manual backup. Your connection request waits and then eventually times out. You cannot get connected no matter how hard you try. Your colleague thinks that the problem is limited resources on the server preventing any additional connections. If the name of the server is SQL1 how can you still connect to the server using SSMS?
- admin:SQL1
- SQL1:admin
- DAC:SQL1
- override:SQL1
Show AnswerAnswer:
admin:SQL1
Comments:
The Dedicated Administrator Connection (DAC) enables you to connect and do your work on almost all occasions. To connect to the DAC using SSMS, you add the admin prefix to the server name
6. You are worried that your SQL 2016 master database is corrupted. However, your manager was smart enough to keep a temporary configuration of the master database that is not corrupt. Which flags should you use to run SQLServr.exe to point it to an alternative master database?
- d and l
- e and m
- n and x
- f and h
Show AnswerAnswer:
d and l
Comments:
SQLServr.exe [-d master_file_path] [-l master_log_path]
7. A database administrator working at your company has defined a startup stored procedure that is causing a problem preventing SQL Server from starting. Not only is he in big trouble but you’ve got to find a way round this. Which flag should you use to start SQL 2016 in minimal mode?
Show AnswerAnswer:
f
Comments:
The f flag starts SQL Server 2016 in minimal mode (if used from the command prompt). This ensures that only one connection is allowed to the server, giving you a chance to fix any problems.
8. You want to modify the server settings without using the Server properties screen from the Management Studio. Which stored procedure allows you to change server settings?
- sp_bindrule
- sp_configure
- sp_autostats
- sp_addtype
Show AnswerAnswer:
sp_configure
Comments:
sp_configure displays or changes global configuration settings for the current server. When executed with no parameters, sp_configure returns a result set with five columns and orders the options alphabetically in ascending order, as shown in the following table
9. SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Which of the following is a component of SQL Server Agent?
- Jobs – work to be done
- Alerts – notification when event occurs
- Schedules – when job is executed
- All of these
Show AnswerAnswer:
All of these
Comments:
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task
10. SQL Server Agent can be used to run and schedule jobs that perform one or more tasks. How can you run a job using SQL Server Agent?
- Any of these options
- Attaching the job to a schedule
- In response to an alert
- By executing the sp_start_job stored procedure
Show AnswerAnswer:
Any of these options
11. SQL Server Agent is commonly used to schedule work to be done automatically, such as backing up a database. A SQL Server Agent job contains the definition of the work to be done. Which of the following is FALSE regarding SQL Server jobs?
- Each job has a name, description and an owner
- Each job can be in multiple categories
- Jobs can have one or more job steps
- Jobs can be ran manually using SQL Server Management Studio
Show AnswerAnswer:
Each job can be in multiple categories
Comments:
Each job can be in only one category
12. Your manager has asked you to create a new user called Report Manager. They will be responsible for running queries to gather information used for departmental reports. They only need access to the main company database called My Comp. Which database role should you give the person?
- db_datawriter
- db_datareader
- db_owner
- db_accessadmin
Show AnswerAnswer:
db_datareader
Comments:
Although you could give them db_owner or a combination of db_datareader and db_datawriter, this would be unwise because they are only reading data from the database for reporting purposes.
13. Which of the following is not a recommended optimization technique for SQL Server?
- Use a RAID 10 configuration on the server
- Store Data and Log files the same physical disks
- Use regular transaction log backups instead of full backups
- Use multi core processors instead of single core
Show AnswerAnswer:
Store Data and Log files the same physical disks
Comments:
Writing log files is a sequential process. Where as the data files are mostly random in nature. Using two separate physical disks help the log disk to track sequentially.
14. Which of the following is TRUE regarding the default SQL Server instance?
- Uses the same name as the computer name on which it is installed
- You cannot connect to the default instance
- Only one instance can exist on each SQL Server
- You cannot install a named instance without installing the default instance first
Show AnswerAnswer:
Uses the same name as the computer name on which it is installed
Comments:
Each instance of SQL Server consists of a distinct set of services that have specific settings for collations and other options The directory structure, registry structure, and service names all reflect the instance name and a specific instance ID created during SQL Server Setup. Only one installation of SQL Server, regardless of version, can be the default instance at one time.
15. What is the process of copying, at set intervals, a log backup from a read/write database (the primary database) on a primary server to one or more copies (the secondary databases) that reside on remote servers known as?
- Log shipping
- Linked server
- Linked Measure
- Dynamic Recovery
Show AnswerAnswer:
Log shipping
16. What is the name given to the system database that records all the system-level information for an instance of SQL Server.? This includes instance-wide metadata such as login accounts, endpoints, linked servers, and system configuration settings.
Show AnswerAnswer:
master
17. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.
Is the following true or false?
If a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
Show AnswerAnswer:
True
Comments:
The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE.
18. You want to shrink a database file because it has grown far too large. Your manager recommends using the T-SQL command called SHRINKDATABASE with 2 parameters (shown below). What unit is the second parameter?
DBCC SHRINKDATABASE (UserDB, 10);
- MB
- Percentage
- System code
- Threads to use
Show AnswerAnswer:
MB
Comments:
The second parameter sets the percentage of free space in the database after the shrink operation.
19. Sometimes SQL Server 2016 uses a special database to help it calculate complex result sets which may require filtering when no indexes are available. What is the name of this special table?
- tempdb
- model
- Resource
- master
Show AnswerAnswer:
tempdb
Comments:
Besides many other tasks, the tempdb database holds internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
20. At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database.
What is the recommend file extension for log files?
Show AnswerAnswer:
ldf
Comments:
The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.
21. You want to use a query to discover all of the database names from your server. Which of the following queries would achieve the desired result?
- SELECT name FROM sys.database_files
- None of these
- SELECT name FROM sys.master_files
- SELECT name FROM sys.databases
Show AnswerAnswer:
SELECT name FROM sys.databases
Comments:
Selecting from sys.databases will yield one row per database in the instance of Microsoft SQL Server or the Windows Azure SQL Database server.
22. You want to remove a log file called testfile1 from a database called Name1. Which query would you use to achieve this?
- ALTER DATABASE Name1 REMOVE FILEGROUP testfile1
- ALTER DATABASE Name1 REMOVE FILE testfile1
- ALTER DATABASE Name1 MODIFY FILE (NAME=testfile1, FILEGROWTH=0MB)
- ALTER DATABASE Name1 MODIFY FILE (NAME=testfile1, SIZE=0MB)
Show AnswerAnswer:
ALTER DATABASE Name1 REMOVE FILE testfile1
23. Can you combine rowstore and columnstore on the same table?
Show AnswerAnswer:
YES
Comments:
Beginning with SQL Server 2016, you can create an updatable nonclustered columnstore index on a rowstore table. The columnstore index stores a copy of the chosen columns so you do need extra space for this but it will be compressed on average by 10x. By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.
24. Which type of index would perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values.
Show AnswerAnswer:
Rowstore
Comments:
Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.
25. Which index type gives high performance gains for analytic queries that scan large amounts of data, especially on large tables?
Show AnswerAnswer:
Columnstore
Comments:
Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, since they tend to require full table scans rather than table seeks.
26. You are trying to create a System-Versioned Temporal table but it’s failing. The syntax is shown below, what might be wrong?
CREATE TABLE Department
(
DeptID int NOT NULL PRIMARY KEY CLUSTERED
, DeptName varchar(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID int NULL
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME ()
)
WITH (SYSTEM_VERSIONING = ON)
;
- SysStartTime,SysEndTime are missing inside the brackets for the PERIOD FOR SYSTEM_TIME
- SYSTEM_VERSIONING must be set to OFF
- The keyword TEMPORAL is missing in the CREATE TABLE statement
- datetime2 should be datetime
Show AnswerAnswer:
SysStartTime,SysEndTime are missing inside the brackets for the PERIOD FOR SYSTEM_TIME
Comments:
A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END
27. Creating a Temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. However, the history table CANNOT have which of the following?
- All of these
- Primary key
- Change tracking
- Triggers
Show AnswerAnswer:
All of these
Comments:
The history table cannot have a primary key, foreign keys, unique indexes, table constraints or triggers. It cannot be configured for change data capture, change tracking, transactional or merge replication.