You need to create a column that allows you to create a unique constraint.
Which two column definitions should you choose? (Each correct Answer presents a complete solution. Choose two.)

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 use SQL Server 2008 to design a database that will hold incoming XML responses for an EDI system.
You have the following requirements:
The data is accessible to heterogeneous platforms.
The database stores various types of reports from multiple sources.
The solution allows search by keywords.
The database stores large amounts of data.
The database is scalable.
You need to design the database to meet the given requirements.
What should you do?

You are a database developer. You develop a task management application that connects to a SQL Server 2008 database named TaskDB.
Users log on to the application by using a SQL Server login.The application contains a module named Task that assigns tasks to users. Information about these tasks is stored in the Tasks table of the TaskDB database. The Tasks table contains multiple columns. These include the CloseDate and EstimatedTime columns.
The application must meet the following security requirements:
Users assigned to a database role named User1 can update all task information columns except the CloseDate and the EstimatedTime columns in the Tasks table.
Administrative users assigned to a database role namedTask_Admin can update all task informationin the Tasks table.
You need to design a strategy to meet the security requirements.
Which two actions should you perform? (Each correct Answer presents part of the solution. Choose two.)

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 todesign a database solution by using SQL Server 2008.
A f requently used query takes very long to execute.
You discover that the query frequently uses full-table scans instead of indexes. This causes other queries that modify the table to be blocked.
The indexing strategy on the underlying tables that the query uses can change.
You need to design a solution that performs the following tasks:
Removes fulltable scans
Allows the query optimizer to select the appropriate index.
What should you do?

Your database is 5GB and contains a table named SalesHistory. Sales informationis frequently inserted and updated.
You discover that excessive page splitting is occurring.
You need to reduce the occurrence of page splitting in the SalesHistory table.
Which code segment 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 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?