MS SQL Server 2012 (Administrator) Assessment Test

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Cripstwick

Cripstwick

Community Contributor

Quizzes Created: 693 | Total Attempts: 703,847

Questions: 10 | Attempts: 256 | Updated: Mar 20, 2023

Questions

Settings

Create your own Quiz

MS SQL Server 2012 (Administrator) Assessment Test - Quiz

As an MS SQL Server database administrator, your role is to be efficient in the datacenter to the cloud, Platform as a Service, and Linux as you administer databases in SQL Server on a number of platforms.

Questions and Answers

1.

How can you trace traffic hitting an SQL server? It can be traced using
- A.
  
  SQL profiler
- B.
  
  SQL replication
- C.
  
  SQL agent
- D.
  
  Update statistics
Correct Answer
A. SQL profiler

Explanation
SQL profiler is a tool provided by Microsoft SQL Server that allows you to trace and monitor the activity on an SQL server. It captures and records SQL statements, stored procedures, and other database activities, providing valuable information for troubleshooting and performance tuning. With SQL profiler, you can trace the traffic hitting an SQL server by capturing events such as queries, stored procedure executions, and errors, helping you to analyze and optimize the performance of your database system.

Rate this question:
2.

Which of these is not a type of SQL Replication?
- A.
  
  Merge
- B.
  
  Snapshot
- C.
  
  Transaction
- D.
  
  Transformation
Correct Answer
D. Transformation

Explanation
The question asks for a type of SQL Replication that is not included in the given options. Among the options provided, Merge, Snapshot, and Transaction are all types of SQL Replication. However, Transformation is not a type of SQL Replication. Therefore, the correct answer is Transformation.

Rate this question:
3.

Which of these Replication uses a snapshot to seed the replication?
- A.
  
  Merge
- B.
  
  Snapshot
- C.
  
  Transaction
- D.
  
  Transformation
Correct Answer
A. Merge

Explanation
Merge replication uses a snapshot to seed the replication. This means that initially, a snapshot of the entire database is taken and then applied to the subscriber database. This snapshot includes all the data and schema objects that are required for replication. After the initial snapshot, any changes made to the publisher database are then replicated to the subscriber databases. Merge replication is commonly used in scenarios where multiple subscribers need to make changes to the data, and those changes need to be synchronized with the publisher and other subscribers.

Rate this question:
4.

How can you schedule jobs to run at set time or when a specific event occurs?
- A.
  
  By Using the SQL Agent
- B.
  
  By Using the SQL scheduler
- C.
  
  By Using checkpoints
- D.
  
  By calling update statistics
Correct Answer
A. By Using the SQL Agent

Explanation
The SQL Agent in SQL Server allows you to schedule jobs to run at specific times or in response to specific events. It provides a centralized tool for managing and automating various tasks such as backups, data imports, and report generation. By using the SQL Agent, you can define job schedules and specify the actions to be performed, ensuring that jobs are executed at the desired time or triggered by specific events.

Rate this question:
5.

What is a correlated sub-query?
- A.
  
  A nested subquery not linked with an outer query
- B.
  
  An outer query linked with a nested subquery
- C.
  
  A nested subquery linked to an outer query
- D.
  
  An outer query not linked to a nested subquery
Correct Answer
C. A nested subquery linked to an outer query

Explanation
A correlated subquery is a nested subquery that is linked to an outer query. In this type of subquery, the inner query references a column from the outer query, allowing the subquery to be executed multiple times, once for each row processed by the outer query. The result of the subquery is dependent on the values from the outer query, making it "correlated" to the outer query. This allows for more complex and specific filtering and retrieval of data.

Rate this question:
6.

SQL supports__ types of authentication modes
- A.
  
  2
- B.
  
  3
- C.
  
  4
- D.
  
  5
Correct Answer
A. 2

Explanation
SQL supports two types of authentication modes.

Rate this question:
7.

What type of subquery can you use to return multiple rows?
- A.
  
  Multiple rows
- B.
  
  Multiple columns
- C.
  
  Single
- D.
  
  Double rows
Correct Answer
A. Multiple rows

Explanation
A subquery that can be used to return multiple rows is known as a correlated subquery. In a correlated subquery, the inner query depends on the outer query for its execution. This allows the subquery to be executed multiple times, once for each row returned by the outer query. As a result, the subquery can return multiple rows of data.

Rate this question:
8.

Which of these functions is function is used to round up a non-integer value to the previous least integer?
- A.
  
  UNION ALL
- B.
  
  FLOOR
- C.
  
  IDENTITY
- D.
  
  UNION
Correct Answer
B. FLOOR

Explanation
The FLOOR function is used to round up a non-integer value to the previous least integer. It returns the largest integer that is less than or equal to the specified value.

Rate this question:
9.

Which of these is not a type of trigger?
- A.
  
  Error
- B.
  
  Insert
- C.
  
  Delete
- D.
  
  Update
Correct Answer
A. Error
10.

What do we use collation for?
- A.
  
  To change a table
- B.
  
  To specify order in a table
- C.
  
  To collect data from a table
- D.
  
  To create rows in a table
Correct Answer
B. To specify order in a table

Explanation
Collation is used to specify the order in which data is sorted and compared in a table. It determines how string values are compared, taking into account factors such as character set, case sensitivity, and accent sensitivity. By specifying the collation for a table, we can control the sorting order of the data, ensuring that it is displayed in a specific way.

Rate this question: