Question 31
Which of the following types of columns would be good candidates for an index? Choose all that apply.
A) Columns that make up the Primary Key
B) Columns that contain Foreign Keys
C) Columns that contain data such as gender for a large population.
D) Columns that contain data such as a tax ID or address for a large population
Question 32
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 33
An UPDATE trigger typically accesses which of the following?
A) The updated table
B) The inserted table only
C) The INSERTED and DELETED tables
D) The DELETED table only
Question 34
Which of the following commands can best assist in diagnosing locking problems?
A) sp_lock
B) sp_who
C) DBCC SHOWLOCKS
D) SET SHOWLOCKS
Question 35
Which of the following commands can be used to interrupt processing of a While loop?
A) BREAK
B) HALT
C) EXIT
D) CONTINUE
Question 36
Choose all the statements that you could use in the WHERE clause to find only the rows
where the first name is Bobby or Bobbi. Choose all that apply.
A) WHERE name = ‘Bobby’ or name = ‘Bobbi’
B) WHERE name LIKE ‘Bobb_’
C) WHERE name LIKE ‘Bobb%’
D) WHERE name LIKE ‘Bobb[iy]’
Question 37
What are the space requirements for views?
A) Views require as much space as all the tables they are created from.
B) Views require as much space as would a table that presented the same data.
C) Views do not store data and therefore use no space.
D) Views require the space used to store a few records in various system tables.
Question 38
You have written a stored procedure that takes several parameters and uses them to issue
appropriate queries to the database. Most of the parameters are optional, and if all are passed
the resulting query you have to run can be quite complex. There is a great deal of conditional
logic in the procedure based on the parameters passed. When testing this procedure you found
that when you first created it, it worked great even though you started with a rather complicated
set of parameters. Since then performance has been disappointing. Every time you re-create it,
it works fast once, but rarely shows such speed on later calls. What is the most likely cause
of the problem?
A) You have specified the With Encryption option, which is causing additional overhead because the procedure has to be decrypted each time it is run.
B) You have specified the With Recompile option, which is causing additional overhead because the procedure has to recompile a new query plan every time.
C) You have forgotten to specify the With Recompile option, which is causing an inappropriate query plan to be used with subsequent runs.
D) Your stored procedure cannot be helped. Stored procedures weren’t intended to handle complex conditional logic.
Question 39
How does a TRUNCATE TABLE statement differ from an unrestricted DELETE? Select all that apply.
A) A TRUNCATE TABLE cannot be rolled back, even if it occurs within a transaction.
B) An unrestricted DELETE resets an Identity column to its original seed, whereas the TRUNCATE TABLE does not.
C) If the table is referenced by a FOREIGN KEY constraint, only the DELETE may be used.
D) The TRUNCATE TABLE causes a DELETE trigger to be fired, whereas the DELETE does not.
Question 40
What is a valid GROUP BY clause for the following SELECT list? SELECT Pub_ID, Type, Max(Price)
A) GROUP BY Pub_ID, Type, Max(Price)
B) GROUP BY Pub_ID, Type, Price
C) GROUP BY Pub_ID, Type
D) GROUP BY max(Price)