- Wayne has a database that he is replicating to fifteen sites around the world. Unfortunately, it's not very reliable because of replication conflicts. What changes can Wayne make to his data model to help alleviate replication conflicts?
   Use horizontal partitioning and add a location identifier to each table that changes to ensure that each site updates only a specific subset of records.

- What are the benefits of a normalized model?

Choose 3   Redundant data is minimized.

- What are the benefits of a normalized model?

Choose 3   Storage space is used more efficiently.

- What are the benefits of a normalized model?

Choose 3   The data model is more flexible.

- What are the maximum number of tables that can be joined together in a query?
   256

- What are the space requirements for views?
   Views require the space used to store a few records in various system tables.

- What are the three general types of constraints used for attributes in a data model?

Choose 2.   No Duplicates

- What are the three general types of constraints used for attributes in a data model?

Choose 2.   No Nulls

- What are the three join operations SQL Server supports?

Choose all that apply.   Nested loops

- What are the three join operations SQL Server supports?

Choose all that apply.   Merge join

- What are the three join operations SQL Server supports?

Choose all that apply.   Hash match

- What are the three main types of integrity implemented by SQL Server constraints?

Choose three.   Domain integrity

- What are the three main types of integrity implemented by SQL Server constraints?

Choose three.   Entity integrity

- What are the three main types of integrity implemented by SQL Server constraints?

Choose three.   Referential integrity

- What are two advantages of triggers over rules and constraints?

Choose two.   The ability to use looping structures.

- What are two advantages of triggers over rules and constraints?

Choose two.   The ability to reference data in other databases.

- What do joins enable you to do?

Select all that apply.   Correlate data from multiple tables

- What do joins enable you to do?

Select all that apply.   Identify unmatched rows between two tables.

- What does DTS call its saved jobs?
   Packages

- What does the Merge agent do?
   Monitors and resolves conflicts on the Publisher and Subscribers.

- What does the Primary Key of a table enforce?
   Entity or row integrity

- What does the WITH ENCRYPTION option do when it's involved in creating a view?
   SQL Server encrypts the entries in the syscomments table so that no one, not even the view owner, can see how the view was created.

- What features make SQL Server a good choice for supporting Internet Applications?

Choose all that apply.   Integrated XML support

- What features make SQL Server a good choice for supporting Internet Applications?

Choose all that apply.   Support for centralized business rules in the form of stored procedures

- What features make SQL Server a good choice for supporting Internet Applications?

Choose all that apply.   Support for ODBC and OLE-DB so SQL Server can respond to direct queries from the maximum variety of browsers.

- What features make SQL Server a good choice for supporting Internet Applications?

Choose all that apply.   Support for virtually unlimited number of client connections

- What is a valid GROUP BY clause for the following SELECT list?
SELECT Pub_ID, Type, Max(Price)
   GROUP BY Pub_ID, Type

- What is the lifetime of a user-defined variable?
   Duration of the batch

- What is the maximum number of rows that a table can store in SQL Server 2000?
   No limit besides physical storage size

- What is the purpose of the column list in the INSERT statement?
   It lists which columns will have data inserted into them.

- What is the purpose of the WITH NOCHECK option in the ALTER TABLE statement?
   CHECK and FOREIGN KEY constraints are not checked for the duration of the table modification.

- What role do CHECK constraints play in partitioned views?
   CHECK constraints are required to ensure that an inserted row goes to the correct table.

- What statement could you use in the WHERE clause to select all the rows in a table where no price is defined?
   WHERE price IS NULL

- What statement would you use in a WHERE clause to select all the names beginning with B from a table.
   WHERE name LIKE ‘B%’

- What tool included with SQL Server is primarily used for writing and executing ad-hoc queries?
   SQL Server Query Analyzer

- When creating a database with the CREATE DATABASE command, in what units can the file size be specified?

Choose all that apply.   MB (Megabytes)

- When creating a database with the CREATE DATABASE command, in what units can the file size be specified?

Choose all that apply.   KB (Kilobytes)

- 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?
   Using a DateTime data type for the column.

- When is it important to use the WITH RECOMPILE option when creating a stored procedure?
   Only if the query plans used by the stored procedure change frequently.

- When is the execution plan for a stored procedure cached?
   The first time the procedure is run

- When orders are placed into a table from your e-commerce Web site, you want to ensure that a proper quantity is entered for each item. Which of the following methods would not work to accomplish this task?

Choose 2.   Specifying an Integer data type

- When orders are placed into a table from your e-commerce Web site, you want to ensure that a proper quantity is entered for each item. Which of the following methods would not work to accomplish this task?

Choose 2.   Specifying a NOT NULL constraint

- When using an INSERT statement, data to be inserted may come from which of the following?

Select all that apply.   The VALUES clause

- When using an INSERT statement, data to be inserted may come from which of the following?

Select all that apply.   A stored procedure

- When using an INSERT statement, data to be inserted may come from which of the following?

Select all that apply.   Another database

- When using an INSERT statement, data to be inserted may come from which of the following?

Select all that apply.   A SELECT statement

- When using BCP to copy data into a table, which of the following statements apply?

Choose 2.   Database users will see the rows inserted by BCP after each batch is complete.

- When using BCP to copy data into a table, which of the following statements apply?

Choose 2.   You must have INSERT permissions on the table.

- When using BCP to transfer data out of a table, which of the following statements apply?

Choose 3.   You must have SELECT permissions on the table.

- When using BCP to transfer data out of a table, which of the following statements apply?

Choose 3.   Data changes made by other users during the BCP will not be reflected in the data file.

- When using BCP to transfer data out of a table, which of the following statements apply?

Choose 3.   The contents of the data file are replaced if the data file already exists.

- Which of the following are aggregate functions?


Choose 3.   Sum

- Which of the following are aggregate functions?


Choose 3.   Max

- Which of the following are aggregate functions?


Choose 3.   Count

- Which of the following are good uses for the I-SQL utility?

Choose 2.   Execution of Data Definition Language statements

- Which of the following are good uses for the I-SQL utility?

Choose 2.   Schedule execution of a T-SQL script

- Which of the following are good uses for the Query Analyzer?

Choose 3.   Writing and debugging a trigger

- Which of the following are good uses for the Query Analyzer?

Choose 3.   Analyzing the performance of a query

- Which of the following are good uses for the Query Analyzer?

Choose 3.   Generating a quick one-time report from a sales database

- Which of the following are parts of a SELECT statement?


Choose all that apply.   FROM

- Which of the following are parts of a SELECT statement?


Choose all that apply.   WHERE

- Which of the following are parts of a SELECT statement?


Choose all that apply.   SELECT

- Which of the following are reasons to choose SQL Server 2000?

Choose all that apply.   Internet integration

- Which of the following are reasons to choose SQL Server 2000?

Choose all that apply.   Scalability and availability

- Which of the following are reasons to choose SQL Server 2000?

Choose all that apply.   Enterprise-Level Database features

- Which of the following are reasons to choose SQL Server 2000?

Choose all that apply.   Data warehousing

- Which of the following are results of specifying schema binding on a view?

Choose all that apply.   The CREATE statement for the view must contain a two-part name for all objects referenced.

- Which of the following are results of specifying schema binding on a view?

Choose all that apply.   Objects referenced by the view cannot be dropped or changed while the view is schema bound.

- Which of the following are valid options for a FETCH command?

Choose 2.   NEXT

- Which of the following are valid options for a FETCH command?

Choose 2.   PREVIOUS

- Which of the following are valid reasons for using views in an application?

Choose all that apply.   Views provide a level of abstraction from the physical layer of the database.

- Which of the following are valid reasons for using views in an application?

Choose all that apply.   Views control how users can interact with a set of tables.

- Which of the following best describes a distribution server?
   A role that a SQL Server can play in which it coordinates communication between the publishing server and the subscribing servers.

- Which of the following best describes a situation in which the statistics for a table would be misleading?
   Deleting all of the records of a specific gender.

- Which of the following best describes the Log Reader agent?
   Monitors the transaction logs of the published database and copies new transactions to the Distributor.

- Which of the following commands are parts of the Data Definition Language (DDL)?
   CREATE TABLE

- Which of the following commands can be used to interrupt processing of a While loop?
   BREAK

- Which of the following commands can be used to interrupt processing of a While loop?
   CONTINUE

- Which of the following commands can best assist in diagnosing locking problems?
   sp_who

- Which of the following commands can you use to refresh the statistics on a table's indexes?
   UPDATE STATISTICS

- Which of the following correctly demonstrates an update to the ClientName column of the Customer view?
   update customer set clientname = 'McBride'

- Which of the following describe legitimate methods for reading data from a remote server?

Choose all that apply.   Referencing an object on a linked server through a four-part naming convention

- Which of the following describe legitimate methods for reading data from a remote server?

Choose all that apply.   Using OPENQUERY in the FROM cause of a statement to retrieve results from a query on the remote server

- Which of the following describe legitimate methods for reading data from a remote server?

Choose all that apply.   Using OPENROWSET in the FROM cause of a statement to retrieve results from a query on the remote server

- Which of the following does not represent a benefit of an N-tier (multiple tier) application design?
   The client is working directly on the server hardware, thus eliminating any network traffic in the application.

- Which of the following EXEC statements are valid?

Choose all that apply.   exec @ReturnValue = MyProc 5, 4, 'Hello', @A OUTPUT

- Which of the following EXEC statements are valid?

Choose all that apply.   exec @ReturnValue = MyProc @length = 5, @width = 4, @message = 'Hello', @A = @A OUTPUT

- Which of the following features are new to SQL Server 2000?

Choose all that apply.   Indexed views

- Which of the following features are new to SQL Server 2000?

Choose all that apply.   Cascading Referential Integrity Constraints

- Which of the following FOR XML options will allow you to extract data in XML format that will conform to a given schema?
   FOR XML EXPLICIT

- Which of the following is a good reason to use transactional replication?
   It provides a method for using a lot of resources on several servers in order to provide up-to-the-second reporting capabilities.

- Which of the following is a valid reason to move index storage into a different filegroup than the data?
   To put the filegroup containing the indexes onto a separate physical disk or set of disks.

- Which of the following is the best way to limit the output rowset of a stored procedure?
   Use parameters passed into the stored procedure to change the output query in the stored procedure.

- Which of the following is the easiest to implement way to re-index all of the tables in a database on a scheduled basis?
   Use the Database Maintenance Plan Wizard

- Which of the following options will create a composite unique index on the CustomerID and ProjectID fields in the CP table?
   create unique index CPidx on CP(CustomerID, ProjectID)

- Which of the following SQL features is used to generate summary data?
   Aggregate functions

- Which of the following statements about views are true?

Choose the correct statement(s).   Unless an INSTEAD OF trigger is defined on the view, modifications can affect only one table in a single statement.

- Which of the following statements about views are true?

Choose the correct statement(s).   Views can include computed columns.

- Which of the following statements are true of sub-queries?

Choose 2.   A sub-query must be enclosed in parentheses.

- Which of the following statements are true of sub-queries?

Choose 2.   A sub-query in the WHERE clause can refer to fields from the outer query.

- Which of the following statements are true of the HAVING clause?

Choose 3.   The HAVING clause can be used in any statement instead of the WHERE clause.

- Which of the following statements are true of the HAVING clause?

Choose 3.   The HAVING clause can restrict rows based on the values of aggregate functions.

- Which of the following statements are true of the HAVING clause?

Choose 3.   The HAVING clause can refer to fields not in the GROUP BY clause.

- Which of the following statements cannot be used to create a view?

Select all that apply.   COMPUTE

- Which of the following statements cannot be used to create a view?

Select all that apply.   ORDER BY

- Which of the following statements is true of correlated sub-queries?
   A sub-query that correlates to the outer tables cannot be used as a derived table in the SQL-92 join syntax.

- Which of the following types of columns would be good candidates for an index?

Choose all that apply.   Columns that make up the Primary Key

- Which of the following types of columns would be good candidates for an index?

Choose all that apply.   Columns that contain Foreign Keys

- Which of the following types of columns would be good candidates for an index?

Choose all that apply.   Columns that contain data such as a tax ID or address for a large population

- Which of the following will help reduce deadlocks in your database?
   Always accessing tables in the same order in all programs

- Which one of the following statements will return all the author IDs from the Authors table along with any title IDs on matching rows from the TitleAuthor table?
   SELECT Authors.Au_ID, TitleAuthor.Title_ID

- Which queries will return all the names in the Authors table sorted by first name, then last?

Choose 2.   SELECT au_fname, au_lname

- Which queries will return all the names in the Authors table sorted by first name, then last?

Choose 2.   SELECT au_fname, au_lname

- Which replication strategy is most prone to conflicts?
   Merge replication

- Why is the WITH ENCRYPTION option useful for triggers?
   It causes the trigger text to be encrypted in syscomments so no one can see how the trigger is implemented.

- With which of the following commands can you reorganize a table to eliminate excessive fragmentation?
   DBCC REINDEX