Chapter 14. Answers to Practice Exam Two

1. C

2. A

3. A,C

4. C

5. B

6. D

7. A

8. C

9. C

10. A

11. A

12. C

13. B

14. B

15. C

16. C

17. D

18. A

19. D

20. C

21. B

22. D

23. C

24. C

25. A, D

26. C

27. B

28. C

29. C

30. D

31. A

32. D

33. D

34. B

35. A

36. C

37. D

38. A

39. C

40. A

41. B

42. D

43. D

44. B

45. C

46. B

47. D

48. D

49. D

50. A

Remember that this is a Microsoft exam that must be answered in a Microsoft way. Here are the answers to the second 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: C. The Full-Text Indexing Wizard is a graphical tool that enables full-text searches on columns by easily and quickly creating full-text indexes and full-text catalogs. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Indexes, Full-Text Catalog and Indexes. Go to http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexnt00/html/ewn0092.asp, MSDN, MSDN Library Archive, Exploring Windows NT (2000).
A2:	Answer: A. Before creating a full-text index, it is required that you close all applications and wizards running or using the MSSearch utility. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Indexes, Full-Text Catalog and Indexes. Go to http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexnt00/html/ewn0092.asp, MSDN, MSDN Library Archive, Exploring Windows NT (2000).
A3:	Answer: A, C. The question is really asking you to normalize the data. In the current design the general information within an order will be repeated for each line item in the order. To eliminate the redundant data, an OrdersDetail table could be used in a one-to-many relationship with orders. By creating a more efficient relationship structure, you will promote fast responses from queries. 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://support.microsoft.com/default.aspx?scid=kb;en-us;100139, Database Normalization Basics.
A4:	Answer: C. First off, you must assume that this is not the only table in the system. The indicators present in the table for AttributeID and ApplicantID indicate at least two other related tables. This table will represent a connection to those tables. The final thing to determine is the relationship between the tables, and because each applicant could have many attributes with each attribute belonging to several applicants, you have a traditional many-to-many relationship. 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 and Visual Database Tools, Database Objects, Table Relationships. Go to http://support.microsoft.com/kb/234208/EN-US/, Understanding Relational Database Design.
A5:	Answer: B. Recognize that deadlock scenarios in most instances are caused by poor application design. Numerous types of design flaws can cause deadlock scenarios, including accessing of objects in an inconsistent order or setting unnecessary locks, to name a couple. To isolate the part of the application causing the problem, you will need to test the program within its production environment. Although the System Monitor will be able to provide you with information on the numbers of deadlocks, it does not provide much specific information about where they are occurring. The SQL Profiler is specifically designed to closely monitor what is going on within the DBMS and provide detailed information. For more information see Chapter 8, "Designing for Optimized Data Access." Go to SQL Server Books Online, Accessing and Changing Relational Data, Locking. Go to SQL Server Books Online, Administering SQL Server, Monitoring Server Performance, Monitoring with SQL Profiler. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_4uav.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Application Design.
A6:	Answer: D. Because the table structure has recently been altered, it is a good possibility that this change has caused the indexing information to become unstable or that statistics affecting the index have not been updated. If you restart the service, SQL Server should then update the statistical information accordingly. After the restart, you may want to ensure that all statistics are intact. Restarting the service is a far more reasonable solution than rebooting the server, though potentially both would serve the same purpose. Also consider index fragmentation as a possible source of the problem. For more information see Chapter 7, "Tuning and Optimizing Analysis." Go to SQL Server Books Online, Creating and Maintaining Databases, Indexes, Creating an Index, Statistical Information. 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, Optimizing SQL Indexes.
A7:	Answer: A. Answer B is wrong because this would be more time-consuming than DBCC DBREINDEX. You would have to individually rebuild all indexes. Answer C is not correct because deleting a clustered index and then re-creating it means deleting and re-creating all nonclustered indexes. Also, the process would have to involve two separate steps. For more information see Chapter 7, "Tuning and Optimizing Analysis." 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/scalenetchapt14.asp, MSDN Home, MSDN Library, .NET Development, Improving .NET Application Performance and Scalability, Improving SQL Server Performance.
A8:	Answer: C. A single table could provide all the necessary information with no redundancy. The table could easily be represented using a self-join operation to provide the desired reporting. For more information see Chapter 2, "Creating a Logical Data Model." Go to SQL Server Books Online, English Query, English Query Fundamentals, SQL Database Normalization Rules. 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.
A9:	Answer: C. To enable full-text searching, you must create a catalog for a table and then populate manually or schedule a job. Answer A is incorrect because creating a non-unique index does not help in any way. B is incorrect because creating a unique clustered index does not enable full-text searching. D is incorrect because there is no such procedure as sp_fulltext_populate (but there is a stored procedure named sp_fulltext_catalog). For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Indexes, Full-Text Catalogs and Indexes. 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.
A10:	Answer: A. To obtain the physical storage sequence of the data, you must use a clustered constraint or index. Although a primary key would also provide for the level of uniqueness, it is not the desired key for this table. For more information see Chapter 2, "Creating a Logical Data Model," and Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Indexes, Table Indexes. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_5b1v.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Logical Database Components, SQL Indexes, Table Indexes.
A11:	Answer: A. Indexes used in larger quantities often degrade the rate at which insertions, deletions, and some modifications to data occur. Nevertheless, indexes generally speed up data access in cases in which the data in the table is sufficient to warrant indexing. For more information see Chapter 3, "Implementing the Physical Database," and Chapter 7, "Tuning and Optimizing Analysis." Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Physical Database Design, Index Tuning Recommendations. 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, Optimizing SQL Indexes.
A12:	Answer: C. This is a perfect situation for sending a recordset to the client machine that can then be used by the front-end application to show only the data needed. XML provides a mechanism by which the data is shipped to the client, resides in the background of the client machine, and is present by the front end in any manner desired. 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 3, "Implementing the Physical Database," and Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, XML and Internet Support, XML and Internet Support Overview. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml07162001.asp, MSDN Home, MSDN Library, .NET Development, A Survey of Microsoft SQL Server 2000 XML Features.
A13:	Answer: B. For index analysis, the Index Tuning Wizard will help determine whether indexing is sufficient for the queries used within a workload file. The workload file should be created using the queries that are going to make up the reporting. A profiler trace would not provide the complete analysis of the indexes requested. The Query Analyzer is more for fine-tuning queries, as opposed to the table and index structures. The System Monitor is used more for an overview of the application server, the operating system, and the interactions with the server hardware. For more information see Chapter 3, "Implementing the Physical Database," and Chapter 5, "Retrieving and Modifying Data." Go to SQL Server Books Online, Using the SQL Server Tools. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp, MSDN Home, MSDN Library, .NET Development, Improving .NET Application Performance and Scalability, Improving SQL Server Performance.
A14:	Answer: B. You want to set the priority to LOW for the less important transaction. This will cause the transaction to be the victim of choice should a deadlock scenario occur. It is a simple matter of having this setting whenever deadlocks are possible and the transaction is not a critical part of the process. Error checking is not a suitable solution to this particular problem. In a sound application there will be error checking of some type, but in this instance the priority setting will take care of the situation. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Transactions, Controlling Transactions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3hdf.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Locking, Customizing Locking with SQL Server, Deadlocking, Minimizing Deadlocks.
A15:	Answer: C. The default location for the snapshot information is in the Distributor in the Mssql\Repldata folder. Using the publication properties, this location can be altered to a newly desired position. Be sure to enable FTP access to this new location if FTP is being used to transfer the snapshots. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Replication, Implementing Replication, Applying the Initial Snapshot. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_271q.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Planning for Replication, Planning for Each Type of Replication, Planning Snapshot Replication.
A16:	Answer: C. The GO delimiter separates one batch from another. The error in the batch prevented the entire batch from running, so to make the UPDATE statement run, put it into a different batch. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, GO. Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Batches. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_04_9elv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Accessing and Changing Data Fundamentals, Using Multiple Statements, Batches.
A17:	Answer: D. An error will occur because the variable @Var does not exist when the second batch of the script runs. A variable exists only within the batch that it was created in. This script contains two batches separated by the GO statement. Because the variable was declared in the first batch, it falls out of scope upon execution of the GO operation and thus does not exist when attempted to be used within the second batch. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, GO. Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Batches. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_04_9elv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Accessing and Changing Data Fundamentals, Using Multiple Statements, Batches.
A18:	Answer: A. Batch A works correctly and prints the multiples of 5 from 5 to 50. Batch B prints the multiples of 5 from 5 to 45, so it prints only nine of them. Batch C is an infinite loop because it doesn't increment its loop counter, and Batch D has a GO in the middle of it, so the variables aren't defined when they're being used. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, GO. Go to SQL Server Books Online, Transact-SQL Reference, WHILE. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_04_9elv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Accessing and Changing Data Fundamentals, Using Multiple Statements, Batches.
A19:	Answer: D. The FETCH statement is used to retrieve data from a cursor. To start the process, you would normally use a FETCH FIRST before entering a loop. At the tail end of the loop, you would implement a FETCH NEXT. DECLARE and OPEN are used to initialize the cursor but do not retrieve any data. RETRIEVE isn't a T-SQL command. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, FETCH. Go to SQL Server Books Online, Accessing and Changing Relational Data, Cursors, Fetching and Scrolling. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_9bzn.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Cursors, Cursor Implementations, Accessing and Changing Relational Data.
A20:	Answer: C. A deadlock is probably the best explanation. With that high a transaction isolation level, you're going to have lock contention in any busy database. You should likely reexamine the isolation level being used and consider eliminating this part of both batches. Data corruption would not occur from the result of the batches because dirty reads are not occurring at this isolation level. Although the isolation level is questionable, it is not invalid. For more information see Chapter 6, "Programming Business Logic," and Chapter 7, "Tuning and Optimizing Analysis." Go to SQL Server Books Online, Accessing and Changing Relational Data, Locking, Isolation Levels. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctransaction_isolation_levels.asp, MSDN Home, MSDN Library, Data Access, Microsoft Open Database Connectivity (ODBC), ODBC Programmer's Reference, Developing Applications and Drivers, Transactions, Transaction Isolation.
A21:	Answer: B. The value of the @@trANCOUNT variable increases by one when a BEGIN TRANSACTION statement is encountered, and it decreases to 0 when the ROLLBACK happens. The last transaction statement run was a ROLLBACK, which leaves @@trANCOUNT set to 0. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, BEGIN TRANSACTION. Go to SQL Server Books Online, Building SQL Server Applications, Meta Data Services Programming, Programming Information Models, Managing Transactions and Threads, Transaction Management Overview. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_66nq.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Transactions, Advanced Topics.
A22:	Answer: D. To some extent, all the answers work. The only differences between the first three are minor tweaks to the cursor operation. The best answer, though, is to not use a cursor, which is usually the right answer anyway. When an operation can be performed without a cursor, there is far less overhead and performance will improve. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Cursors. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_71nw.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Application Design.
A23:	Answer: C. It will print the value of @Foo. Rollbacks do not change how print statements work. A print happens immediately. Also, it's not an error to have an empty transaction or a transaction that just doesn't do any database updates. It's unwise, but not an error. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, Print. Go to SQL Server Books Online, Transact-SQL Reference, ROLLBACK TRANSACTION. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_2it2.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Transactions, Controlling Transactions.
A24:	Answer: C. The correct order for the events are INSTEAD OF triggers, constraints, AFTER triggers. Constraints will not prevent the execution of code for INSTEAD OF triggers but will prevent the code for an AFTER trigger from executing when the data does not conform to the restrictions of the constraint. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Transact-SQL Reference, SQL Server Architecture, Database Architecture, Logical Database Components, Constraints Rules Defaults and Triggers. Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_37zi.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers, Designing Triggers, Trigger Execution.
A25:	Answer: A, D. CREATE TABLE, DROP TABLE, ALTER TABLE, GRANT, REVOKE, DISK, ALTER DATABASE, LOAD DATABASE, RESTORE DATABASE, UPDATE STATISTICS, SELECT INTO, LOAD TRANSACTION, and RECONFIGURE are all commands that cannot be used within a trigger. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4xmb.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers, Creating a Trigger.
A26:	Answer: C. An INSERT trigger is the best choice because data is validated as it is inserted. Second, there are no such things as direct and indirect recursion triggers; recursion is something the triggers might do. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4xmb.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers, Creating a Trigger.
A27:	Answer: B. More than one trigger may be placed on a table; however, only one INSTEAD OF trigger, per trigger action, may be placed on a table. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4xmb.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers, Creating a Trigger.
A28:	Answer: C. The CREATE TRIGGER statement is used to create a trigger, and the ALTER TRIGGER statement is used to later modify the trigger. Although many objects can be created within the context of a CREATE TABLE statement, triggers are not one of them. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4xmb.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers, Creating a Trigger.
A29:	Answer: C. Dropping a trigger could have ramifications elsewhere in the environment. Before dropping any object you should also find out what other objects may be effected. You should check for dependent objects when renaming or deleting a trigger. You can also use the Enterprise Manager to visually check object dependencies. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers, Deleting a Trigger. Go to SQL Server Books Online, Transact-SQL Reference, System Stored Procedures, sp_depends. Go to SQL Server Books Online, Transact-SQL Reference, System Stored Procedures, DROP TRIGGER. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_8wj6.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, DROP TRIGGER.
A30:	Answer: D. INSTEAD OF triggers can be used to make non-updatable views capable of supporting updates. The process would not affect the views themselves and would allow for updates to be performed through the code within the trigger. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers, Designing Triggers, Designing INSTEAD OF TRiggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvconusinginsteadoftriggersonviews.asp, MSDN Home, MSDN Library, Development Tools and Languages, Visual Studio .NET, Developing with Visual Studio .NET, Designing Distributed Applications, Visual Database Tools, Developing Database Structure, Working with Triggers, Using INSTEAD OF TRiggers on Views.
A31:	Answer: A. The sp_helptext stored procedure can be used to read the trigger definition. sp_helptrigger is used to find the type or types of triggers defined for a database. sp_displaydef is not a valid system stored procedure. sp_help provides name and type information about various database objects. If a procedure is encrypted, you can not use sp_helptext to obtain the trigger definition. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, System Stored Procedures. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_7c38.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, System Stored Procedures.
A32:	Answer: D. A scalar user-defined function will return a single value. Option A needs to return a list, not a single value; option B would change data, which is not allowed within the context of a scalar UDF; option C is already implemented through a system-defined function. Option D needs to return only a single number, the value of the bill, so it's the best answer. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL User-Defined Functions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_11_5spx.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Transact-SQL Syntax Elements, Using Functions, Invoking User-Defined Functions, Invoking User-Defined Functions That Return Scalar Values.
A33:	Answer: D. Options A, B, and C are all possible using a view. Option A is not possible with a user-defined function. Option D is not possible with a view; a view has a static filter on the output with no recordset. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL User-Defined Functions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_73lf.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User-Defined Functions, Inline User-Defined Functions.
A34:	Answer: B. Because you are using a parameter that drastically affects the output, you want to avoid using the cached plan. Option A doesn't allow enough flexibility in the resultset; option C is simply a developer's preference for the creation of the procedure and would make no difference to the end result. Option D would make minimal difference and adds unnecessary complexity to the process. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, Stored Procedures, Creating a Stored Procedure. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_6cmd.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Stored Procedures, Recompiling a Stored Procedure.
A35:	Answer: A. User-defined functions cannot send email; sending email is a side effect, and user-defined functions are not allowed to have side effects. Depending on how the system process is desired, the functionality can be provided with a trigger, an alert, or a stored procedure. Often the three objects work together to provide this type of capability. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Relational Database Engine Architecture, SQL Server and e-mail Integration. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8xf0.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Managing Servers, SQL Mail.
A36:	Answer: C. This is a very common typographical error to make. return will cause a stored procedure to immediately exit, and statements following the return will not be executed. returns, however, specifies the type of value returned by a function. Such errors are spotted more easily as you do more development. It is common on Microsoft development exams to be faced with questions that will involve analyzing these types of errors. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, RETURN. Go to SQL Server Books Online, Transact-SQL Reference, CREATE FUNCTION. Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Control of Flow, Using RETURN. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_361x.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User-Defined Functions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Building SQL Server Applications, Programming ADO SQL Server Applications, Executing Queries, Executing Stored Procedures, Using Return Code and Output Parameters for Stored Procedures.
A37:	Answer: D. Option A will run without an error, and won't return the result. Option B will run without an error, and capture the return code into the @SalesTax variable, which can't handle the floating-point number that is the @SalesTax. Option C won't work because the first parameter is specified by name, but the rest are specified by position, which is a syntax error. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Using Variables and Parameters. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_04_6e5v.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Accessing and Changing Data Fundamentals, Using Multiple Statements, Using Variables and Parameters.
A38:	Answer: A. Using an IF statement with @@ERROR sends the email. Option B won't send an email and will probably result in a particularly nasty infinite loop; option C won't work because it doesn't send the email on failure, and option D won't work because @@LAST_ERROR isn't a valid system function. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Error Handling, Using @@ERROR. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00f15.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, Error Handling in T-SQL: From Casual to Religious.
A39:	Answer: C. INSERT statements are not allowed within functions because they have the side effect of changing data in a table somewhere. Formatting is not relevant to how well the function operates; the keyword AS is optional in a CREATE FUNCTION statement (but required in a CREATE PROCEDURE); and the function is scalar, not table valued, but that doesn't even matter. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL User-Defined Functions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_50mr.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Logical Database Components, SQL User-Defined Functions.
A40:	Answer: A. If a temporary table is created inside a stored procedure, it is dropped when the stored procedure terminates, so this won't work to get tabular data outside the stored procedure. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, CREATE TABLE. Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Creating and Modifying a Table. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8jtx.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Tables, Creating and Modifying a Table.
A41:	Answer: B. Option A is a type mismatch because trying to put the value 4.3 into an integer doesn't work. Option C has a positional parameter after a named parameter, which won't work, and option D has something as OUTPUT which isn't declared that way. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Transact-SQL Reference, CREATE PROCEDURE. Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Stored Procedures. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Logical Database Components, SQL Stored Procedures.
A42:	Answer: D. Option A is nonsense. Option B won't work because functions can't actually record anything. Option C won't work because cursors aren't used to validate data. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Stored Procedures. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Logical Database Components, SQL Stored Procedures.
A43:	Answer: D. Option A is an incorrect declare statement; option B would be valid for an output parameter, but not a return value; and option C just ignores the whole return-value issue altogether. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Logical Database Components, SQL Stored Procedures. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Architecture, Logical Database Components, SQL Stored Procedures.
A44:	Answer: B. Option A is missing a lot of parentheses; option C is wrong because there isn't a CREATE INLINE FUNCTION statement; and option D is a multiline table-valued function, not an inline. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, User-Defined Functions, Inline User Defined Functions. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_73lf.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, User-Defined Functions, Inline User-Defined Functions.
A45:	Answer: C. Options A and B have the same problem: If the constraints on the tables change, the user-defined function or the stored procedure will need to be rewritten. Option D would be nice; but the constraints are probably there for a reason, and removing them just so that an INSERT can put suspect data into a table is not the correct approach. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Error Handling, Using @@ERROR. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_8oc2.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Advanced Query Concepts, Error Handling.
A46:	Answer: B. This is a perfect example of how partitioning a table into two smaller objects enables you to use two machines to help reduce the load on the overall application. Remember that failover clustering is the only form of clustering supported by SQL and therefore does not actually reduce the load; it only assists in obtaining an around-the-clock operation. Log shipping assists in offloading query load, but does little to reduce update load because it leaves the second server in a read-only state. Merge replication may enable updates to span many servers, but the associated overhead and data latency makes it a less-than-desirable alternative. For more information see Chapter 4, "Advanced Physical Database Implementation." Go to SQL Server Books Online, Creating and Maintaining Databases, Views, Creating a View, Creating a Partitioned View. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_10_2z4x.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Advanced Query Concepts, Using Partitioned Views.
A47:	Answer: D. The INSTEAD OF TRigger was designed specifically for this type of situation and also to handle complicated updates where columns are defined as Timestamp, Calculated, or Identity. Cascade operations are inappropriate because the updated key is not always stored. Indexed views by themselves do not allow for the type of alteration desired and would have to be complemented with the actions of a trigger. Disabling referential integrity is a poor solution to any problem, especially considering the medical nature of this application and the possible ramifications. For more information see Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers, Creating a Trigger, Using INSTEAD OF triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/INSTEADOF.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, INSTEAD OF TRiggers.
A48:	Answer: D. Because the IMAGE data will seldom be accessed, it makes sense to get the remainder of the data away from the images while moving the log away from the data. This will help to improve performance while providing optimum recoverability in the event of a failure. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Physical Database Design, Data Placement Using Filegroups. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_4cj7.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Database Design, Physical Database Design, Data Placement Using Filegroups, Placing Tables on Filegroups.
A49:	Answer: D. Good controllers suitable for database use will have a battery backup. The battery should be regularly tested under controlled circumstances. Disabling caching, if currently in place, is likely to affect performance, as will enabling torn page detection. Torn page detection might help point out whether data is being corrupted because of failures. A maintenance plan is recommended, although it is not an entire solution in its own right. For more information see Chapter 3, "Implementing the Physical Database." Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Modifying a Database, Setting Database Options. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server.
A50:	Answer: A. Because a trigger is already in place, it can easily be altered to perform the additional data check. A rule cannot provide the required functionality because you cannot compare the data. The CHECK constraint may be a viable solution, but you would have to alter the trigger to check for an error and provide for nested operations. The number of triggers firing should be kept to a minimum. To accommodate additional triggers, you would have to check the order in which they are being fired and again set properties of the server and database accordingly. For more information see Chapter 3, "Implementing the Physical Database," and Chapter 6, "Programming Business Logic." Go to SQL Server Books Online, Creating and Maintaining Databases, Enforcing Business Rules with Triggers. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_116g.asp, MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2000, Enforcing Business Rules with Triggers.