Exam Questions

1:	You are building a practice database that will be used to perform experiments before moving processing over to the production system on a new SQL Server 2000 computer. The database that you will be implementing is for a pharmaceutical company that operates a wholesale operation over the web and through a catalog ordering system. You have built several new tables with scripts similar to the following: CREATE TABLE ProductTable ( ProductID uniqueidentifier Primary Key Clustered, ProductName varchar(30), CatalogShortID int, CatalogFullID uniqueidentifier, price float ) The only differences in the scripts are the table and column names. Your development team has told you that they are building several generic queries that can be run on this or any number of similar tables throughout the database. Because the column names differ slightly from one table to the next , they would like to build the queries so that the Primary Key values can be extracted without necessarily calling the key column by name . They also require the key to have a unique value supplied by SQL Server upon insertion of new data. They've decided to use the ROWGUIDCOL keyword in their queries. What must be done when the table is created to allow the solution to work? (Select two.) Change the data type of CatalogFullID . Provide a default for the CatalogFullID . Define the CatalogFullID as a ROWGUIDCOL . Change the data type of the ProductID . Provide a default for the ProductID . Define the ProductID as a ROWGUIDCOL .
A1:	E, F. Although any number of columns in a table can be defined with the uniquidentifier data type, only one column can be the ROWGUIDCOL . This single column must be defined in this manner for any procedures to use the ROWGUIDCOL function in place of a column name. To provide a value by the server for a uniqueidentifier data type, you should provide a default definition that uses the NEWID() functions to generate a GUID upon insertion of data. You can find information on data types in Chapter 1, and information on DEFAULT , NEWID() , and ROWGUIDCOL usage in Chapter 3. Also, in SQL Server Books Online you can read more information under the topic heading "Using uniqueidentifier Data."
2:	You have prepared the logical design for a very large database system that will act as the back end for an Internet application, as well as being accessed from the corporate WAN. The data in the system will be spread over a number of databases. You need to support a large number of concurrent users who will be accessing the database across various bandwidth speeds. Which SQL Server technologies could assist in allowing the users access while providing good performance? (Choose all that apply.) Analysis services Replication Partitioned views English query Meta data services
A2:	B, C. Partitioned views and replication enable you to spread the load of a very large database system across several machines. The benefit of additional processing power and getting the data closer to the user could be recognized by both features, assuming they were properly partitioned and configured. Replication is discussed in full in Chapter 11 and partitioned views in Chapter 7. In SQL Server Books Online, the complete background information can be found under the topic "Publishing Data and Database Objects."
3:	You are working as a database administrator for a manufacturing firm that supplies articles constructed of glass to several international firms. In building several new objects in a SQL Server 2000 database, you develop a script to create a set of tables as follows : CREATE TABLE BaseTable ( BaseID bigint identity (45, 17), SupplyID varchar(300) unique, General char(300), Admin char(600), Corpor char(600), LeftBase char(600), RtBase char(300), KitFace char(300) ) CREATE TABLE ReferalTable ( RefID int, SupplyID varchar(300) Foreign Key References BaseTable(SupplyID), Tally int ) What elements in this script are suspect and would not permit the two tables to be created? (Select all that apply.) Varchar fields cannot be used as Foreign Keys. BaseTable rows exceed the maximum allowable row length. There is no evidence the script should encounter problems. The increment of an identity key should be the divisor of the identity's seed. There is no Primary Key for Foreign relationship. The BaseID should be used as the FK relationship.
A3:	C. There is no reason a varchar data type can't be used as a Primary or Foreign Key. It is more pertinent that the Primary Key values be unique and that the Foreign Key relationship be properly defined. The maximum size for a data row in SQL Server is 8,060. We are obviously nowhere near this maximum. The increment of an Identity column can be any desired value. As long as the Foreign Key relationship points to a unique value, it does not have to be declared as a Primary Key. Chapter 3 defines the Foreign Key relationship and the guidelines for its use. SQL Server Books Online has a complete section on defining this activity under "FOREIGN KEY Constraints."
4:	An automobile dealership tracks inventory in a SQL Server database. The database contains information on the autos in stock. A VehicleInventory table is used to hold information for the automobiles in stock. A partial listing of attributes is as follows: VehicleIDNo(20 char) , InvoiceNo(bigint) , Make(20 char) , Model(15 char) , Year(smalldatetime) , Colorcode(int) , PurchasePrice(smallmoney) , StickerPrices(smallmoney) . Which of the columns would you choose as a Primary Key? Use a compound key with Make , Model , and Year . Create a surrogate identity key. Use the VehicleIDNumber as the key. Use the InvoiceNumber as the key. Use a compound key with InvoiceNo and VehicleIDNo .
A4:	B. An automobile's VIN number, though unique, is character data and is much too large to use as an effective Primary Key. This is a perfect situation for an automatically incremented numeric surrogate key that would take up a lot less storage space. A surrogate key may be preferred, because it is small and has no reason to ever change. For information on setting appropriate Primary Keys and supplying the definitions, see Chapters 2 and 3. SQL Server Books Online covers Primary Keys extensively under the topic "PRIMARY KEY Constraints."
5:	You are evaluating the database design given to you by another developer. This database was to be designed with an emphasis on query performance, and an attempt has been made to meet the design goal. Replying to this directive, the developer has sketched out several indexes for the tables. These indexes have been put together keeping in mind the highest expected query usage. As you review his design, you notice that the new indexes will provide varying degrees of benefit to a variety of queries. Which of his indexes is likely to be the most effective? An index on gender for 134,000 registered voters. An index on the sales agent's last initial for 25,000 orders. An index for the StateCode Primary Key column in a US_States table. An index for the State column in a PacificTime_ZIP_Codes table.
A5:	C. Gender is never a good column to supply an index against because it has only two possible values. In general, a column that has the highest percentage of unique values, based on the total number of rows in a table, is the best choice for indexing. An agent's last initial has 26 possibilities in a table of 25,000, which is still a rather poor choice. An index created on the State value would be a good choice in either C or D, but C would provide the best ratio. Index definitions are covered in Chapter 3, and the optimization of indexing strategies is covered in Chapter 10. SQL Server Books Online provides two similar topics under "Indexes" and "Index Tuning Recommendations."
6:	You are working in a database that has an nchar(5) attribute used to store solely numeric data. You believe that an alternate data type may reduce disk space used. You want to use the smallest amount of disk space for storage of this attribute. Which of the following data types would you select? char(5) real smallint int bigint
A6:	D. According to byte sizes, int (4 bytes) would take less than half the space of the current nchar(5) setting (10 bytes). Smallint would even be better, but has an upper limit of 32,767. Using the char(5) data type would cut the space used in half, but using actual numeric storage would be better. Whenever a variable is going to contain only numbers , numeric storage is always more efficient. Chapter 2 covers the complete set of SQL Server data types, and SQL Server Books Online goes into further depth under the topic "Data Types."
7:	You're conducting a class to help some junior-level developers hone their database-development skills. You enter a discussion with them about local and global variables, and are trying to clarify the differences between the two. Which of the following statements can you always make about these two kinds of variables ? (Select two.) Global variables are really functions. All values returned are object-context-specific. They use different characters to denote themselves . All values returned are connection-context-specific. Both of them are available to all connections.
A7:	A, C. In SQL Server 7 and beyond, global variables defined by a prefix @@ are now referred to as global functions and can be accessed through all connections and have an object context. Local variables are defined by a single @ prefix, are connection-context-specific, and are available to the connection of only the current session. Variables are covered in Chapter 6, and SQL Server Books Online has many sections covering this topic. Possibly the most helpful for this situation are "Global Variables" and "Variables: Declare."
8:	As a database implementer, you are creating an historical database for a local library that stores data and photographs about important dates in local history. In the database you will need to be able to store dates from the beginning of 14th century to the current date. You want to minimize the storage space used by the data. Which type of data type would you use? datetime smalldatetime bigint int char(8)
A8:	D. This is a tricky question to solve, and if it were not for the space restriction there would be a temptation to use characters for the storage. At 8 bytes a piece (double that of int ) the easier technique would be to track days from the beginning of recorded time in an integer. (2001 “1300)x365 1/4 would require 6 digits, and therefore int is the closest to the size required. Datetime allows dates to go no further back than the 1700s, and smalldatetime to the 1900s. Chapter 2 covers the complete set of SQL Server data types, and SQL Server Books Online goes into further depth under the topic "Data Types."
9:	You are exploring some of SQL Server 2000's new features. The feature you are particularly interested in is for supporting XML documents. To experiment with the capability to output XML documents, you have developed the following query: USE Northwind GO SELECT t.TerritoryID, e.EmployeeID as EMPLOYEE, e.LastName, e.FirstName, t.EmployeeID FROM Employees e INNER JOIN EmployeeTerritories t ON e.EmployeeID = t.EmployeeID WHERE t.EmployeeID = (SELECT min(EmployeeID) FROM Employees) ORDER BY TerritoryID You are able to submit this script in a Query Analyzer session and return tabular results from the server. What must you do before this query can return its results as an XML document? Remove the ORDER BY clause. Remove the JOIN clause. Remove one or both of the duplicate column headers. Add a clause to the SELECT statement Remove the subquery from the query.
A9:	D. The results of almost any SQL Server SELECT operation can be given in the form of an XML document regardless of the columns, clauses, and other options in the SELECT statement proper. To produce the XML document you must add the FOR XML clause to the SELECT operation and specify the appropriate XML option for the desired output ”either RAW or AUTO. EXPLICIT could alternatively be used if the format of the query was also altered . Chapter 5 deals completely with XML and related operations. SQL Server Books Online covers XML through a variety of topics, beginning with "XML Integration of Relational Data."
10:	You are preparing a database structure for a large local construction company. At any one time the firm has 5 or more active sites, each site having anywhere between 25 and 200 homes . In charge of each site is a site supervisor who organizes the subcontractors at each phase of the building process, including landscaping, framing, drywalling, electrical, plumbing, and so on. Any subcontractor who is planning on working on a given site must be found in a database of approved vendors . The company would like a structure that would allow for storage of the subcontractors' personal information and information about the sites that includes the subcontractors assigned to each site. How would you set up this structure? Site entity and Contractor entity Site entity, Contractor entity, and Site/Contractor entity Site entity, Process entity, Contractor entity Site entity, Contractor entity, and Site/Process entity
A10:	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 information on logical entity design and physical table design, see Chapters 2 and 3. SQL Server Books Online covers relationships in a variety of topics. The most helpful in this situation would likely be "Table Relationships."
11:	As a database implementer, you are developing several new stored procedures on an existing SQL Server 2000 database that resides at your company headquarters. You are experimenting with a Query Analyzer session that contains each of the individual queries to be used in these procedures. After running a given SELECT query and using the Graphical Showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information? Disable the graphical Showplan display. Hold the mouse over each individual node relating to the desired column. Use a SET SHOWPLAN statement. Examine each individual node relating to the desired column without mousing over it.
A11:	B. A feature of the Graphical Showplan display enables you to see information about each node in the display by holding the mouse over the top of the node. This causes a display to show the node analysis based on the contents of the entire query. Of the choices available, this would be the best mechanism to use to gain the desired information. Chapter 12 provides information on using the graphical execution plan display option. SQL Server Books Online covers this topic well under the heading "Graphical Showplan."
12:	A small manufacturing company has a considerable number of data sources because no standardization has occurred across any platform. One of the database servers has SQL Server installed; the others come from a variety of vendors. For a project you are working on you need to gather data from the SQL Server and merge it together with data from two other sources. You then need to bring the data from all sources into Excel where it will later be used for charting purposes. How would you accomplish this? Export the data from the other sources into a comma-delimited file for import to SQL Server. Then export from SQL Server the data that is to be imported into Excel. Export the data from all three sources so that it can be imported into Excel. Use SQL Server to transfer all the data from all sources directly into Excel. Use Excel to transfer data from all three sources into a spreadsheet.
A12:	C. SQL Server is ideal for this situation. Depending on the actual details of the process, this can be performed directly using either replication or data transformation services (DTS). Given the complexity of the scenario, it is likelier that DTS would be used because of its limitless flexibility. DTS is covered in Chapter 5, and replication is in Chapter 11. SQL Server Books Online covers DTS under the topic "Programming DTS Applications," and replication beginning under "Introducing Replication."
13:	You have developed a new database for your company to use when testing new applications. When the database was first created, data recovery was not a major concern. As a result of this oversight, the database initially carried a log file of only 5MB (with autogrow, but no autoshrink) and had its Truncate Log on Checkpoint option set. Midway through tests, however, one of the developers turned the option off, and consequently the log automatically grew to its current size of 150MB. Concerned about the low amount of free space remaining on the disk drive holding the log, you turn the option on once again. What configuration can you expect after the next checkpoint is passed? SQL Server will never truncate this particular log. The log would have been truncated, but the log allocation remains at 150MB. The log would have been truncated, and the log allocation returns to 5MB. The log would have been truncated, and the log allocation would shrink somewhat, but not back to 5MB.
A13:	B. Having the size growing to the 150MB mark will be the size allocated unless the file is shrunk. Truncating and/or backing up the log removes the dead wood from the log, but does not affect the log size. Transaction logs are dealt with fully in Chapter 3, and SQL Server Books Online has a variety of related topics, the best of which in this situation is "Shrinking the Transaction Log."
14:	You are the database administrator for a small private educational institution. You would like to use SQL Server as a gateway to a variety of data sources that have been used for a number of different applications. As a primary goal you would like to get a copy of all data stored on the SQL Server. What technologies would be used to solve this problem? (Select all that apply.) OLE-DB ANSI ISO ODBC Analysis Services SQL Profiler
A14:	A, D. OLE-DB and ODBC are industry-standard technologies for drivers supplied to allow data to be read from an underlying data source. ODBC ( open database connectivity) is a mature interface supported by almost all database engines. OLE-DB is a set of driver APIs that has growing usage and also allows for access to data in a generic form. ANSI and ISO are both organizations and not technologies. This topic is fully covered in Chapters 1, 2, and 3. SQL Server Books Online can also be referenced under the topic "Heterogeneous Data Sources."
15:	You are the developer for a local manufacturing company and are currently working on the sales database. This database is used by three custom applications. Users of these three applications are members of a Windows 2000 domain group . The custom applications will connect to the database using application roles. Each of the roles was assigned a unique password. You need to ensure that the desired users have access to the database through the custom application. What do you do? Assign correct permissions to each Windows 2000 group. Assign correct permissions to each application role. Assign the Windows 2000 group to the appropriate application roles. Provide each user with a password to the appropriate application role.
A15:	B. The custom applications will invoke role-based security for the duration of their execution. When this operation is performed, only the permissions applied to the application role are in effect. Assigning the correct levels of permissions to each of the application roles, therefore, is the only approach that would be successful in this case. Application roles and role-based security are covered in Chapter 6. SQL Server Books Online covers security throughout, but the most relevant topic to this situation is "Application Roles."
16:	A large shipping company uses a dual processor SQL Server to track load information for a fleet of transport vehicles that handle shipments throughout North America. Each shipment carries a variety of goods from fresh produce, to hardware, to automobiles, and so on. The data being collected will be shared with other vendors through a variety of applications. A technology is required that will provide a data structure and formatting rules and be easily transferable between business-to-business applications. Which technology is best suited for this structure? HTML IIS XML Replication Triggers
A16:	C. XML, now supported through a number of new features, provides a mechanism whereby the data can be transmitted from one application to the other, while maintaining the data structure and other formatting provided by XML schemas and style sheets. Chapter 5 deals completely with XML and related operations. SQL Server Books Online covers XML through a variety of topics, beginning with "XML Integration of Relational Data."
17:	Your company supplies building materials to contractors throughout the Midwest. You are the database developer of a SQL Server 2000 computer with dual processors and 1GB RAM. You are working with a database named Policies that holds a variety of corporate documents and data. You have designed a stored procedure, containing a cursor, that will run against the database. An analyst from the IT department in your firm reports that there is an initial delay when the query is executed. After that, the query performance runs well. How can you improve performance? sp_configure "cursor threshold", 0. sp_dboption "Policies" set cursor close on commit on. Set Transaction isolation level to serializable. ALTER db "Policies" set Cursor_default local.
A17:	A. Use of the cursor threshold can enable operations to continue in a procedure while rowsets are being generated asynchronously. If you set the cursor threshold to “1, all keysets are generated synchronously, which benefits small cursor sets. If you set the cursor threshold to 0, all cursor keysets are generated asynchronously. The option should not be set too low in applications that generate small resultsets, because small resultsets are better built synchronously. Configuration options and their use are discussed fully in Chapter 3. You can find additional information on these options in SQL Server Books Online under the topic "Configuration Options Specifications."
18:	You are the administrator of a SQL Server 2000 computer at your company's warehouse. All product orders are shipped out from this warehouse. Orders are received at 30 sales offices throughout the country. Each sales office offers a range of products specific to its own region. Each sales office contains one SQL Server 2000 computer. These servers connect to the warehouse through dial-up connections once a day. Each sales office needs data pertaining to only its own region. You need to replicate inventory data from the server at the warehouse to the servers at the sales offices. You have decided to use transactional replication. You want to minimize the amount of time needed to replicate the data. Which actions should you take? (Choose three.) Create one publication for each subscriber. Create a standard publication for all subscribers. Enable horizontal partitioning. Enable vertical partitioning. Use pull subscriptions. Use push subscriptions.
A18:	A, C, E. It is necessary to design the structure of the database so that the publications can include an article specific to each location. This means you need a compound Primary Key containing regional information and an article for each subscriber that is horizontally partitioned based on the region. Also, because the connection is initiated by the subscriber dial-up, you must set up pull subscriptions. Replication is covered in Chapter 11. SQL Server Books Online covers replication beginning under "Introducing Replication."
19:	You are the database developer for a large scientific research company. You are designing a SQL Server 2000 database that will host a distributed application used by several companies. You create a stored procedure that contains confidential information. You want to prevent the companies from viewing the confidential information, but not hamper their use of the procedure. Remove the text of the procedure from the syscomments table. Encrypt the definition of the stored procedure. Deny SELECT on syscomments for the Public role. Deny SELECT on sysobjects for the Public role. Encrypt the output from the stored procedure.
A19:	B. If you encrypt a stored procedure, the details of the procedure become unreadable to a human being but can still be used and deciphered by SQL Server. Stored procedures are covered fully in Chapter 9, and definition encryption is covered fully in Chapter 3. SQL Server Books Online covers both topics thoroughly. Reference the topic "Stored Procedures" for more information on their use and "With Encryption" for definition encrypting.
20:	Your company has just purchased an accounting application from a vendor. The application stores its data in a database named Accounting . The tables in this database contain columns that function as Primary Keys, but PRIMARY KEY and FOREIGN KEY constraints are not used. You need to replicate data from this database to another SQL Server computer. This server will use the replicated data to generate reports. Most reports will run each month, but the accounting department needs to be able to run reports at any time. Reports should be accurate through the last full working day. You cannot make any changes to the database, but you need to implement replication. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.) Implement merge replication. Implement snapshot replication. Implement transactional replication. Schedule replication to run continuously. Schedule replication to run during off-peak hours.
A20:	B, E. Because there is no Primary Key, and no other changes to the database can be performed, the only alternative that does not require one of these two things to occur is snapshot replication. Because the data does not need to be up-to-the-minute, then a scheduled data refresh occurring overnight or during other non-peak times is most appropriate. Replication is covered in Chapter 11. SQL Server Books Online covers replication beginning under "Introducing Replication."
21:	You are a database developer for an international corporation that ships products worldwide. Andrea, a Visual Basic developer, is also a member of your IT team. Andrea needs to alter views. But you need to prevent her from viewing and changing tables and their data. Currently she belongs to the Public database role. What needs to be done? Add her to the db_owner database role. Add her to the db_ddl admin database role. Grant her Create View permission. Grant her Alter View permission.
A21:	C. The db_owner and ddladmin fixed database roles allow for the creation and alteration of many different kinds of objects within the database. To limit Andrea solely to views, you would have to provide her with that specific permission. There is no such permission as Alter View. Role-based security is covered in Chapter 6. SQL Server Books Online covers security throughout, but the most relevant topic to this situation is "Fixed Database Roles."
22:	You are working for a large scientific research corporation that performs a variety of tests for numerous government agencies. In preparation for a major system upgrade, a large set of data changes are going to be made on the system you administer. You would like to implement a number of changes without affecting any of the existing data. Which of the following operations can be made without affecting any existing data values? (Select all that apply.) INSERT . UPDATE . Change column name. Increase column length. Decrease column length.
A22:	A, C, D. If you were to select UPDATE , the purpose of the command 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, will add data but not alter any existing values. INSERT and UPDATE operations are covered fully in Chapter 4, whereas table definitions are covered in Chapter 3. SQL Server Books Online covers all three topics in full in " UPDATE ," " INSERT ," and "Creating and Maintaining Databases."
23:	You are the database design technician for a large shipping firm that transports goods throughout North America. You are designing an Inventory and Shipping database that will be used as the heart of the corporate order-taking system. You must ensure that referential integrity of the database is maintained . What are the three constraint definitions you would choose? A partial database description is as follows: ORDER Table OrderID PK CustomerID ShipDate PRODUCT Table ProductID PK SupplierID ORDER DETAILS Table OrderID PK ProductID PK SUPPLIER Table SupplierID PK Create a Foreign Key constraint on the Products table that references the Order Details table. Create a Foreign Key constraint on the Products table that references the Supplier table. Create a Foreign Key constraint on the Order table that references the Order Details table. Create a Foreign Key constraint on the Order Details table that references the Order table. Create a Foreign Key constraint on the Order Details table that references the Products table. Create a Foreign Key constraint on the Supplier table that references the Products table.
A23:	B, D, E. According to the partial table definitions, there is a SupplierID in the Products table and a ProductID and OrderID in the Order Details table. The relationships to define, therefore, would be from these tables to the table that contains the remaining information. For information on logical entity design and physical table design, see Chapters 2 and 3. SQL Server Books Online covers relationships in a variety of topics. The most helpful in this situation would likely be "Table Relationships."
24:	As a database implementer, you are creating a one-time report to supply the office staff with a revenue breakdown from the last year of sales. The data source for the report contains cryptic column headings that cover several different categories. You must provide the report in a manner users can easily understand. Which of the following would be the best solution? (Select 2; each answer represents half of the correct solution.) Provide friendly aliases for the table names. Provide friendly aliases for the column names. Create a VIEW with corresponding definition. Create a corresponding DEFAULT definition. Execute a corresponding query from the Analyzer. Create a front-end program to execute the required query.
A24:	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 therefore views would not be warranted. 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. Views are covered in their entirety in Chapter 7. SQL Server Books Online covers this topic in detail under "Views."
25:	You are working as a database designer for an independent consulting firm. A variety of your clients need to support an XML document export from SQL Server. After developing a query capable of producing a working XML document, you would now like to build an XML schema document describing the validation rules for the generated document. What steps would you take to produce only a schema document from it? Add XML notation to the query's column headers. Ensure you have a live Internet connection. Add or extend the WHERE clause. Replace the FOR XML clause with a different clause. Add a keyword to the FOR XML clause.
A25:	E. To have the Schema information output with the XML data document, you must use the XMLDATA option on the FOR XML clause. Without this option, the XML data is output without schema. Chapter 5 deals completely with XML and related operations. SQL Server Books Online covers XML through a variety of topics, beginning with "XML Integration of Relational Data."
26:	You are working for a service organization that provides temporary personnel. You are trying to update the information in a table named Customers . You also need to remove and change records in the Orders and Order Details tables. You execute several attempts at a variety of UPDATE and DELETE queries, each without success. Where would you begin to look for a solution to the problem? (Select all that apply.) You are using a login that does not have the required permissions. A DEFAULT is defined that disagrees with your attempted changes. Referential integrity has been defined on the table structure. You don't have permission to create temporary tables. Backup operations have not yet been performed.
A26:	A, C. Backup should not affect the alterations as SQL Server as backup is performed with the data online, and a DEFAULT should not restrict input. The most likely causes are referential integrity considerations and the permission to perform such updates. Permissions are covered fully in Chapter 6, whereas referential integrity is covered in Chapters 2 and 3. SQL Server Books Online covers both these functions. Security and permissions are related to numerous topics, but start with "Managing Permissions."For information on referential integrity usage, begin with the "Enforcing Referential Integrity Between Tables" topic.
27:	You are working for a large manufacturing firm that produces a variety of goods for sale throughout the continental USA. You are working with a Sales database that has an Inventory table. An application provides new sales information that updates the Inventory table. When the process executes, it gives an error message: Transaction was deadlocked on resources with another process, etc. The stored procedure looks like the following: CREATE PROCEDURE UpdateInventory @IntID int As BEGIN DECLARE @Count int BEGIN TRANSACTION SELECT @Count = Available From Inventory with (HoldLock) WHERE InventoryID = @IntID IF (@Count >0) UPDATE Inventory SET Available = @Count - 1 Where InventoryID = @IntID COMMIT TRANSACTION END Which of the following operations would be most likely to solve the problem and allow the process to complete? Change the table hint to UPDATE . Remove the table hint. Change the table hint to Repeatable Read . Set transaction isolation level Serializable . Set transaction isolation level Repeatable Read .
A27:	A. If locking is altered to an UPDATE level lock when the initial SELECT , you can read data without blocking other processes that need to read data as well. Later in the process you may update it with the assurance that the data has not changed since the SELECT . Locking and hints are covered thoroughly in Chapter 6. SQL Server Books Online covers this same information in the topic "Locking Hints."
28:	As one of several administrators, you support users who have been using a front-end application to INSERT new records into an existing table called Products . Several users report that they are unable to add product information to the table. Which of the following is not likely to be a possible cause of the problem? The database files have had their properties changed. The database properties have been changed. The transaction log is full. Backups are taking place. User permissions have changed.
A28:	D. Database properties, file properties, and user permission changes could all have been implemented by another administrator, which could affect the end users and cause the problem defined. Also, if the transaction log becomes full, the database no longer accepts updates and deletions. Permissions are covered fully in Chapter 6. SQL Server Books Online covers security and permissions are related to numerous topics. Start with "Managing Permissions."
29:	You are designing a database for a retail company that owns 300 stores. Every month, each store submits approximately 1,500 sales records. The sales records are loaded into a SQL Server 2000 database at the corporate headquarters. A DTS package transforms the sales records as they are loaded. The package writes the transformed sales records to the Sales table, which has a column for integer Primary Key values. The IDENTITY property automatically assigns a key value to each transformed record. After loading this month's sales data, you discover that a portion of the data contains errors. You stop the loading of the data to identify and delete the problem records. You want to reuse the key values that were assigned to the records that you deleted. You want to assign the deleted key values to the next sales records you load. You also want to disrupt users' work as little as possible. What should you do? Export all records from the Sales table to a temporary table. Truncate the Sales table, and then reload the records from the temporary table. Export all records from the Sales table to a text file. Drop the Sales table, and then reload the records from the text file. Use the DBCC CHECKIDENT statement to reseed the Sales table's IDENTITY property. Set the Sales table's IDENTITY_INSERT property to ON . Add new sales records that have the desired key values.
A29:	C. The DBCC CHECKIDENT operations check the current identity value for the specified table and, if needed, correct the value. Reseed will set the next ID to the largest current ID + 1 and work in this case only if the records deleted are ones at the end of the ID range. For more information on this and other DBCC operations, refer to Chapter 12. DBCC is covered fully in SQL Server Books Online under "DBCC Overview."
30:	You have entered a query using a TOP function to limit the number of records being viewed to five. When you see the results of the query, the dates being viewed were not the first five in the data. What could be the source of the problem? The resultset has not been grouped. The data contains NULL values. There is an incorrect ORDER . Table aliases were used. Schema binding has been applied.
A30:	C. You are likely 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. ORDER and TOP are covered fully in Chapter 4, or refer to SQL Server Books Online, "Order" and "Top N."
31:	You are implementing a database on a SQL Server 2000 computer. The server contains a database named Inventory . The database contains a table that is used to store information about equipment scheduling. Users report that some equipment schedules have an end date that is earlier than the start date. You need to ensure that the start date is always earlier than or equal to the end date. You also want to minimize physical I/O. You do not want users to change the Transact -SQL statements they use to modify data within the database. What should you do? Create a constraint that compares the start date to the end date. Create a trigger that compares the start date to the end date. Create a rule that compares the start date to the end date. Create a stored procedure that tests the start and end dates before inserting the row into the database.
A31:	A. When given a choice between a constraint, trigger, rule, and stored procedure, the constraint is often the best option. In this case, a constraint catches the error with the least amount of work and overhead on the server. A trigger or stored procedure is more useful when greater functionality is needed. A trigger is also helpful for processes that are reactionary based on data input, deletion, or alterations. A rule is rarely a good option because of its limited functionality and larger overhead. A stored procedure in this case is not valid because of the requirement that the users not change their statements. Otherwise, it is often recommended that programmatic data access (anything that isn't an ad-hoc one-time query) be done through a stored procedure for the purpose of maintainability (stored procedures provide centralized code base), reliability (no chance of mistyping an Insert statement with unfortunate or potentially even tragic results), and speed (precompiled, more reliable reuse of query plans than ad-hoc queries). For more information on the appropriate use of constraints, see Chapters 2 and 3. Triggers are covered in detail in Chapter 8. Stored procedures are dealt with at length in Chapter 9. SQL Server Books Online also covers these options under their respective headings: "Constraints," "Rules," "Triggers," and "Stored Procedures."
32:	As a database design technician, you work for a large manufacturing organization that maintains a large production database system on a single SQL Server 2000 machine. In attempting to enter a query to add a record to a table, you find that it is not possible. Which of the following is not a likely cause for the error? Data doesn't meet constraint. Referential integrity. Database is read-only. Permissions. Other applications are locking data. SQL Server Agent is not started.
A32:	F. Each of the reasons, excluding the Agent, are very possibly a cause of the symptoms being given. The SQL Server Agent handles non-data 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. See Chapters 2 and 3 to look at the variety of options that might prevent updates. SQL Server Books Online has an excellent "Troubleshooting" section that also covers these options.
33:	You are implementing a database on a SQL Server 2000 computer. The server contains a database named Inventory . Users report that several rows in the UnitsStored field contain negative numbers. You examine the database's table structure. You correct all the negative numbers in the table. You must prevent the database from storing negative numbers. Which Transact-SQL statement should you execute? ALTER TABLE dbo.StorageLocations ADD CONSTRAINT CK_StorageLocations_UnitsStored CHECK (UnitsStored >= 0) CREATE TRIGGER CK_UnitsStored On StorageLocations FOR INSERT, UPDATE AS IF (SELECT Inserted.UnitsStored FROM Inserted) < 0 ROLLBACK TRAN CREATE RULE CK_UnitsStored As @Units >= 0 GO sp_bindrule "CK_UnitsStored", "StorageLocations.UnitsStored" GO CREATE PROC UpdateUnitsStored (@StorageLocationID int, @UnitsStored bigint) AS IF @UnitsStored < 0 RAISERROR (50099, 17, 1) ELSE UPDATE StorageLocations SET UnitsStored = @UnitsStored WHERE StorageLocationID = @StorageLocationID
A33:	A. When given a choice between a constraint, trigger, rule, and stored procedure, the constraint is often the best option. In this case, a constraint catches the error with the least amount of work and overhead on the server. A trigger or stored procedure is more useful when greater functionality is needed. A trigger is also helpful for processes that are reactionary, based on data input, deletion, or alterations. A rule is rarely a good option because of its limited functionality and larger overhead. For more information on the appropriate use of constraints, see Chapter 2 and 3. Triggers are covered in detail in Chapter 8. Stored procedures are dealt with at length in Chapter 9. SQL Server Books Online also covers these options under their respective headings: "Constraints," "Rules," "Triggers," and "Stored Procedures."
34:	A local branch of a large hotel chain maintains guest information on a single SQL Server 2000 computer. You are creating an application that will change the contents of a database programmatically through a Visual Basic interface on a local area network. Which technology would you utilize? ADO RDO DAO SQL DMO XML
A34:	A. An XML implementation may be more suited to an active server page, Internet application than a LAN application. RDO and DAO represent older technologies that aren't as efficient and versatile as ADO. SQL-DMO is for development of system applications that interact with SQL Server on a non-data level. See these options in Chapters 1, 2, and 3. Also SQL Server Books Online covers this in the "ADO."
35:	You are implementing a database on a SQL Server 2000 computer. The server contains a database named Inventory . The database has a Parts table that has a field named InStock . When the parts have shipped, a table named PartsShipped is updated. When the parts are received, a table named PartsReceived is updated. You want the database to update the InStock field of the Inventory table automatically. What should you do? Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the Parts table. Create a user-defined function that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables. Use a view that creates an InStock field as part of an aggregate query. Create stored procedures for modifying the PartsShipped and PartsReceived tables that also modify the InStock field in the Parts table. Use these procedures exclusively when modifying data in the PartsShipped and PartsReceived tables.
A35:	A. Triggers are perfect for any automated processing that needs to be done to update, insert, or delete other data based on data activity on the server. Be aware, though, that triggers can also make it hard to diagnose why data is "mysteriously" changing in a table. A stored procedure may have been the best solution in other cases. When all data access is done through stored procedures, the need for triggers is reduced. Admittedly, triggers protect you from an ill-conceived ad-hoc INSERT statement, which stored procedures cannot do; of course, this is mitigated by proper permissions to keep the riff-raff out of the database. Triggers are discussed in depth in Chapter 8. SQL Server Books Online also covers them at length under the "Triggers" topic.
36:	One of the local small merchants has a thriving business with a central manufacturing operation and three retail stores in the surrounding counties. You are preparing a report that will list sales figures for each store, and you would like to calculate subtotals and totals accordingly . Which of the following SELECT clauses will be required? ORDER BY , COMPUTE ORDER BY , COMPUTE BY GROUP BY , COMPUTE GROUP BY , COMPUTE BY ORDER BY , GROUP BY , COMPUTE , COMPUTE BY
A36:	E. ORDER BY and GROUP BY with COMPUTE will result in final totals and that is all. COMPUTE BY requires the use of ORDER BY . To produce an ordered, grouped listing with both intermediate and final totals, all four clauses need to be used. The basic SELECT operation and clauses are covered in Chapter 4. The GROUP and COMPUTE options are covered in Chapter 5. If you begin with the SELECT clause in SQL Server Books Online, you'll find a variety of good examples and ideas on where they can be used.
37:	You are working for a large international distributor of toiletry items. You are implementing a database on a new installation of SQL Server 2000. The server contains a database named Inventory that has a Parts table with a Primary Key that is used to identify each part stored in the company's warehouse. Each part has an unique UPC code that the accounting department uses to identify it. You want to maintain the referential integrity between the Parts table and the OrderDetails table. You want to minimize the amount of physical I/O that is used for access to the database. Which two T-SQL statements should you execute? (Select 2. Each correct answer represents part of the correct solution.) CREATE UNIQUE INDEX IX_PUPC On Parts(UPC) CREATE UNIQUE INDEX IX_ODUPC On OrderDetails(UPC) CREATE TRIGGER INUPOD1 On OrderDetails FOR INSERT, UPDATE As IF NOT EXISTS (SELECT Parts.UPC FROM Parts INNER JOIN Inserted.UPC ON Parts.UPC = Inserted.UPC) BEGIN ROLLBACK TRANSACTION END CREATE TRIGGER INUPP1 On Parts FOR INSERT, UPDATE As IF NOT EXISTS (SELECT OrderDetails.UPC FROM OrderDetails INNER JOIN Inserted ON OrderDetails.UPD = Inserted.UPC) BEGIN ROLLBACK TRANSACTION END ALTER TABLE dbo.OrderDetails ADD CONSTRAINT FK_OrderDetails_Parts FOREIGN KEY(UPC) REFERENCES dbo.Parts(UPC) ALTER TABLE dbo.Parts ADD CONSTRAINT FK_Parts_OrderDetails FOREIGN KEY (UPC) REFERENCES dbo.Parts(UPC)
A37:	A, E. According to the problem description, a Foreign Key relationship is needed from the OrderDetails table to a uniquely defined element in the Parts table. For information on logical entity design and physical table design, see Chapters 2 and 3. SQL Server Books Online covers relationships in a variety of topics. The most helpful in this situation would likely be "Table Relationships."
38:	A greenhouse operation has the unique problem of dealing with live merchandise that is vulnerable to a unique set of circumstances. The database is stored on a single SQL Server 2000 computer that is accessed 24 hours a day from a web site. Periodically, an inventory update procedure alters the categories for various plants and other inventory. The update will encompass 90% of the records in a database that has over 12,000 rows. Which of the following answers represents the best solution? Perform one singular update. Divide the process into smaller batches using GROUPINGS . Vertically partition the data. Use WHERE clauses to perform small batch updates. Create a new database based on data from the old system combined with data from the update.
A38:	D. A single update of this nature would lock the database out for an extended period of time. Because the majority of the data is going to be acted against through the update, and the data is needed around the clock to service the web site, you are probably better off breaking the update into smaller batches. Each batch would process a segment of the data based on WHERE conditions. Batch updates of this nature are covered in Chapter 9. SQL Server Books Online has additional information available under the topic "Effects of Transactions and Batches on SQL Server Performance."
39:	You are working as a database designer for a large U.S.-based scientific research firm. You are working on a DTS package that has been installed on a SQL Server 2000 computer. The DTS package queries multiple databases and writes the results to a text file. This package is run from a Windows 2000 batch file. The batch file uses the dtsrun utility to execute the package. You want to ensure that connection properties, such as login names and passwords, cannot be read or modified by users. Which two actions would you take to ensure this is done? (Select two. Each correct answer represents part of the correct solution.) Save the DTS package so that it has an owner password. Save the DTS package so that it has a user password. Encrypt the package details in the command line of the dtsrun utility. Store the DTS package in the Meta Data Services repository. Store the DTS package as a Microsoft Visual Basic file.
A39:	A, B. By setting an owner and user password, you can control who is allowed to make updates to a DTS package as well as who can execute the process. Chapter 5 deals with DTS operations. SQL Server Books Online has additional material under "DTS."
40:	You work as an application developer for a large warehouse operation. The stock is organized by aisle and bin numbers that are stored in character columns and maintained in a SQL Server 2000 database. You want to prepare a query to list the products by their location (aisle, bin). Which of the following queries would suit the requirements? SELECT (Aisle + Bin) Location, ProductID, ProductName FROM Products ORDER BY Location SELECT (Aisle + Bin) Location, ProductID, ProductName FROM Products GROUP BY Location SELECT (Aisle + Bin) Location, ProductID, ProductName FROM Products GROUP BY Aisle + Bin SELECT (Aisle + Bin) Location, ProductID, ProductName FROM Products GROUP BY Aisle + Bin, ProductID, ProductName SELECT (Aisle + Bin) Location, ProductID, ProductName FROM Products ORDER BY Aisle, Bin
A40:	A. When using an alias for a column, ORDER BY will allow for the actual alias name to be used, whereas GROUP BY requires that the columns be used individually. The basic SELECT operation and clauses are covered in Chapter 4. GROUP and COMPUTE options are covered in Chapter 5. If you begin with the SELECT clause in SQL Server Books Online, you'll find a variety of good examples and ideas on where they can be used.
41:	You are working for a large international firm with headquarters in Miami and regional offices in Detroit, San Francisco, Houston, Boston, and Denver. You are the database designer responsible for a variety of databases stored on a SQL Server 2000 computer. The server contains a database named EmployeeData that your company's human resources department uses for a variety of applications. This database contains several tables that gather and distribute information from all regional offices. The Employees table holds information for employee names, addresses, departments, and base salaries. The Bonuses table records information on bonuses that have been paid to individual employees. The Awards table holds information about awards that have been presented to individual employees. The human resources department wants to make employee names, addresses, and award information available to anyone who has permission to access the server. However, you should allow users in only the human resources department to access salary and bonus information. You need to enable company users to access only the appropriate employee information. How would you accommodate the request? Create a trigger on the Salary column that prevents unauthorized users from making changes to the data in the column. Create a stored procedure that retrieves all the data from the Employees and Awards tables, inserts the data into a temporary table, and then grants the current user SELECT permissions on the temporary table. Grant the Guest database user EXECUTE permissions on the stored procedure. Move the sensitive information out of the Employees table into a new table. Grant the Guest database user SELECT permission on the Employees and Awards tables. Create a view that contains the name, address, and award information. Grant the Guest database user SELECT permissions on the view.
A41:	D. In this situation the benefit of Views really comes out. They are particularly useful in situations where there is sensitive information or a complex data structure. Views are covered in their entirety in Chapter 7. SQL Server Books Online covers this topic in detail under "Views."
42:	You administer the database server for a large lumber and building materials supplier. Your company ships to sites throughout a 500-mile radius from your center of operations. You want to query the materials used at a single site. Which of the following queries would suit your needs? SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site SELECT Materials, Weight, Quantity FROM Inventory WHERE Site = 4 SELECT Materials, Weight, Quantity FROM Inventory GROUP BY Site SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site GROUP BY Materials, Weight, Quantity SELECT Materials, Weight, Quantity FROM Inventory GROUP BY Materials, Weight, Quantity
A42:	B. There is no need for ORDER or GROUP . In this solution, the best query is a simple SELECT query with a WHERE condition for the site. The basic SELECT operation and clauses are covered in Chapter 4. GROUP and COMPUTE options are covered in Chapter 5. If you begin with the SELECT clause in SQL Server Books Online, you'll find a variety of good examples and ideas on where they can be used.
43:	You are working as an independent database consultant for a variety of companies that use a number of different products in all areas of different n- tier systems. You have been asked by one of your clients to develop a process that will import a file from a predetermined server location. The file is a comma-delimited text-based flat file created by a third-party data source. The fields in this flat file have been designed to match exactly with a table stored on the client's SQL Server 2000 system. The client needs the application as soon as possible and wants it to be fast, efficient, and easy to maintain. It must be possible to execute the process through a simple call from within the SQL Server Query Analyzer environment and allow the file to be imported at will. The file is created in such a manner that it is always being appended to. Each night, several thousand rows are added to the end of the file without removing previous data. The SQL Server table holding the imported data is built from this file as well as two other data sources that are processed in a separate procedure. Your solution is to import only new records to the table; that is, it cannot import the same flat-file record twice, nor can it import records that have been added from either of the two other processes. At the end of each import, you need to know how many new records were added from the flat file during the single process. What is the best SQL Server technology to use for this process? Use a SQL script and a BULK INSERT operation. Use the bcp command utility. Build a DTS with T-SQL tasks and standard tasks . Use DTS with standard tasks and transformations. Use a stored procedure and a SELECT INTO operation. Use a stored procedure and an INSERT INTO operation.
A43:	C. Although each of the answers will potentially solve the problem, those easiest to develop, use, and maintain would be those that use DTS processes. Of the two DTS procedures, there are no transformations needed; it is more than just a simple DTS operation, and some standard tasks will be needed. Chapter 5 deals with DTS operations. SQL Server Books Online has additional material under "DTS."
44:	You have several new database designers that have been hired by your company to join several existing development teams. The current teams are working on a variety of projects that use Visual Basic, ASP, DHTML, and Visual C++ for development of internal projects. After each project is completed, it is then turned over to a specialized team that adjusts the project so that it can be resold to other companies as a finished product. You are working with the new employees on aspects of SQL Server 2000 database design and the constructs of all new features. The new employees have a variety of backgrounds in SQL Server and other third-party data sources. You are illustrating some of the standard mechanisms used to design the corporate databases as well as building appropriate error handling into stored procedures. You execute the following script from the SQL Server 2000 Query Analyzer against the Northwind database on your local machine: CREATE PROCEDURE CaseTest AS BEGIN TRANSACTION UPDATE Employees SET LastName = "Jones" WHERE EmployeeID > 0 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END UPDATE Employees SET LastName = "Smith" IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION After you run the procedure, you direct the students to run linked-server queries to your server in a Query Analyzer session and display all rows in Northwind's Employees table. All students report that the LastName value of each employee row is the same value, " smith ", noted to be all in lowercase. All your updates specifically denote a mixed case value. You run your procedure again, with the same result. What is causing the anomaly? Check your SQL Server collation settings. Restart Query Analyzer on the linked servers. ALTER your procedure to correct the conversion error. Check the current settings for the database options. Correct the CONSTRAINT on the Employees table. Correct the INSTEAD OF trigger on the Employees table.
A44:	F. Potentially an INSTEAD OF operation is altering the data before it gets into the table. Conversion of upper, lower, and mixed case would not usually be performed by the other types of potential problems listed. Triggers are discussed in depth in Chapter 8. SQL Server Books Online also covers this at length under the "Triggers" topic.
45:	You are designing a database that will serve as a back end for several large web sites. The web sites themselves will use XML to communicate with each other and pass data back and forth. You would like to control the data displayed on the user browser, based on interactions with the user, without placing any unnecessary load on the server. In many cases, columns and rows need to be eliminated from the display based on the criteria supplied. You would like to minimize round trips to the server for data exchange purposes. What technology is the best to apply? Use a user-defined function with SCHEMABINDING set to the XML recordsets. Create an indexed view of the XML recordset, specifying only the columns needed, and supply a WHERE condition based on the rows selected. Create standard views of SQL Server data and use XML to export the requested data. Use FOR XML and OPENXML options to send data requests and updates directly from the client machine to the SQL Server. Use HTML and an XML schema to provide the necessary view of the data.
A45:	E. SCHEMABINDING refers only to SQL Server objects, specifically 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 to the user on the user's browser. Chapter 5 deals with XML and related operations. SQL Server Books Online covers XML through a variety of topics, beginning with "XML Integration of Relational Data."