Question 51
What is the lifetime of a user-defined variable?
A) Duration of the script
B) Duration of the statement block
C) Duration of the batch
D) Duration of the current SQL Server connection
Question 52
To use full-text searching and find all rows containing forms of the verb "swim,"
you should use which CONTAINS predicate?
A) CONTAINS(columname, FORMSOF(INFLECTIONAL, 'Swim'))
B) CONTAINS(columnname, 'swim', 'swims')
C) CONTAINS(columnname, FINDALL("Swims"))
D) CONTAINS(columnname, 'swim*')
Question 53
Which of the following will help reduce deadlocks in your database?
A) Keeping the transaction isolation level as high as possible without hurting user concurrency
B) Always accessing tables in the same order in all programs
C) Never completing transactions until a user can confirm that the changes are correct
D) Always checking the state of @@TRANCOUNT before issuing a ROLLBACK command
Question 54
Sarah needs to figure out how to replicate data from all of her warehouses back to the
corporate office. She wants to store all the inventory information in one table, and all of
the information needs to be accessible to all of the warehouses and the corporate office, and
the information should be updateable at any location. The warehouses do not have reliable
network connections back to the corporate office. Which type of replication should Sarah use?
A) Snapshot replication
B) Merge replication
C) Transactional replication
D) Snapshot replication with Updating Subscribers
Question 55
When creating a database with the CREATE DATABASE command, in what units can the file size be
specified? Choose all that apply.
A) MB (Megabytes)
B) Number of pages
C) Number of extents
D) KB (Kilobytes)
Question 56
Eddie needs to have a database that is replicated to other locations. They have highly
redundant network connectivity to all of the other locations, and a very large central server.
Changes to the database need to be made at every site, and need to be done instantaneously at
every site, with no delay. Which type of replication would work best for Eddie?
A) Merge replication
B) Transactional replication
C) Snapshot replication
D) Snapshot replication with updates
Question 57
Which of the following are aggregate functions? Choose 3.
A) Sum
B) ABS
C) Max
D) Count
Question 58
Barney wrote this stored procedure to manage inserts to his Sales table: CREATE PROCEDURE
InsertSale @SalesPersonID int, @ProductID int, @Quantity int, @CustomerID int AS
If @Quantity > 0 Insert Sales (SalesPersonID, ProductID, Quantity, CustomerID) VALUES
(@SalesPersonID, @ProductID, @Quantity, @CustomerID) else RAISERROR
('Unable to enter negative or 0 quantity into Sales table.', 16, 1) GO
If there are no constraints on the table, how can Barney make sure that the value of
@SalesPersonID is valid?
A) Check the Sales table and see whether there are any other rows in that table for that SalesPersonID.
B) Check the table in which SalesPersonID is a Primary Key to ensure that the value is valid.
C) Attempt the insert and check to see whether the foreign key constraint causes an error.
D) It's not possible to access more than one table in a stored procedure.
Question 59
Which queries will return all the names in the Authors table sorted by first name, then last? Choose 2.
A) SELECT au_fname, au_lname FROM Authors SORT BY 1, 2
B) SELECT au_fname, au_lname FROM Authors ORDER BY au_fname, au_lname
C) SELECT au_fname, au_lname FROM Authors SORT BY au_fname, au_lname
D) SELECT au_fname, au_lname FROM Authors ORDER BY 1, 2
Question 60
When using an INSERT statement, data to be inserted may come from which of the following?
Select all that apply.
A) The VALUES clause
B) A stored procedure
C) Another database
D) A SELECT statement