Microsoft-PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 Set 1

You intend to import data from an external source into a table. You must make sure that the tasks below are achieved: The rows that fail the foreign key constraints during import are inserted into a separate table. Even if the import encounters rows that fail foreign key constraints, it is successfully completed. So what should you do?

There is a SQL Server 2008 instance which hosts a third-party database. A database application is developed for the instance. You have no permissions to modify the database schema. The database contains two tables that are as shown in the diagram above. On the basis of FullTimeIndicator flag, you intend to extract address information about full-time employees. In order to simplify the extraction process, you have to design a data access layer. So what should you do?

You intend to use SQL Server 2008 to create a database which will be use by a scheduling application. The following information is stored in this database: Rooms Classes Students Teachers You have to consider the following facts in the design plan: Each room can host one or more classes; Each class can be in one or more rooms; Each teacher can teach one or more classes; Each student can register for one or more classes. Look at the entities below: Rooms; Classes; Students; Teachers; ClassesTeachers; ClassesStudents You identify these entities for the database design. In order to ensure normalization, you have to design the database. So what should you do?

You intend to use SQL Server 2008 instance to create a database solution which has some requirements: Import data from various data sources such as Microsoft Office Excel, Microsoft SQL Server 2000, Microsoft SQL Server 2005, and CSV files; Profile the source data before it is imported; Allow mobile users to use heterogeneous data stores; Provide mobile users with collaboration and offline capabilities. In order to achieve these requirements, you have to configure the suitable SQL Server components. When performing this, you have to use as little administrative effort as possible. So which SQL Server components should you use? (choose more than one)

The full-text search component is installed in the database which supports a Web site. You intend to create a table named Courses which has the structure above. On the basis of the CourseTitle field, users of the Web site will search for courses. When the search is launched for a course by a user, a full- text must be constructed to ensure the compliances below: when the exact search phrase is found, rows are returned; rows are in order of how well they match with the search phrase. So what should you specify in the full-text query?

There's a table which is partitioned into four geographic regions in the database. The table is named Selling.Items. Look at the stored procedures below: CREATE STORED PROCEDURE usp_Update @RegionID tinyint AS UPDATE Sales.Inventory SET Qty = T.CurrentQuantity FROM Sales.Inventory I JOIN Sales.TempData T ON I.ItemID = T.ItemID AND I.RegionID = @RegionID; You use the stored procedure above, the UPDATE statement locks the Sales.Inventory table. So what should you do to prevent this from happening?

A database includes a table named Selling. Client order summary information is contained in the Selling table. A stored procedure in created and it uses a SELECT statement and it must return a precise summation of the total sales for the current day when executing. When the stored procedure is being executed, you have to prevent any data modification in the Selling table by using a query hint. So which query hint should you use?

There's a stored procedure in a database. This stored procedure retrieve data from a table and produces aggregations by using a transaction. Now you have to design a solution and it must satisfy the requirements below: When the stored procedure is being executed, insert operations in the table can be performed. When the stored procedure is being executed, update operations cannot be performed on the retrieved data. You must make sure that the requirements can be met. So what isolation level should you use?

There's a query which is used very often. But you notice that this query executes quite slow. This query often uses full-table scans while not indexes. Because of this, other queries that modify the table are to be blocked. On the underlying tables, the indexing strategy that the query uses can change. Now you have to design a solution which removes full-table scans and allows the query optimizer to select the appropriate index. So what should you do?

You are creating a SQL Agent job. This SQL Agent job updates data in two related databases on two different servers by using Transact-SQL. You require that each evening the job can only execute once and it uses transactions to ensure that all updates are rolled back if an error occurs; the databases on each server use the full-recovery model; transaction log backups for the two databases occur at different times. You have to make sure that when a database is restored on either server, the two databases are restored to a state, the state should reflect the last time the job successfully executed. So what should you do?