MS SQL Server 2012 (Administrator) Assessment Test

1. Which of these is not a type of trigger?

Error

Insert

Delete

Update

not-available-via-ai

Explanation

not-available-via-ai

2. SQL supports__ types of authentication modes

2

3

4

5

SQL supports two types of authentication modes.

Explanation

SQL supports two types of authentication modes.

3. What type of subquery can you use to return multiple rows?

Multiple rows

Multiple columns

Single

Double rows

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.

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.

4. How can you trace traffic hitting an SQL server? It can be traced using

SQL profiler

SQL replication

SQL agent

Update statistics

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.

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.

5. How can you schedule jobs to run at set time or when a specific event occurs?

By Using the SQL Agent

By Using the SQL scheduler

By Using checkpoints

By calling update statistics

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.

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.

6. Which of these functions is function is used to round up a non-integer value to the previous least integer?

UNION ALL

FLOOR

IDENTITY

UNION

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.

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.

7. Which of these is not a type of SQL Replication?

Merge

Snapshot

Transaction

Transformation

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.

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.

8. What is a correlated sub-query?

A nested subquery not linked with an outer query

An outer query linked with a nested subquery

A nested subquery linked to an outer query

An outer query not linked to a nested subquery

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.

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.

9. What do we use collation for?

To change a table

To specify order in a table

To collect data from a table

To create rows in a table

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.

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.

10. Which of these Replication uses a snapshot to seed the replication?

Merge

Snapshot

Transaction

Transformation

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.

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.