SQL Server 2008 Interview Questions for Support Technicians
These interview questions cover the support side of SQL Server 2008. Including questions on various aspects of SQL Server such as the SQL Server agent, using SQL Server Management Studio, database roles, backups and general support.
We wrote these questions for other IT professionals looking to get some practice in before attending their next interview or sitting a technical test. They are suitable for consultants, temp staff or full time permanent roles.
If the job involves using SQL Server 2008 then these questions should help you out..
Interview Questions
1. 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 can be in multiple categories
Each job has a name, description and an owner
Jobs can be ran manually using SQL Server Management Studio
Jobs can have one or more job steps
Show AnswerAnswer:
Each job can be in multiple categories
Comments:
Each job can be in only one category
2. SQL Server Agent allows you to schedule jobs to perform any number of steps from backing up databases to running SQL scripts. Which of the following is the odd one out with regard to scheduling a job through SQL Server Agent?
Jobs can be scheduled on a recurring basis
Jobs can be run at random times
Jobs can be run when CPU utilization is idle
Jobs can be run once at a specific date and time
Show AnswerAnswer:
Jobs can be run at random times
Comments:
Jobs cannot be run on a random basis, you need to choose a specific trigger such as date and time, or CPU utilization.
3. When you schedule a SQL Server job you can configure which individuals are alerted about its completion. What is the correct term given to these people that can be alerted?
Alert Groups
Notifiers
Contacts
Operators
Show AnswerAnswer:
Operators
Comments:
An operator is a SQL Server Agent object that contains a friendly name and some contact information
4. A developer in your organisation has added a new table to the company database called PERSON. However, he has forgotten the table structure and his manager needs to recreate the table in his local database. What should you do to help?
Select the table in SQL Server Management studio and..
Select Tasks – Script Table as – Select to – File
Select Tasks – Script Table as – Create to – File
Select Tasks – Export Data
Select Tasks - Import Data
Show AnswerAnswer:
Select Tasks – Script Table as – Create to – File
Comments:
The Script table option will save table definition as a SQL script which the manager can rerun to create the table in their own database.
5. Stored procedures, triggers and functions can all be written in CLR compatible languages in SQL Server 2008. Why would someone want to use CLR store procedures rather than standard T-SQL stored procedures?
Type safety
They are faster
They require less development effort
T-SQL is outdated and CLR languages are a better choice
Show AnswerAnswer:
Type safety
Comments:
Using the CLR gives all the benefits of writing stored procedures with the benefit of using a type safe, structured framework that is independent of the actual language its written in. However, it is not true that it is any easier, faster or that T-SQL is out of date.
6. One of the developers at your company has written a CLT stored procedures that’s clever enough to call a web service and update database records based on local weather information. He has packaged and deployed this CLR stored procedure as .NET assembly. However, the code has a major bug and you have been asked to delete the assembly from SQL Server. Which command should you use?
DELETE ASSEMBLY
DROP ASSEMBLY
CLEANUP ASSEMBLY
REMOVE ASSEMBLY
Show AnswerAnswer:
DROP ASSEMBLY
Comments:
You should drop an assembly using the DROP ASSEMBLY statement. If the assembly is referenced by other objects, such as user-defined functions or stored procedures, then you can’t drop the assembly until you drop those dependent objects as well:
7. Your manager has asked you to create a new user on SQL Server 2008 that has a specific permission set. This person will only be responsible for restoring, dropping and sometimes creating new databases. Which server role should you give the person?
bulkadmin
dbcreator
processadmin
diskadmin
Show AnswerAnswer:
dbcreator
Comments:
The dbcreator role can create, alter, restore, and drop databases
8. 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_datareader
db_datawriter
db_accessadmin
db_owner
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.
9. Your manager is trying to create a new SQL login using a T-SQL script. This is the command that he is trying to run:
CREATE LOGIN users\temp1 FROM WINDOWS MUST CHANGE
However, the command fails when he tries to run it in SQL Management Studio. What is wrong with his SQL statement?
He needs to use WITH SUBJECT as well
He can’t use MUST CHANGE with a windows login
He needs to use CHECK POLICY = ON
He needs to use the HASHED argument for windows logins
Show AnswerAnswer:
He can’t use MUST CHANGE with a windows login
Comments:
The MUST CHANGE argument applies to SQL Server logins only. If this option is included, SQL Server prompts the user for a new password the first time the new login is used.
10. Which of the following is not a recommended optimization technique for SQL Server?
Use a RAID 10 configuration on the server
Use regular transaction log backups instead of full backups
Use multi core processors instead of single core
Store Data and Log files the same physical disks
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.
11. Your company has just launched a new in-house application for call monitoring. However, the database is performing poorly and you have been asked to investigate why. You have opted to use the Database Engine Tuning Advisor. What does this tool need to run?
Nothing, you just start the advisor during the peak usage period
The database must be offline and not in use
An xml configuration file of the database to analyse
A trace file to run against
Show AnswerAnswer:
A trace file to run against
Comments:
Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases
12. Which of the following is TRUE regarding the default SQL Server instance?
You cannot connect to the default instance
You cannot install a named instance without installing the default instance first
Only one instance can exist on each SQL Server
Uses the same name as the computer name on which it is installed
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.
13. The RESTORE DATABASE T-SQL command can be used to restore a database. You want to restore a database from a physical file on a removable media disk USB) called MyBackup.bak. Which restore parameters should you use?
FROM <logical name>
FROM DISK [Path to file]
STOPATMARK
WITH MOVE
Show AnswerAnswer:
FROM DISK [Path to file]
Comments:
Since the backup is a file and not a logical backup, we use the FROM DISK command
14. 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?
Linked server
Dynamic Recovery
Linked Measure
Log shipping
Show AnswerAnswer:
Log shipping
15. What is the name given to the column or combination of columns whose values match the primary key (PK) or unique key in the same or another table.
Foreign Key
Foreign Reference
Foreign Match
Primary Reference
Show AnswerAnswer:
Foreign Key
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.
default
master
model
tempdb
Show AnswerAnswer:
master
17. What is the name given to the process of replacing a table with multiple smaller tables. Each smaller table has the same format as the original table, but with a subset of the data. Each of these tables has rows allocated to it based on some characteristic of the data, such as specific key ranges.
Splitting
Partitioning
Dividers
Subsets
Show AnswerAnswer:
Partitioning