1.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You intend
to utilize SQL Server 2008 to design a database solution.
A transaction is utilized by a stored procedure in a database to restore
data from a table and aggregations are produced by the procedure.
A solution should be implemented to have the following requirements
satisfied.
When the users are executing stored procedure, the update operations
should not be performed on the restored data.
When the users are executing stored procedure, the insert operations in
the table can be performed.
Since you are the database developer, you are required to make sure that the
requirements can be satisfied by the solution.
From the following four isolation levels, which one should be utilzied?
A: REPEATABLE READ should be utilized
B: SERIALIZABLE should be utilized.
C: READ COMMITTED should be utilized
D: READ UNCOMMITTED should be utilized
Correct Answers: A
MB6-820 VCP-310 642-453
2.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. A database
solution should be designed by utilizing SQL Server 2008. You have a
database, and a table and a table-valued function is included by the
database. The primary key from the table as a parameter is accepted by the
table-valued function accepts. A query should be written, and the query
joins the table to the results of the table-valued function. Since you are
the database administrator, you are required to make sure that only rows
from the table that a result set is produced from the table-valued function
will be returned.
Which join predicate should be utilized to achieve the goal?
A: To achieve the goal, OUTER APPLY should be utilized
B: To achieve the goal, INNER JOIN should be utilized
C: To achieve the goal, CROSS APPLY should be utilized
D: To achieve the goal, LEFT OUTER JOIN should be utilized.
Correct Answers: C
70-294 70-298 70-299
3.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You intend
to utilize SQL Server 2008 to design a database solution.
A table will be contained by the database, and customer data will be stored
in the table as XML data. An application that cannot be varied is supported
by the data.
The following three types of errors in the XML data should be stopped by the
database solution. NULL values in the Customer Name field, Non-numeric
values in the Customer Telephone field, and Invalid values in the Gender
field
The plan should be implemented with no variation of the application.
Which action should you perform to achieve the goal?
A: To achieve the goal, the FileStream data type should be utilized
B: To achieve the goal, the HierarchyID data type should be utilized to
validate data
C: To achieve the goal, the XML data should be saved in a standard table
format. And then, the correct data types, constraints, and NOT NULL
parameters should be specified in the standard table.
D: To achieve the goal, the XML data type should be modified to Typed XML
Correct Answers: D
70-648 mb2-631 MB6-821
4.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You plan
to design a database solution by using SQL Server 2008.
A database contains a table named Person. You can see the structure of the
table from the exhibit below.
The table contains a unique clustered index and two nonclustered indexes.
The unique clustered index is on the PersonID column, and it is named
IX_Person_PersonID.
One nonclustered index is on the FirstName and LastName columns named
IX_Person_FirstName_LastName, while the other nonclustered index is on the
PersonType column named IX_Person_PersonType, and the IX_Person_PersonType
has FirstName and LastName as included columns
About 700,000 records are included by the table. The number of records for
each PersonType is about 3,000.
The following query is performed by you.
SELECT P.FirstName, P.LastName
FROM Person P
WHERE P.PersonType = 'DR'
You intend to utilize an execution plan to analyze the performance of the
query.
You are required to verify that the indexes are utilized optimally.
Which action should be performed?
A: You should confirm that a clustered index scan operation is executed on
the IX_Person_PersonID index
B: You should confirm that an index seek operation is executed on the
IX_Person_PersonType index, and a key lookup operation is executed on the
IX_Person_PersonID index.
C: You should confirm that an index seek operation is executed on the
IX_Person_PersonType index.
D: You should confirm that an index seek operation is executed on the
IX_Person_PersonType index, and an index scan operation is executed on the
IX_Person_FirstName_LastName index
Correct Answers: C
5.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You intend
to utilize SQL Server 2008 to design a database solution.
A query which is regularly utilized costs extremely long time to execute.
You find that full-table scans are frequently utilized by the query rather
than indexes. As a result, other queries that modify the table are blocked.
You can have the indexing strategy changed, and the strategy is on the
underlying tables which are utilized by the query.
A solution should be implemented to perform the tasks listed below.
Full-table scans should be moved, and the query optimizer should be
permitted to select the proper index.
Which action should you perform to finish the task?
A: The INDEX table hint should be utilized
B: The INDEX(0) table hint should be utilized
C: The NOEXPAND table hint should be utilized.
D: The FORCESEEK table hint should be utilized
Correct Answers: D
6.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You
utilize SQL Server 2008 to offer solutions in an enterprise environment.
A table named SalesOrders is included by your online transaction processing
(OLTP) database. A table named factBuyingHabits is included by your data
warehouse. And no indexes are contained by the factBuyingHabits table.
Data should be synchronized between the two tables on a weekly basis. The
synchronization process should meet the requirements listed below.
The company wants to have the new records in the SalesOrders table inserted
in the factBuyingHabits table. And then, the variation should be updated in
the factBuyingHabits table, if the record is varied in the SalesOrders
table. At last, the records should be deleted from factBuyingHabits
table, if they are deleted from the SalesOrders table.
A proper synchronization solution should be implemented. The least amount of
coding and administrative efforts should be utilized to finish the task.
Which action will you perform?
A: An SSIS package should be designed each for the INSERT, UPDATE, and
DELETE operations. And then, a job should be scheduled to run this package
B: One stored procedure should be written, and a MERGE statement is included
by the procedure to have the INSERT, UPDATE, and DELETE operations
performed. And then, a job should be scheduled to run the stored procedure.
C: A single SSIS package should be designed, and the Slowly Changing
Dimension task should be utilized by the single SSIS package. And then, a
job should be scheduled to run this package.
D: Three stored procedures should be written each for the INSERT, UPDATE,
and DELETE operations. And then, a job should be scheduled to run the stored
procedures in a sequential manner.
Correct Answers: B
7.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You intend
to utilize SQL Server 2008 to design a database solution.
There is a table named Transactions in the database application, and
millions of rows are included in the table. Various columns are contained in
the table, such as transaction_id and transaction_date. A clustered index is
contained on the transaction_id column. And a nonclustered index is included
on the transaction_date column.
You find a problrm that it takes a long time to execute the following query.
SELECT transaction_id, transaction_date, transaction_notes
FROM transactions
WHERE transaction_type_id = 'FXO'
AND transaction_date between @start_date and @end_date
You can see the summary of the execution plan from the following code
segment.
|--Filter(WHERE:([transaction_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 are required to make sure that data should be retrieved by the query in
the shortest time.
Which action should be performed by you?
A: A nonclustered index should be created on the transaction_type_id column
B: A nonclustered index should be created on the transaction_date and
transaction_type_id columns and the transaction_notes column should be
included
C: A nonclustered index should be created on the transaction_date and
transaction_type_id columns.
D: A nonclustered index should be created on the transaction_date column and
the transaction_type_id and transaction_notes columns should be included
Correct Answers: B
8.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer.
A database solution should be designed by utilizing SQL Server 2008. Two
schemas are contained, and they are respectively named Sales and Marketing.
The Sales schema is owned by you, while the Marketing schema is owned by a
user named MarketingUser. Users of the Marketing schema are not allowed to
get access to the Sales schema. You are allowed to create objects in all
schemas in the database. There is a table named Customers in the Sales
schema. A stored procedure will be created in the Marketing schema for the
marketing team. Data will be selected from the Customers table by the stored
procedure, and MarketingUser will own the stored procedure. Since you are
the database administrator, you are required to make sure that the stored
procedure should be executed by the marketing team.
Which action will you perform?
A: You should utilize the EXECUTE AS CALLER option to create the procedure
B: You should utilize the EXECUTE AS SELF option to create the procedure.
C: You should utilize the EXECUTE AS OWNER option to create the procedure.
D: You should utilize the EXECUTE AS USER=MarketingUser option to create the
procedure
Correct Answers: B
9.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. There is a 5GB database, and a table named
SalesHistory is contained by the database. Sales information is regularly
inserted and updated. You find a problem that there is too much page
splitting. The display of page splitting in the SalesHistory table should be
minimized. From the following four code segments, which one should be
utilized?
A: ALTER DATABASE Sales MODIFY FILE (NAME = Salesdat3, SIZE =
10GB);
B: EXEC sys.sp_configure 'fill factor (%)', '60';
C: UPDATE STATISTICS Sales.SalesHistory(Commoditys) WITH FULLSCAN,
NORECOMPUTE;
D: ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);
Correct Answers: D
10.You are an IT developer in an international corporation named Wiikigo. In
the company, your major job is associated with databases. You are not only
experienced in creating, and supporting third-party applications by
utilizing SQL Server 2008, but also excellent in writing application code,
T-SQL, and Microsoft .NET code. You work as a database developer. You intend
to utilize SQL Server 2008 to implement a database solution.
A table is contained by the database, and the table includes the following
four commodity inventory information: Department, Class, Item and Quantity
A query should be written, and the query produces the sum of quantity data
broken into the following four groups.
First, Department
Second, Department and Class
Third, Department and Item
Fourth, Department, Class, and Item
The query should be written, and the least number of Transact-SQL statements
should be utilized.
From the following four options, which one should you perform?
A: You should write a single query that contains a GROUP BY clause
B: You should write a single query that contains a GROUP BY GROUPING SETS
clause
C: You should write a single query that contains a GROUP BY WITH CUBE clause
D: You should write a single query that contains a GROUP BY WITH ROLLUP
clause
Correct Answers: B