Microsoft 70-454 Real Exam Questions

Upgrade: Transition Your MCITP SQL Server 2005 DBD to MCITP SQL Server 2008 DBD

This is an upgrade exam that allows a candidate to upgrade their MCITP Database Developer certification from SQL Server 2005 to SQL Server 2008.

1: You are configuring Service Broker to process messages within a single database. You have performed the following steps. 
1.  CREATE MESSAGE TYPE 
2.  CREATE CONTRACT 
3.  CREATE QUEUE
You need to complete the Service Broker configuration.
What should be the next step?
A.CREATE ROUTE
B.CREATE SERVICE
C.CREATE ENDPOINT
D.CREATE BROKER PRIORITY
Correct Answers: B

2: 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?
A.Get-Item
B.Get-Location
C.Get-ChildItem
D.Get-ItemProperty
Correct Answers: C

3: 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.)
A.nvarchar(100) NULL
B.nvarchar(max) NOT NULL
C.nvarchar(100) NOT NULL
D.nvarchar(100) SPARSE NULL
Correct Answers: A C

4: You need to capture the execution plan for a query. Which statement should you use?
A.SET FORCEPLAN ON;
B.SET SHOWPLAN_XML ON;
C.SET STATISTICS IO ON;
D.SET STATISTICS TIME ON;
Correct Answers: B

5: You are using SQL Server Profiler to gather deadlock information. You need to capture an XML description of a deadlock. Which event should you use?
A.Lock:Deadlock
B.Showplan XML
C.Deadlock Graph
D.Lock:Deadlock Chain
Correct Answers: C

6: You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Which Transact-SQL statement should you use?
A.RANK
B.NTILE(10)
C.DENSE_RANK
D.ROW_NUMBER
Correct Answers: D

7: You are creating a table that stores the GPS location of customers. You need to ensure that the table allows you to identify customers within a specified sales boundary and to calculate the distance between a customer and the nearest store. Which data type should you use?
A.geometry
B.geography
C.nvarchar(max)
D.varbinary(max) FILESTREAM
Correct Answers: B

8: Your database is 5GB and contains a table named SalesHistory. Sales information is 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?
A.ALTER DATABASE Sales   MODIFY FILE      (NAME = Salesdat3,       SIZE = 10GB);
B.ALTER INDEX ALL ON Sales.SalesHistory   REBUILD WITH (FILLFACTOR = 60);
C.EXEC sys.sp_configure ‘fill factor (%)’, ’60’;
D.UPDATE STATISTICS Sales.SalesHistory(Products)   WITH FULLSCAN, NORECOMPUTE;
Correct Answers: B

9: You need to create a stored procedure that accepts a table-valued parameter named @Customers. Which code segment should you use?
A.CREATE PROCEDURE AddCustomers   (@Customers varchar(max))
B.CREATE PROCEDURE AddCustomers   (@Customers Customer READONLY)
C.CREATE PROCEDURE AddCustomers   (@Customers CustomerType OUTPUT)
D.CREATE PROCEDURE ADDCUSTOMERS   (@Customers varchar (max))ASEXTERNAL NAME Customer.Add.NewCustomer
Correct Answers: B

10: You have a transaction that uses the repeatable read isolation level. This transaction causes frequent blocking problems. You need to reduce blocking. You also need to avoid dirty reads and non-repeatable reads. Which transaction isolation level should you use?
A.SNAPSHOT
B.SERIALIZABLE
C.READ COMMITTED
D.READ UNCOMMITTED
Correct Answers: A

Download  |  Password: certificatexam.com