Question 11
You are finding unusually high memory utilization on a SQL Server that is primarily used to
process XML data. The server will periodically freeze up and refuse to process any more XML
statements. The problem can only be fixed by restarting the SQL Server service, at which point
it work fine for a while, but the problem always returns. What is the most likely cause for
the problem?
A) Your developers are probably not deallocating their cursors after processing the XML data.
B) The system is routinely encountering malformed XML and the accumulated errors are crashing the system.
C) The XML strings are not being removed after being prepared and used.
D) A hardware problem is the root of your trouble.
Question 12
What are two advantages of triggers over rules and constraints? Choose two.
A) The ability to define boundaries within which the data must reside.
B) The ability to use looping structures.
C) The ability to reference data in other databases.
D) The ability to check data before it is logged.
Question 13
Which of the following is a good reason to use transactional replication?
A) It provides an up-to-the-second backup in case one of the sites in the replication model goes down.
B) It provides a method for using a lot of resources on several servers in order to provide
up-to-the-second reporting capabilities.
C) It provides the lowest-overhead replication of any other type.
D) It is veryQuestion tolerant of network outages.
Question 14
When creating a table that contains a birth date for a person,
you want to ensure that a valid date is always entered. Which of the following is the best way to accomplish this?
A) Adding a CHECK constraint to the column
B) Adding a trigger to the table that will validate the column
C) Using a DateTime data type for the column.
D) Using a NOT NULL constraint on the column
Question 15
Which of the following EXEC statements are valid? Choose all that apply.
A) exec ReturnValue = MyProc 5, 4, 'hello', @A OUTPUT
B) exec @ReturnValue = MyProc @length = 5, 4, 'Hello', @A OUTPUT
C) exec @ReturnValue = MyProc 5, 4, 'Hello', @A OUTPUT
D) exec @ReturnValue = MyProc @length = 5, @width = 4, @message = 'Hello', @A = @A OUTPUT
Question 16
What tool included with SQL Server is primarily used for writing and executing ad-hoc queries?
A) SQL Server Enterprise Manager
B) SQL Server Profiler
C) SQL Server Query Analyzer
D) SQL Server Index Wizard
Question 17
What are the three join operations SQL Server supports? Choose all that apply.
A) Nested loops
B) Merge join
C) Index spool
D) Hash match
Question 18
The graphical execution plan presented by SQL Server Query Analyzer uses different icons to
represent which of the following?
A) Physical operators
B) Logical Operators
C) T-SQL commands
D) Process flow
Question 19
Phil has created a new database that takes advantage of multiple physical disks on his server.
He has created his primary data file on one disk and placed a secondary data file on a separate
physical disk. The transaction log exists on the same physical disk as the primary file.
Phil’s plan is to put all user objects on the secondary data file and leave the system tables
on the primary data file. After creating all his objects, however, he quickly discovers that
they ended up on the primary file group. What might he have done wrong?
A) All objects are created on the primary data file until there is no more room.
Only after the primary data file is full will objects be created on the secondary data file.
B) He probably forgot to create the secondary data file as part of a separate file group.
Objects are created on file groups rather than on data files.
C) He probably forgot to declare the secondary data file as the default data file.
Objects are created on the default data file unless another data file is specified.
D) Nothing is actually wrong. SQL Server automatically assigns the secondary data file as the
primary file group so that all user defined objects will be created separately from the
primary data file.
Question 20
Which of the following best describes a situation in which the statistics for a table would
be misleading?
A) Random rows in the table have been deleted.
B) Deleting all of the records of a specific gender.
C) Dropping Foreign Key constraints on the table
D) Dropping indexes on a table