You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a table that has a column definedas a smallint data type. The table is partitioned on the basis of the smallint column. The partition function, named MyRangePF1, is a range-right function that has boundaries of 100 and 1,000.
The table must be altered to contain the following partitions:
< 100
>= 100 and < 400
>= 400 and < 700
>= 700 and < 1000
>= 1000
You need to alter the partition function to provide the required partitions.
Which code fragment should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain three tables.The structure of the three tables is as shown in the following table.

You need to minimize disk space usage without altering the data types in thetables of the database.
What should you do?

You are using SQL Server Profiler to gather deadlock information.
You need to capture an XMLdescription of a deadlock.
Which event should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain information on retail sales transactions of more than 500 stores.
The marketing department uses the solution to analyze daily sales patterns for each store.Users report that the solution takes a long time to retrieve the required data.
You need to ensure that the solution provides results in the minimum possible time.
What should you do?

You need to capture the execution plan for a query.
Which statement should you use?

You have the following XML document that contains Product information.
DECLARE @prodList xml =’

…
‘;
You need to return a list of products that contains the Product Name, Category, and Price of each product.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a table named Person. The structure of the table is as shown in the following exhibit. ( Click the Exhibit button.)

The table has the following indexes:
A unique clustered index on the PersonID column named IX_Perso n_PersonID
A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName
A nonclustered index on the PersonType column named IX_Person_PersonType that has FirstNameand LastName as included columns
The table contains approximately 700,000 records. The approximate number of records for each PersonType is 3,000.
You execute the following query.
SELECT P.FirstName, P.LastName
FROM Person P
WHERE P.PersonType = ‘DR’
You plan to analyze the performance of the query by using an execution plan.
You need to ascertain that the indexes are used optimally.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a nonclustered index on the transaction_date column.
You discover that the following query takes a long time to execute.
SELECT transaction_id, transaction_date, transaction_notes
FROM transactions WHERE transaction_type_id =’FXO’
AND transaction_date between @start_date and @end_date
The summary of the execution plan is as shown in the following code segment.
|–Filter(WHERE:([transactio n_type_id]=’FXO’)
|–Nested Loops(Inner Join)
|–Index Seek(OBJECT:([transactions]. [nc_transactions_transaction_date])
|–Clustered Index Seek(OBJECT:([transactions]. [PK_transactions_transaction_id])
You need to ensure that the query retrieves data in minimum possible time.
What should you do?

You have a table named Customer that has anXML column named Locations. This column stores anX ML fragment that contains details of one or more locations, as show in the following examples.

You need to write a query that returns a row for each of the customer’s locations. Each resulting row must include the customer name, city, and an XML fragment that contains the location details.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain a table that has a parent child relation ship to it self. Each child might also be aparent. This might exist up to 10 levels deep.
You need to retrieve all levels by using a single Transact-SQL query.
What should you do?