You are a database developer. You plan to design a database solution byusing SQL Server 2008.
The database will contain a table to stage data from an external source.The imported data will contain a field named TransactionTime. The Transaction Time field will use the hh:mm:ss.[m] format (such as 12:55:30.1).
You need to select the appropriate data type for the Transaction Time field. You want to achieve this goal by incurring minimum amount of storage cost.
Which data type 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 have a table named Inventory.
You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider.
PS
SQLSERVER:\SQL\CONTOSO\DEFAULT \Databases\ReportServer\Tables\dbo.Inventory\Columns>
Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table.
Which cmdlet should you use?

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?