MS SQL Server 2014 Quiz– Temporary Tables, Functions, Bulk Operations & SQL Server Basics

1. What happens to local temporary tables when connection is closed? They are...

Transferred

Deleted

Converted to numbers

Change to rows

When a connection is closed, local temporary tables are deleted. This means that any data stored in these tables is removed and the tables themselves no longer exist. This is because local temporary tables are only accessible within the current session or connection, and once the connection is closed, they are no longer needed and are therefore deleted to free up resources.

Explanation

When a connection is closed, local temporary tables are deleted. This means that any data stored in these tables is removed and the tables themselves no longer exist. This is because local temporary tables are only accessible within the current session or connection, and once the connection is closed, they are no longer needed and are therefore deleted to free up resources.

2. Which of these tools can be used to copy a large amount of data in an SQL?

Copy

Large copy

Bulk copy

Cut

Bulk copy is the correct answer because it is a tool that can be used to copy a large amount of data in SQL. It is specifically designed for efficient and fast copying of data between SQL Server databases. The Bulk Copy Program (BCP) utility is commonly used to import or export large volumes of data in a tabular format. It provides high-performance data transfer and supports various data formats, making it an ideal choice for handling large data copying tasks in SQL.

Explanation

Bulk copy is the correct answer because it is a tool that can be used to copy a large amount of data in SQL. It is specifically designed for efficient and fast copying of data between SQL Server databases. The Bulk Copy Program (BCP) utility is commonly used to import or export large volumes of data in a tabular format. It provides high-performance data transfer and supports various data formats, making it an ideal choice for handling large data copying tasks in SQL.

3. Which of these cannot be checked by the sign function?

Positive

Negative

Zero

Alphabet

The sign function is a mathematical function that returns the sign of a number. It can determine whether a number is positive, negative, or zero. However, it cannot check whether a value is an alphabet or not, as alphabets are not numerical values. Therefore, the sign function cannot be used to determine whether a value is an alphabet or not.

Explanation

The sign function is a mathematical function that returns the sign of a number. It can determine whether a number is positive, negative, or zero. However, it cannot check whether a value is an alphabet or not, as alphabets are not numerical values. Therefore, the sign function cannot be used to determine whether a value is an alphabet or not.

4. What type of table is created by ## commands?

Global temporary table

Local temporary table

Column table

Row table

Global temporary tables are created using ## commands. These tables are accessible to all users and are stored in the tempdb database. They are created for a specific session or connection and are automatically dropped when the session or connection is closed. Global temporary tables are useful for storing temporary data that needs to be shared among multiple users or sessions.

Explanation

Global temporary tables are created using ## commands. These tables are accessible to all users and are stored in the tempdb database. They are created for a specific session or connection and are automatically dropped when the session or connection is closed. Global temporary tables are useful for storing temporary data that needs to be shared among multiple users or sessions.

5. Which of these can be applied to a column to limit the data that can be placed in it?

Server agent

Check constraint

Column sub query

Coalesce

A check constraint can be applied to a column to limit the data that can be placed in it. It is a rule defined on a single column or a group of columns that restricts the values that can be inserted or updated in the column(s). The check constraint ensures that only values satisfying the specified condition are allowed in the column, preventing any invalid or inappropriate data from being stored.

Explanation

A check constraint can be applied to a column to limit the data that can be placed in it. It is a rule defined on a single column or a group of columns that restricts the values that can be inserted or updated in the column(s). The check constraint ensures that only values satisfying the specified condition are allowed in the column, preventing any invalid or inappropriate data from being stored.

6. How many authentication modes are there in an SQL server?

7

6

2

3

There are two authentication modes in an SQL server. This means that users can either authenticate using Windows authentication mode or SQL Server authentication mode. Windows authentication mode allows users to log in using their Windows credentials, while SQL Server authentication mode requires users to provide a username and password specific to the SQL Server.

Explanation

There are two authentication modes in an SQL server. This means that users can either authenticate using Windows authentication mode or SQL Server authentication mode. Windows authentication mode allows users to log in using their Windows credentials, while SQL Server authentication mode requires users to provide a username and password specific to the SQL Server.

7. Which of these allows an admin to monitor events in an SQL server?

SQL profiler

Local table

Sub query

SQL server agent

SQL Profiler is a tool that allows an admin to monitor events in an SQL server. It provides a graphical interface to capture and analyze events that occur within the SQL server, such as queries, stored procedures, and database transactions. The admin can use SQL Profiler to track performance issues, identify and troubleshoot problems, and optimize the server's performance. It helps in understanding the behavior of the SQL server by capturing and analyzing the events in real-time or from saved trace files.

Explanation

SQL Profiler is a tool that allows an admin to monitor events in an SQL server. It provides a graphical interface to capture and analyze events that occur within the SQL server, such as queries, stored procedures, and database transactions. The admin can use SQL Profiler to track performance issues, identify and troubleshoot problems, and optimize the server's performance. It helps in understanding the behavior of the SQL server by capturing and analyzing the events in real-time or from saved trace files.

8. All of these are types of trigger except...

Update

Instead of

Insert

Pull

The given options - Update, Instead of, Insert, and Pull - are all related to triggers in databases. Update and Insert are common types of triggers used to perform actions before or after updating or inserting data into a table. Instead of triggers are used to replace the default behavior of a DML statement. However, Pull is not a type of trigger. It is likely an unrelated term or a distractor in this context.

Explanation

The given options - Update, Instead of, Insert, and Pull - are all related to triggers in databases. Update and Insert are common types of triggers used to perform actions before or after updating or inserting data into a table. Instead of triggers are used to replace the default behavior of a DML statement. However, Pull is not a type of trigger. It is likely an unrelated term or a distractor in this context.

9. What is the maximum level a recursive stored procedure can reach?

40

200

32

48

A recursive stored procedure is a procedure that calls itself repeatedly until a certain condition is met. The maximum level that a recursive stored procedure can reach refers to the maximum number of times the procedure can call itself before reaching a termination condition. In this case, the maximum level is 32, meaning that the procedure can call itself up to 32 times before it must stop.

Explanation

A recursive stored procedure is a procedure that calls itself repeatedly until a certain condition is met. The maximum level that a recursive stored procedure can reach refers to the maximum number of times the procedure can call itself before reaching a termination condition. In this case, the maximum level is 32, meaning that the procedure can call itself up to 32 times before it must stop.

10. Which of these is used to compare only strings with strings?

==

=

====

===

The === operator is used to compare only strings with strings. This operator compares both the value and the data type of the operands. It returns true if the operands are equal in both value and data type, and false otherwise. The other options, == and =, are used for general comparison and assignment respectively, and do not specifically compare only strings with strings. The option ==== does not exist in programming languages and is not a valid comparison operator.

Explanation

The === operator is used to compare only strings with strings. This operator compares both the value and the data type of the operands. It returns true if the operands are equal in both value and data type, and false otherwise. The other options, == and =, are used for general comparison and assignment respectively, and do not specifically compare only strings with strings. The option ==== does not exist in programming languages and is not a valid comparison operator.