Home | Interview Questions
Interview Questions | Windows 7, CRM, SharePoint, SQL Server, Windows Server, .NET..
Copyright © Accelerated Ideas 2017

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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?

  • m
  • d
  • h
  • f

Show Answer

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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 Answer

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.

  • master
  • default
  • tempdb
  • model

Show Answer

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.

  • True
  • False

Show Answer

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 Answer

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 Answer

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?

  • mdf
  • ldf
  • sql
  • log

Show Answer

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 Answer

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 Answer

23. Can you combine rowstore and columnstore on the same table?

  • NO
  • YES

Show Answer

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.

  • Columnstore
  • Rowstore

Show Answer

25. Which index type gives high performance gains for analytic queries that scan large amounts of data, especially on large tables?

  • Rowstore
  • Columnstore

Show Answer

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 Answer

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 Answer





Popular



Windows Server 2016
Amazon Web Services

Windows Server
Windows Server 2008
Windows Server 2012
Windows Server 2016

SQL Server
SQL Server 2008 (Administrator)
SQL Server 2008 (Support)
SQL Server 2016

Solution Architect
AWS

Windows Desktop
Windows 7
Windows 8

Development
Windows Commmunication Foundation
Windows Workflow Foundation

Disaster Recovery
Disaster Recovery (General)

Microsoft Office
Word 2010

Project Manager
Project Management (General)

SharePoint
SharePoint 2010





English English  |  Portuguese Portuguese