Question 1
What are the maximum number of tables that can be joined together in a query?
A) 16
B) 32
C) 256
D) 1024
Question 2
When using BCP to copy data into a table, which of the following statements apply? Choose 2.
A) Database users will not be able to access the table because BCP will lock it.
B) Database users will see the rows inserted by BCP after each batch is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.
Question 3
An UPDATE statement may modify how many tables?
A) One or more tables, provided the UPDATE statement is not operating on a view.
B) Only one, though triggers may cause additional side-effects.
C) Only one.
D) Up to sixteen
Question 4
Data and log files expand by what increment?
A) 8KB
B) 64KB
C) 512KB
D) 1MB
Question 5
Jamie is having a problem tracking down an issue users are having with a stored procedure.
Users will occasionally have problems with the stored procedure running for an excessive
amount of time, perhaps hours on end. There are no messages in the SQL Server error log that
correspond to the times that users are having problems. Which of the following is the most
likely cause and resolution?
A) The server is crashing and the users aren't able to access their data. Replace the hardware.
B) The server is processing a database backup, and which is causing an excessive
amount of locking. Stop the automated backup jobs.
C) There is a problem with one or more user connections causing lock contention.
Use sp_who and DBCC INPUTBUFFER to track down the likely culprit.
D) There is a problem with one or more user connections causing lock contention.
Use sp_lock to track down the
Question 6
Which of the following commands are parts of the Data Definition Language (DDL)?
A) INSERT
B) CREATE TABLE
C) UPDATE
D) CREATE VIEW
Question 7
Consider the following table and trigger definitions: Table1: UserID int IDENTITY FirstName
char(50) LastName char(50) DepartmentID int BillingID int CREATE TRIGGER Table1_InsertUpdate ON
Table1 FOR INSERT, UPDATE AS BEGIN IF UPDATE(DepartmentID) IF (Select count(*) from Table1
inner join Deleted on Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END What does the
ROLLBACK statement in the trigger do?
A) It causes the transaction to roll back, aborting the INSERT or UPDATE that cause the trigger to run,
but not aborting the transaction that the offending statement was part of.
B) It causes the transaction containing the offending statement to roll back.
C) Nothing; the keyword EXIT should have been used to abort the transaction.
D) It causes the transaction log to fill up.
Question 8
In order for her new application to process a sales record, Kim has to make sure that the
application has collected enough information. For sales over $500, she needs to have a valid
account manager's ID number. For sales over $1000, she needs to have a valid zip code for
delivery to determine who gets commission. For sales over $10,000, she needs to make sure that
enough stock is available. Which of the following would be a good way to check the data prior
to inserting it?
A) Table constraints
B) Triggers
C) An inline table-valued function
D) A multi-statement table-valued function
Question 9
What is the maximum number of rows that a table can store in SQL Server 2000?
A) 255
B) 1,048,516
C) 2,147,483,647
D) No limit besides physical storage size
Question 10
If table_a has an insert trigger that performs an update on table_b, in which case will the
update trigger on table_b be fired?
A) If the 'nested triggers' database option has a value of '1'.
B) If the 'nested triggers' server option is set to '1'.
C) If the 'nestable' option is set for the trigger on table_a.
D) If the 'nestable' option is set for the trigger on table_b.