Question 1

Which of the following is true about indexes in SQL Server?

Accepted Answer

SQL Server automatically creates indexes for primary keys but not for foreign keys. However, it is a common practice to manually create indexes on foreign keys to improve query performance.

Question 2

SQL Server system functions start with the symbol ________.

Accepted Answer

SQL Server system functions start with the symbol @@.

Question 3

Which type of index in SQL Server is faster for data retrieval?

Accepted Answer

A clustered index physically reorders the rows in a table based on the index columns, making data retrieval faster because the data is stored in a sorted order. Nonclustered indexes are still useful for improving performance, but they require an additional lookup to the clustered index or table data to retrieve the actual values. Unique and baseline indexes do not necessarily provide any advantage for data retrieval.

Question 4

Which of the following is not a type of trigger supported by SQL Server?

Accepted Answer

CONCURRENT is not a type of trigger supported by SQL Server.

Question 5

A view may not be assigned to a(n)________.

Accepted Answer

The answer of A view may not be assigned to...

Question 6

When creating SQL Server tables in a database,SQL statements can now also be submitted by using ________.

Accepted Answer

Microsoft PowerShell is a task automation and configuration management framework that allows users to execute SQL statements and automate SQL Server tasks. It offers more flexibility and customization than using a GUI tool such as Microsoft SQL Server Management Studio.

Question 7

What name is so special to SQL Server that no stored procedure will work on a table with that name,not even if it is enclosed in brackets?

Accepted Answer

The answer of What name is so special to SQL...

Question 8

When reviewing a table in the SQL Server GUI tools,which property should be set to "Yes" to indicate that the column is a surrogate key for which SQL Server should automatically supply values?

Accepted Answer

The property that should be set to "Yes" to indicate that the column is a surrogate key for which SQL Server should automatically supply values is Identity. This property will automatically generate unique, sequential values for the column as new rows are inserted into the table. The other options listed (Surrogate, AutoIncrement, AutoNumber, and Sequence) either do not apply to SQL Server or are not the correct property to use for automatically generating surrogate key values.

Question 9

Which type(s)of index in SQL Server has/have a bottom level that does not contain data but has pointers to the data?

Accepted Answer

Nonclustered indexes have a bottom level that contains pointers to the actual data rows, rather than the data itself.

Question 10

For triggers on insert and update actions,the new column values are stored in ________.

Accepted Answer

For triggers on insert and update actions, the new column values are stored in a pseudotable named inserted. The pseudotable inserted contains the new column values for the corresponding insert or update action.

Question 11

Which of the following is known to be true if the command EXEC abcCust @Cost = 5 is used to invoke a stored procedure in the Microsoft SQL Server Management Studio?

Accepted Answer

The command EXEC abcCust @Cost = 5 is used to invoke a stored procedure named abcCust with a parameter @Cost that is set to 5. Thus, option B is the correct choice. Option A is incorrect because it refers to a CHECK constraint, which is unrelated to the stored procedure being invoked. Option C is incorrect because it suggests that the name of the stored procedure is only abc, which is not the case. Option D is incorrect because it suggests that Cust is a parameter, which is not the case. Option E is incorrect because it refers to a table or view, which is unrelated to the stored procedure being invoked.

Question 12

Which of the following is true of clustered indexes?

Accepted Answer

A clustered index determines the physical order of data in a table, and only one clustered index is allowed per table.

Question 13

The language available in SQL Server that adds programming constructs to the SQL language is known as ________.

Accepted Answer

The language available in SQL Server that adds programming constructs to the SQL language is known as Transact-SQL (T-SQL). PL/SQL is used in Oracle databases, SQLP and INTERACT-SQL are not valid languages, and C++ is a general-purpose programming language that is not specific to SQL.

Question 14

Using only SQL Server tools,you can enter data into a table by ________.

Accepted Answer

Both options A and B are valid ways to enter data into a table using SQL Server Management Studio. Option C is not a standard SQL Server tool and is not a valid way to enter data into a table. Therefore, the correct answer is D, which includes both valid options.

Question 15

When creating a T-SQL statement,you should:

Accepted Answer

It is important to end T-SQL statements with a semicolon as it is the standard way to terminate SQL statements and helps improve readability and maintainability. Predefining the statement graphically in Enterprise Manager or including the word "View" in the view name are not necessary for creating a T-SQL statement. Limiting the statement to one or two tables may depend on the specific query and its purpose.

Question 16

Which of the following are ways of processing an SQL Server database?

Accepted Answer

All of the options listed are ways of processing an SQL Server database. Applications written in programming languages such as C# or Visual Basic can connect to a SQL Server database using ADO.NET or other data access frameworks. The SQL Server Query Analyzer can be used to execute T-SQL commands or scripts stored in text files. SQL Server stored procedures can be invoked to perform specific tasks within the database, and SQL Server triggers can be set up to automatically execute in response to certain database events.

Question 17

SQL Server user variables and parameters start with the symbol ________.

Accepted Answer

SQL Server user variables and parameters start with the symbol "@"

Question 18

A stored procedure can be stored in a text file and run with the Microsoft SQL Server Management Studio.If we are running the code that creates this procedure for the third time,it must start with ________.

Accepted Answer

If we are running the code that creates this procedure for the third time, we need to modify the existing procedure. In SQL Server, we can modify an existing stored procedure using the ALTER PROCEDURE statement. Using the CREATE PROCEDURE statement again will result in an error saying the stored procedure already exists. Therefore, the correct choice is C) ALTER PROCEDURE.

Question 19

The column(s)that is/are the primary key in an SQL table are indicated by ________ when viewed graphically in Enterprise Manager.

Accepted Answer

The key symbol typically indicates the primary key(s) in an SQL table when viewed graphically in Enterprise Manager.

Question 20

Anytime you want to use an SQL Server reserved word as a user identifier,enclose it in ________.

Accepted Answer

SQL Server uses square brackets [] to escape reserved words or special characters in user identifiers. This allows the user to use reserved keywords without causing syntax errors in SQL statements.

Quiz 10: A: Managing Databases With SQL Server 20012