Chapter 12. Answers to Practice Exam One

1. A

2. D

3. B

4. B, E

5. D

6. C

7. B

8. E, F

9. C

10. E

11. D

12. C, D

13. B

14. D

15. A, C, D

16. F

17. A

18. C

19. E

20. D

21. B

22. D

23. B, C

24. C

25. A, C

26. A

27. A

28. C

29. C

30. C

31. B

32. C

33. C

34. B

35. C

36. A

37. D

38. B, C

39. A

40. B, C, D

41. D

42. B

43. A, C

44. A, C, D

45. A, C

46. B

47. D

48. D

49. B

50. C

Remember that this is a Microsoft exam that must be answered in a Microsoft way. Here are the answers to the first practice exam. Each answer is accompanied by references to the applicable materials within SQL Server Books Online and on the Microsoft official websites.

A1:	Answer: A. By placing joined tables into separate filegroups on different arrays you can allow for the reading of both arrays at the same time during the join operation. For the same reason, when nonclustered indexes are separated from their data, both arrays can be best utilized simultaneously, allowing for sequential access through indexes that don't interfere with the random accesses of the data. Striped volumes will provide better performance than mirrored volumes. For more information see Chapter 3, "Implementing the Physical Database," and Chapter 8, "Designing for Optimized Data Access." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Files and Filegroups, Using Files and Filegroups. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp, Chapter 14, "Improving SQL Server Performance," Deployment Considerations.
A2:	Answer: D. There are several keys to watch out for in this type of question. The first is the types of systems being distributed to. When you can't determine the design of the application that is going to use the data, more or less the data system that will store the data, you need a mechanism that provides for the most versatility for generic data distribution. This pretty well eliminates the backup/restore approach, which mandates the same versions and sort order of SQL Serveronly database systems. Replication would require a different solution for each subscriber and would require some level of connectivity between the systemsin this instance, it's just too difficult a solution. XML, Text, and HTML files are all reasonable (or possible) solutions, but XML is preferred because it provides for descriptive data schema within the files themselves. For more information see Chapter 6, "Programming Business Logic," and Chapter 10, "Completing a Business System Design." Go to SQL Server Books Online, SQL Server Architecture, SQL Server and XML Support. Go to http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarxml/html/elxml.asp, XML and Web Services, Elementary XML (XML General Technical Articles).
A3:	Answer: B. An integer data type of one form or another would be the correct choice for the type for the order number. The size of integer used would be determined based on the total number of orders maintained in the table over time. By the looks of the volume being discussed, it may even be worth considering an alphanumeric data type in the future. Small and tiny integers would be out of the question because they don't provide for a size large enough to even hold a week's worth of data. An IDENTITY column is the best way to implement sequential numbering. It can be automated at the data store and thus guarantee uniqueness. The front-end application solution could easily produce duplicates between two different entry points. A uniqueidentifier is never a good choice for any field value that has the possibility of being placed within a key or an index. The space taken up by this type of field will hamper performance of data inserts and retrievals. A timestamp, though producing the necessary uniqueness, is cumbersome to work with as an order number. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Transact-SQL Reference, Data Types. Go to SQL Server Books Online, Using the SQL Server Tools, Using Interface Reference, Visual Database Tools, Developing Database Structure, Working with Keys, Defining Primary Keys. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvcondatabasedesignerconsiderationssql.asp, Development Tools and Languages, Visual Studio.NET, Product Documentation, Developing with Visual Studio.NET, Designing Distributed Applications, Visual Database Tools, Reference, Database Server Considerations, SQL Server Databases.
A4:	Answer: B, E. The key to this question is that this operation is going to be performed as a one-time thing, so the creation of data objects would likely be avoided and views would not be warranted. However, a script that performs the activity could easily be saved if needed in the future. Table aliases may help in your development, but in this scenario column aliases provide the end user with the necessary data definition. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Data Transformation Services. Go to SQL Server Books Online, Replication. Go to SQL Server Books Online, Administering SQL Server, Importing and Exporting Data. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_1njt.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Importing and Exporting Data.
A5:	Answer: D. Dates are inclusive with the BETWEEN function. Be careful when using comparisons that may rely on the time elements of the data because improperly formulating a condition could exclude some desired data. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Transact SQL Reference, BETWEEN. Go to SQL Server Books Online, Transact SQL Reference, IN. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowspecifyingmultiplesearchconditionsforonecolumn.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User Interface Reference, Designing Data Retrieval and Manipulation, Designing Queries.
A6:	Answer: C. You are probably not ordering the data to achieve the desired results. Grouping of the resultset doesn't seem to be warranted because the question is asking for five rows. NULL values should not affect this query, though in some instances NULL data can interfere with the results. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Query Fundamentals, Filtering Rows with WHERE and HAVING, NULL Comparison Search Conditions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvovrspecifyingsearchcriteria.asp, MSDN Home.
A7:	Answer: B. With all versions of SQL Server, the interface doesn't always show newly created objects. A periodic refresh in the Enterprise Manager and the object browser within the Query Analyzer is needed to ensure accuracy of the display. For more information see Chapter 1, "Database Development on SQL Server 2000." Go to SQL Server Books Online, SQL Server Architecture, Administration Architecture, Graphical Tools, SQL Query Analyzer. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1bad.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User Interface Reference, SQL Query Analyzer Dialog Boxes and Windows.
A8:	Answer: E, F. Both E and F produce the desired results, though in this instance the IN may be easier to read and use and somewhat more efficient. The exam may ask you to choose the best of several working queries, in which case F would be the best answer. For more information see Chapter 1, "Database Development on SQL Server 2000." Go to SQL Server Books Online, SQL Server Architecture, Administration Architecture, Graphical Tools, SQL Query Analyzer. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1bad.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User Interface Reference, SQL Query Analyzer Dialog Boxes and Windows.
A9:	Answer: C. Date data types also include information for time, which causes many comparisons to not provide the desired results. Use the DATEDIFF function to prevent this problem. In this case the current date would be greater than the production date on file, and therefore the first parameter must be PDate. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Transact SQL Reference, DATEDIFF. Go to SQL Server Books Online, Transact SQL Reference, Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spssdk/html/_where_clause.asp, MSDN Home, MSDN Library, Web Development, Server Technologies, SharePoint Products and Technologies, Reference, Search Programmability, SharePoint Portal Server Search SQL Syntax.
A10:	Answer: E. The most efficient and quickest way of performing this task would be to get the data out before you create the default. When the data is placed back without the ID, the default will generate the missing values. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Creating and Modifying a Table, Creating and Modifying Default Definitions. Go to SQL Server Books Online, Transact SQL Reference, CREATE DEFAULT. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_3x4k.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Create Default.
A11:	Answer: D. Both A and C essentially do the same thing and would provide the desired results if tables were already present. The INTO within the SELECT command is optional, which would allow for either syntax. SELECT INTO is the most appropriate when tables do not already exist. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Designing Data Retrieval and Manipulation, Manipulating Data, Creating INSERT INTO Queries. Go to SQL Server Books Online, Transact SQL Reference, SELECT INTO. Go to SQL Server Books Online, Transact SQL Reference, INSERT INTO. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1l4j.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, SELECT.
A12:	Answer: C, D. You don't want to set unnecessary read-only properties that wouldn't permit any alterations to the database or the records contained therein. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Modifying a Database, Setting Database Options. Go to SQL Server Books Online, Transact SQL Reference, System Stored Procedures, sp_dboption. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_2ke1.asp, MSDN Home.
A13:	Answer: B. The query needed is a simple SELECT query with a WHERE condition for the site. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Transact SQL Reference, SELECT, SELECT Examples. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, SELECT.
A14:	Answer: D. Option A is the fastest, and the options get slower as you go down the list. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Using the SQL Server Tools, Command Prompt Utilities, Getting Started with Command Prompt Utilities, bcp Utility. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Using the SQL Server Tools, BCP Utility.
A15:	Answer: A, C, D. In choosing UPDATE, you would be selecting an option whose purpose is exactly what you want to avoid. You should be able to increase the data storage size and alter a column name without affecting the internal data. However, decreasing the size for data storage results in data truncation or loss. INSERT, used appropriately, adds data but does not alter any existing values. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Transact-SQL Reference, INSERT. Go to SQL Server Books Online, Transact-SQL Reference, ALTER TABLE. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowresizingpropertycolumns.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User Interface Reference, Developing Database Structure, Working with Tables.
A16:	Answer: F. All the reasons, excluding the agent, are very possibly a cause of the symptoms being described. The SQL Server Agent handles nondata activity on the server related to operators, jobs, and events configured on the system. If the Agent is not running, only these particular processes are interrupted, not the entire database. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Transact-SQL Reference, INSERT. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp9/html/f9d15b20-eb9d-4c37-8d4a-d9d02c01eb56.asp, MSDN Home, MSDN Library, Development Tools and Languages, Visual FoxPro, Reference, Language Reference, Commands, INSERT SQL Command.
A17:	Answer: A. Given the problem scenario, you will need an additional entity to maintain a normalized structure while still allowing for the instructor information to be attached to the course. Option B would cause significant redundant data and would be a poor design choice. Option C would create a far more complex key than is needed and would also cause the length of the key to be rather large. Option D is not necessarily a poor choice but would do nothing to sort out the data design issues. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconDataNormalization.asp, MSDN Home, MSDN Library, Development Tools and Languages, Visual Studio .NET, Developing with Visual Studio .NET, Designing Distributed Applications, Data Design, Data Integrity, Data Normalization.
A18:	Answer: C. The communication configuration between the two servers has already been set up, so the only necessary element on the coding would be the use of a four-part name within the query. If the desire was to pass the query to the linked server, the OPENQUERY functionality could be utilized. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Administering SQL Server, Managing Servers, Configuring Linked Servers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/prsql_4vxv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Building SQL Server Applications, SQL in Analysis Services, Passing Queries from SQL Server to a Linked Analysis Server.
A19:	Answer: E. SCHEMABINDING refers only to SQL Server objectsspecifically tables, views, and user-defined functions. An XML schema cannot be bound in this manner. XML resides in memory and is processed against its own internal set of rules, referred to as a schema. An XML schema interacts directly with the data to supply logic and display attributes on the user's browser. HTML does not have the required functionality. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, What's New, XML Integration of Relational Data. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_2joz.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Building SQL Server Applications, Programming ADO SQL Server Applications, ADO Support for SQL Server XML Features, Mapping an XML Schema to a Relational Schema Using Annotated Schemas.
A20:	Answer: D. Option A is incorrect because the LIKE keyword is not efficient in searching on text-based columns. Option B is wrong for the same reason. Option C is wrong because the syntax for CONTAINS is wrong. Option D is correct because it uses the correct method of searching, using FREETEXT. Where test searching is available, SQL Server will perform queries more efficiently with their use. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Full-Text Search, Using the FREETEXT Predicate. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ehx.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Relational Database Engine Architecture, Full-Text Query Architecture.
A21:	Answer: B. What you are really looking at with this solution is to allow the client to have a 10-record window of data. The bulk of the data will be maintained on the server, with 10 records being sent to the client when the client requests the information. This is likely to be the best choice for the implementation of the process. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Cursors, Cursor Implementations. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, Application Architecture, Microsoft Patterns and Practices for Application Architecture and Design, .NET Data Access Architecture Guide.
A22:	Answer: D. You know exactly how many new records are coming in. You know that 25,000 is 50% of 50,000, so filling the page by 50% and leaving 50% free space for the remaining 50,000 records seems logical. The default FILLFACTOR of 0 doesn't leave any room for additions; this will slow inserts. If you set the FILLFACTOR too big, searches slow down because any query processed has to cycle through a lot of empty space. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Building SQL Server Applications, SQL-DMO, SQL-DMO Reference, Properties, F, FILLFACTOR. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_9ak5.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Indexes, Designing an Index, Fill Factor.
A23:	Answer: B, C. The column that you plan to index has to be made up of text data. After you create a full-text index, it is not automatically updated and it resides in a storage space called a full-text catalog. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, SQL Server Architecture, Relational Database Engine Architecture, Full-Text Query Architecture. Go to http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexnt00/html/ewn0092.asp, MSDN Home, MSDN Library Archive, Exploring Windows NT (2000), Implementing the Full-Text Search Service in SQL Server.
A24:	Answer: C. To gain optimum performance, you want to get as many different physical devices into the fray as possible. By pulling data simultaneously from different drive arrays and controllers, you can improve the speed at which data is read. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Files and Filegroups. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Databases, Parts of a Database, Files and Filegroups, Using Files and Filegroups.
A25:	Answer: A, C. The WITH SCHEMABINDING argument of the CREATE INDEX statement is needed when creating an indexed view. When WITH SCHEMABINDING is specified, tables participating in the indexed view are prevented from alteration and deletion. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Views, Creating a View, Creating an Indexed View. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Improving Performance with SQL Server 2000 Indexed Views.
A26:	Answer: A. You can use the stored procedures sp_who and sp_lock to find the locks that are currently in place and the owners of the processes. Similar information can be received by viewing the lock information in the Current Activity window of the SQL Server Enterprise manager. For more information see Chapter 7, "Tuning and Optimizing Data Analysis." Go to SQL Server Books Online, Transact-SQL Reference, Stored Procedures, sp_who. Go to SQL Server Books Online, Transact-SQL Reference, Stored Procedures, sp_lock. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Server and Database Troubleshooting, Troubleshooting Locking.
A27:	Answer: A. Before executing full-text searches, you must create and populate a full-text catalog. A full-text catalog is the basis of the storage used for the indexes. Periodically these catalogs should be repopulated to ensure usefulness. Repopulation can be done by schedule or by administrative task. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, SQL Server Architecture, Relational Database Engine Architecture, Full-Text Query Architecture. Go to http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexnt00/html/ewn0092.asp, MSDN Home, MSDN Library Archive, Exploring Windows NT (2000), Implementing the Full-Text Search Service in SQL Server.
A28:	Answer: C. There can be only one clustered index per table and as many as 249 nonclustered indexes. This will become an issue only when you attempt to create a second clustered index or when you add an index to a table that has already been given a primary key with the default settings. For more information see Chapter 3, "Implementing the Physical Database," and Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Database Development and Visual Database Tools, Database Objects, Indexes. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Creating Indexes.
A29:	Answer: C. This is a many-to-many relationship scenario, which in SQL Server is implemented using three tables. The center table, often referred to as the connecting or joining table, is on the many side of both of the relationships to the other base table. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Database Development, Database Objects, Table Relationships. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvcontablerelationships.asp, MSDN Home, Table Relationships.
A30:	Answer: C. Option A is not correct because you can't use COUNT(*) with COMPUTE BY. Option B is not correct because it's missing the ORDER BY. The fourth choice is totally fictitious. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Summarizing Data, Summarizing Data Using COMPUTE and COMPUTE BY. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_08_4ego.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Advanced Query Concepts, Summarizing Data, Summarizing Data Using COMPUTE and COMPUTE BY.
A31:	Answer: B. Option A doesn't fit the requirements, option C uses if rather than when, and option D is missing an end. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Control-of-Flow, Using CASE. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, CASE.
A32:	Answer: C. The other close answer is A, but that shows you all the records, and not necessarily the count. Option B is wrong because CNT() is not an aggregate function. COUNT(*) is an aggregate function. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Using Aggregate Functions, Using COUNT(*). Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5790.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, COUNT.
A33:	Answer: C. Option A would work if you could find someone who wanted to sit and count through the output. Options B and D are invalid syntax: B because you have to name fields in a GROUP BY, and D because the Type field isn't in the SELECT list. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Grouping Rows with GROUP BY. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, SELECT, SELECT Examples.
A34:	Answer: B. Only option B uses correct syntax. Option A doesn't have everything in the select list it needs, and doesn't count anything. Option C should read COMPUTE COUNT(*) BY PCType and it would work; but it still wouldn't be as efficient as option B. Option D is just a mess syntactically. For more information see Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Summarizing Data, Summarizing Data with ROLLUP. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_08_3ho3.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Advanced Query Concepts, Summarizing Data, Summarizing Data with ROLLUP.
A35:	Answer: C. Vertical partitioning is the selection of some (but not all) columns in a table. Horizontal partitioning is the creation of an article based on some (but not all) rows in a table. Horizontal filtering is a term that is sometimes used as a synonym for horizontal partitioning. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Replication Options, Filtering Published Data. Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Physical Database Design, Partitioning. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/reploptions_0jam.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Replication Options, Filtering Published Data.
A36:	Answer: A. The Merge agent connects to the publishing server and the subscribing server and updates both as changes are made. The major role of the Merge agent is to propagate the updates, and then monitor for conflicts. The agent is also responsible for applying the initial snapshot at the subscriber. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Merge Agent. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_30z7.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Types of Replication, Merge Replication.
A37:	Answer: D. In transactional replication, the Log Reader agent, by default, resides on the Distributor. Because you are using a push subscription, the Distribution agent is also located on the Distributor, by default. If you were to use a pull subscription, the Distribution agent would by default be located on the Subscriber. You can alter the location on which the agent is run by using remote agent activation if the load would be better distributed by not using defaults. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Merge Agent. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_30z7.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Types of Replication, Merge Replication.
A38:	Answer: B, C. Out of the answer choices provided, only two were actually possible causes: The Model, Master, Msdb, and TempDB databases cannot be replicated, and a replication process must contain at least two agents, depending on the replication options chosen. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Replication Overview. Go to SQL Server Books Online, Replication, Replication Data Considerations. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Replication Overview.
A39:	Answer: A. The Log Reader agent moves transactions from the transaction log of the published database on the Publisher to the distribution database or server. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Log Reader Agents. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replmon_57xv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Administering and Monitoring Replication, Replication Agents, Log Reader Agent.
A40:	Answer: B, C, D. Because of index fragmentation, the reduction in performance in this database could be improved by rebuilding, re-creating, or defragmenting the current index. For more information see Chapter 7, "Tuning and Optimizing Analysis," and Chapter 8, "Designing for Optimized Data Access." Go to SQL Server Books Online, Transact-SQL Reference, DBCC. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto03.asp, MSDN Home, MSDN Library, .NET Development, Improving .NET Application Performance and Scalability, How Tos, How To: Optimize SQL Indexes. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, DBCC, DBCC SHOWCONTIG.
A41:	Answer: D. Procedures 72 and 78 are holding locks against the database and preventing the stored procedure from executing. For more information see Chapter 7, "Tuning and Optimizing Analysis." Go to SQL Server Books Online, Accessing and Changing Relational Data, Locking, Displaying Locking Information. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_0uni.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Locking, Understanding Locking in SQL Server.
A42:	Answer: B. The data file is growing at too small an increment, which causes growth to occur in small, too-frequent increments. You may even want to set the growth rate higher than 10%, but of the available choices this is the best solution. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Database Files. Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Physical Database Architecture, Physical Database Files. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Physical Database Architecture, Physical Database Files and Filegroups.
A43:	Answer: A, C. By definition, timestamp and uniqueidentifier data types are guaranteed to be unique. The timestamp is an automatically entered value. The uniqueidentifier is usually entered using a NEWID() function to generate the uniqueidentifier. For more information see Chapter 2, "Creating a Logical Data Model," and Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Accessing and Changing Relational Data, Transact-SQL Syntax Elements, Using Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Data Types.
A44:	Answer: A, C, D. An Identity provides for uniqueness by incrementing a value continually, and therefore it is a standard choice for a column that requires a unique value. Unique indexes and unique constraints enforce the uniqueness of entered values and do not let any entry come into the system that already exists. For more information see Chapter 2, "Creating a Logical Data Model," and Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables, Autonumbering and Identifier Columns. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8kqb.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Tables, Creating and Modifying a Table, Creating and Modifying Identity Columns.
A45:	Answer: A, C. The name fields can be broken down into firstname and lastname and therefore are not in their most decomposed form. This breaks the first normal form rule of normalization. The salesman name should not be stored in this entity because it depends on the SalesmanID and not the CustomerID. This breaks the third normal form rule of normalization. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Database Design Considerations, Normalization. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconDataNormalization.asp, MSDN Home, MSDN Library, Development Tools and Languages, Visual Studio .NET, Developing with Visual Studio .NET, Designing Distributed Applications, Data Design, Data Integrity, Data Normalization.
A46:	Answer: B. An automobile's VIN (Vehicle Identification Number), though unique, is character data and is much too large to use as a primary key. This is a perfect situation for an automatically incremented numeric surrogate key that will take up a lot less storage space. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables, Using Constraints, Defaults and NULL Values, PRIMARY KEY Constraints. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_3bsp.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Databases, Database Design Considerations, Data Integrity.
A47:	Answer: D. According to byte sizes, int would take considerably less space compared against the current nchar(5) setting. Smallint would even be better, but it has an upper limit of 32,767. Char(5) would cut the space used in half but is not as good as using actual numeric storage. Whenever a variable is going to contain only numbers, numeric storage is always more efficient. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Transact-SQL Reference, Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dblibc/dbc_pdcapb_7dr6.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Building SQL Server Applications, DB-Library and C Reference, Using DB-Library and C Datatypes.
A48:	Answer: D. This is a tricky question to resolve, and if it were not for the space restriction, there would be a temptation to use characters for the storage. At 8 bytes each (double that of int) the easier technique would be to track days from the beginning of recorded time in an integer. requires six digits, and therefore int is the closest to the size required. Datetime allows dates only in the 1700s; smalldatetime, in the 1900s. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Transact-SQL Reference, Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Data Types.
A49:	Answer: B. The many-to-many relationship in this scenario occurs because many contractors can work on a single site, and a single contractor can work at many sites. The connection needs to involve both sites and contractors for an appropriate relationship to be drawn. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Logical Database Design. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2oby.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Databases, Database Design Considerations, Normalization.
A50:	Answer: C. Float gives accuracy up to 308 decimal places, which is almost 10 times better than can be achieved with any of the other types. Real and decimal data types provide only 38 decimal places of accuracy at, whereas money and smallmoney have accuracy to only the ten-thousandths. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, Transact-SQL Reference, Data Types. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Data Types.