Q&A Hero

Q: Which of the following is a type of cursor concurrency that causes SQL Server to place an update lock on a row when the row is read? A) SCROLL_LOCK B) Optimistic C) Serializable D) Read only

Q: Which of the following is the most restrictive transaction isolation level option supported by SQL Server? A) Repeatable Read B) Read Committed C) Serializable D) Read Uncommitted

Q: Which of the following is the default transaction isolation level for SQL Server? A) Repeatable Read B) Read Committed C) Serializable D) Read Uncommitted

Q: Which of the following is not a transaction isolation level supported by SQL Server? A) Pessimistic B) Read Committed C) Serializable D) Read Uncommitted

Q: Which of the following is not a lock granularity supported by SQL Server? A) Row B) Attribute C) Table D) Page

Q: For triggers on insert and update actions, the new column values are stored in ________. A) a table named new:TableName B) a table named old:TableName C) a pseudotable name inserted D) a pseudotable named deleted

Q: SQL Server system function names start with the symbol ________. A) @@ B) # C) ## D) @

Q: A view may not be assigned to a(n) ________. A) BEFORE trigger B) INSTEAD OF trigger C) CONCURRENT trigger D) AFTER trigger

Q: Which of the following is not a type of trigger supported by SQL Server? A) BEFORE B) INSTEAD OF C) CONCURRENT D) Both A and C are correct

Q: SQL Server user variables and parameters start with the symbol ________. A) @@ B) # C) ## D) @

Q: 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 ________. A) RUN PROCEDURE B) CREATE PROCEDURE C) ALTER PROCEDURE D) MODIFY PROCEDURE

Q: Which of the following is known to be true if the command EXECabcCust @Cost = 5 is used in SQL to invoke a stored procedure in Microsoft SQL Server? A) The name of the stored procedure is abcCust. B) The name of the stored procedure is abc. C) The parameter Cost is given a value of 5. D) Both A and C are correct.

Q: The language available in SQL Server that adds programming constructs to the SQL language is known as ________. A) PL/SQL B) C++ C) INTERACT-SQL D) TRANSACT-SQL

Q: Which of the following is true about the SQL Server Import and Export Wizard? A) The spreadsheet data must already be normalized in order to be imported. B) There may be problems importing Excel 2013 data and we may need to install Excel 2007 compatibility software. C) The Import and Export Wizard sometimes creates blank rows at the end of the imported table. D) Both B and C are correct.

Q: Which type of index in SQL Server is faster for data retrieval? A) Clustered B) Nonclustered C) Baseline D) There is no advantage to any particular index for data retrieval, only for data updating.

Q: Which of the following is true of clustered indexes? A) They are normally slower than nonclustered indexes for retrieving data. B) Only one is allowed per table. C) They are normally slower than nonclustered indexes for updating data. D) They do not have data in the bottom level.

Q: Which type(s) of index in SQL Server has/have a bottom level that does not contain data but has pointers to the data? A) Clustered B) Nonclustered C) Unique D) Both A and B are correct

Q: Which of the following is true about indexes in SQL Server? A) SQL Server automatically creates indexes for columns appearing in WHERE clauses in queries. B) SQL Server pads all indexes. C) SQL Server supports filling up to a limit of 70 percent. D) SQL Server automatically creates indexes for foreign keys.

Q: When creating a T-SQL statement, you should ________. A) predefine it graphically in Enterprise Manager B) limit it to one table C) limit it to two tables D) end it with the semicolon normally used to terminate SQL statements

Q: Using only SQL Server tools, you can enter data into a table by ________. A) entering it into a table grid in the Microsoft SQL Server Management Studio B) using INSERT statements through the Microsoft SQL Server Management Studio C) using an SQL Server form D) Both A and B are correct

Q: 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? A) Identity B) Surrogate C) AutoIncrement D) AutoNumber

Q: 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? A) NAME B) UPDATE C) TRANSACTION D) KEY

Q: The column(s) that is/are the primary key in an SQL table are indicated by ________ when viewed graphically in Enterprise Manager. A) the black arrow symbol B) the outline arrow symbol C) the key symbol D) being underlined

Q: Anytime you want to use an SQL Server reserved word as a user identifier, enclose it in ________. A) { } B) [ ] C) ( ) D) < >

Q: When creating SQL Server tables in a database, SQL statements can now also be submitted by using ________. A) Internet Explorer B) Microsoft PowerShell C) Microsoft Excel D) PL/SQL

Q: SQL Server tables can be created and modified using ________. A) Enterprise Manager B) Query Analyzer C) Microsoft SQL Server Management Studio D) Both A and B are correct

Q: SQL statements can be passed to SQL Server using the ________. A) Enterprise Manager B) Query Analyzer C) Microsoft SQL Server Management Studio D) Both A and B are correct

Q: The SQL Server default when creating a new database is to create ________ for each database. A) one data file and one log file B) one data file and two log files C) two data files and one log file D) There is no defaultthe database creator must specify how many data files and log files will be created.

Q: Which of the following is true about SQL Server Import and Export Wizard? A) SQL Server Native Client should be selected as the destination. B) The user can't specify that the column names are in the first row. C) There are no problems with modifying the default column attributes. D) The user can specify the primary key to be set on the imported table.

Q: The SQL Server GUI interface database management program is the ________. A) Enterprise Manager B) Query Analyzer C) Microsoft SQL Server Management Studio D) Both A and B are correct

Q: In SQL Server, the full recovery model creates a log entry for every change to the database.

Q: SQL Server bulk-logged recovery differs from other log-based recovery methods because it does not include log entries for changes that cause large log entries.

Q: SQL Server supports three recovery models: simple, complex and bulk logged.

Q: In SQL Server, a transaction log backup makes a copy of the changes that have been made since the last time a backup of the entire database was made.

Q: In SQL Server, a differential backup makes a copy of the entire database.

Q: When recovering a database in SQL Server, it is possible to process the log to a particular point in time or to a transaction mark.

Q: SQL Server security roles provide a simple way to control user privileges in a database.

Q: The recommended SQL Server security is Windows-only security.

Q: SQL Server logins can be based on Windows Operating System security or SQL server-specific security.

Q: In SQL Server, locking behavior can be modified by providing locking hints in the WITH parameter of the FROM clause in SELECT statements.

Q: Locking hints included in an SQL statement will override locking behavior based on explicit transaction isolation level statements.

Q: In SQL Server, SCROLL_LOCK is a version of pessimistic locking.

Q: The default transaction isolation level for SQL Server is Read Committed.

Q: The default cursor concurrency for a dynamic cursor in SQL Server is optimistic.

Q: In SQL Server, it is not possible to make dirty reads.

Q: SQL Server automatically makes determinations of whether or not to promote or demote a lock.

Q: When using SQL Server, developers place explicit locks.

Q: SQL Server built-in system function names start with the @@ symbol.

Q: In SQL Server, insert and update triggers store old values in a pseudotable named deleted.

Q: In SQL Server, insert and update triggers store new values in a pseudotable named inserted.

Q: In SQL Server, triggers can roll back the transaction that caused them to be fired.

Q: SQL Server AFTER triggers may be used with insert and update actions, but not delete actions.

Q: SQL Server AFTER triggers may be assigned to either tables or views.

Q: SQL Server supports AFTER and INSTEAD OF triggers, but not BEFORE triggers.

Q: When you use the Microsoft SQL Server Management Studio to run and rerun a set of SQL Server DBMS commands stored in a text file, the stored procedure must start with CREATE PROCEDURE.

Q: SQL Server user variables and parameters start with the @ symbol.

Q: PL/SQL is a programming language for SQL Server that adds programming constructs to the SQL language.

Q: One means of processing an SQL Server database is to save groups of SQL Server DBMS commands in a text file and then process this file in the Microsoft SQL Server Management Studio.

Q: One means of processing an SQL Server database is to create application code in a programming language and invoke SQL Server DBMS commands from that program.

Q: Database views can only be created in SQL Server through the use of SQL commands.

Q: Data can be entered directly into a table in Enterprise Manager or via SQL INSERT statements submitted through the Microsoft SQL Server Management Studio.

Q: With SQL Server, the clustered index is faster for data retrieval.

Q: With SQL Server, a nonclustered index does not contain data but has pointers to the data.

Q: With SQL Server, only one clustered index is allowed per table.

Q: With SQL Server, a clustered index has the data with, and in the same order as, the bottom level of the index.

Q: In an SQL Server clustered index, the data are stored in the bottom level of the index and in the same order as the index.

Q: SQL Server supports three types of indexes: clustered, unclustered, and random.

Q: The developer can direct SQL Server to create indexes on non-key fields.

Q: SQL Server automatically creates an index on primary key fields only.

Q: When viewing a table in the GUI tools table design window in SQL Server, the notation that should supply the values for a surrogate key is that the Identity property of that column is set to "Yes."

Q: When viewing a table in the GUI tools table design window in SQL Server, the primary key is indicated by a black arrow.

Q: In the Microsoft SQL Server Management Studio, the amount to add to the surrogate key value when adding a new row is specified by the increment property.

Q: In the Microsoft SQL Server Management Studio, the starting value for a field that has been set as an IDENTITY in SQL Server is set in the seed (StartingValue) property.

Q: The name TRANSACTION is so special to SQL Server that no stored procedures will work on a table with that name.

Q: Stored procedures and triggers in SQL Server may become confused while executing stored procedures and triggers when SQL Server special words such as TRANSACTION appear as table or other names, even if they are enclosed in square brackets [ ].

Q: The Microsoft SQL Server Management Studio cannot verify SQL statements until they are executed.

Q: To use an SQL Server reserved word as a user identifier, it must be enclosed in square brackets [ ].

Q: SQL Statements can be submitted to SQL Server from the Microsoft SQL Server Management Studio.

Q: With SQL Server, the preferred way to create database structures is with SQL statements.

Q: SQL Server 2014 provides two ways to create tablesgraphically and through SQL code.