Question 21
Which of the following are results of specifying schema binding on a view?
Choose all that apply.
A) The view created with schema binding cannot be dropped.
B) The CREATE statement for the view must contain a two-part name for all objects referenced.
C) When created with schema binding, the CREATE statement is stored in an encrypted manner and cannot be scripted out.
D) Objects referenced by the view cannot be dropped or changed while the view is schema bound.
Question 22
George needs to write a view that can return one or more customers from a table given a last
name or part of a last name. Which of the following strategies will allow him to do that?
A) Write a view for every single last name that can be entered.
B) Write an application that automatically creates a new view for each last name in the database.
C) Pass a lastname parameter into the view.
D) Write a SELECT statement that references the view. Use the WHERE clause in the SELECT statement to filter the data.
Question 23
Which of the following are parts of a SELECT statement? Choose all that apply.
A) FROM
B) SORT BY
C) WHERE
D) SELECT
Question 24
Which of the following features are new to SQL Server 2000? Choose all that apply.
A) Full Text Searching
B) Indexed views
C) Cascading Referential Integrity Constraints
D) Remote administration of servers
Question 25
When is it important to use the WITH RECOMPILE option when creating a stored procedure?
A) Always use WITH RECOMPILE.
B) On servers that aren't busy, WITH RECOMPILE should be used all the time.
C) Only if the query plans used by the stored procedure change frequently.
D) When any tables that are going to be used by the stored procedure are routinely dropped and re-created.
Question 26
Given the following partial function definition, which SET statement shown below would correctly accept the returned value? create function Area (l1 int, l2 int) returns int
A) set @A = dbo.area(5, 4)
B) set @A = area(5,4)
C) exec area(5, 4, @A OUTPUT)
D) set @A = dbo.@Area(5, 4)
Question 27
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 RAISERROR statement in the trigger do?
A) It provides the connection with an error message.
B) It provides the connection and the SQL Server error log with an error message.
C) It provides the connection, the SQL Server error log file, and the Windows NT application event log with an error message.
D) It causes a server-wise severity one message, stopping SQL Server.
Question 28
Diane needs to enforce referential integrity on one of her tables. The data in one of the
columns should contain key values from a table residing in a database on the corporate server,
as opposed to her departmental server where her application database resides. She decides to
use constraints to enforce data integrity. How would you rate this solution?
A) This is an optimal solution. Using constraints to validate data entry is the best way to verify referential integrity.
B) This is a fair solution. Although using constraints will work, it would be better to use triggers to accomplish this goal.
C) This is a poor solution. It won't perform well, but it will work.
D) This solution won't work.
Question 29
Choose the statement that best describes a composite index.
A) It physically sorts the rows in a table.
B) It contains multiple keys.
C) It logically sorts the rows in a table in both ascending and descending order.
D) It contains multiple columns in its key.
Question 30
You need to install SQL Server Developer Edition on a spare machine to deliver to a new developer in your company so he can study for his certification exam. Of the following machines that can be spared,
what is the least powerful machine you can use for this purpose?
A) Pentium 266MHz, 32MB memory
B) Pentium 266MHz, 64MB memory
C) Pentium 166MHz, 64MB memory
D) Pentium 90MHz, 32MB Memory