Examen 70-461 V001

16 octubre, 201818 octubre, 2018 JuanCastillo 0 comentarios

Results

#1 You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

Smallmoney

Money

Float

Numeric

Explanation: Numeric is the only one in the list that can give a fixed precision and scale. http://msdn.microsoft.com/en-us/library/ms179882.aspx

#2 Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a self-reference foreign key constraint?

EmployeeID

DepartmentID

FirstName

MiddleName

EmployeeNum

ReportsToID

JobTitle

DateHired

LastName

#3 You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to improve the performance of the view by persisting data to disk. What should you do?

Create a clustered index on the view.

Modify the view to use the WITH VIEW_METADATA clause.

Create an AFTER trigger on the view.

Create an INSTEAD OF trigger on the view.

Explanation:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

#4 The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

IF (XACT_STATE ( ) ) = 0

IF @@TRANCOUNT = l

IF @@TRANCOUNT = 0

IF (XACT_STATE ( ) ) = 1

Explanation:

http://msdn.microsoft.com/en-us/library/ms189797.aspx

http://msdn.microsoft.com/en-us/library/ms187967.aspx

#5 You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

Use a FORCESCAN hint in the query

Update statistics on SalesOrderId on both tables

Use a FORCESEEK hint in the query.

Add a clustered index on SalesOrderId in SalesOrderHeader.

Explanation:

http://msdn.microsoft.com/en-us/library/ms187348.aspx

#6 Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tablescontain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The datain the two tables is distinct from one another. Business users want a report that includes aggregateinformation about the total number of global sales and total sales amounts. You need to ensure that yourquery executes in the minimum possible time. Which query should you use?

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrdersUNIONSELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrdersUNION ALLSELECT SalesOrderId, SalesAmountFROM InternationalSalesOrders) AS p

ELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrdersUNION ALLSELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrdersUNIONSELECT SalesOrderId, SalesAmountFROM InternationalSalesOrders) AS p

Explanation:
http://msdn.microsoft.com/en-us/library/ms180026.aspx
http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-alloptimalperformance-comparison/

http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/

#7 You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

Use the DATE data type.

Use the VARBINARY data type

Use the DATETIME2 data type

Use the FORMAT function.

Use the DATETIMEOFFSET data type.

Use an appropriate collation.

Use the TODATETIMEOFFSET function

Use the DATETIME data type.

Use the CAST function.

Use a user-defined table type.

Explanation:

http://msdn.microsoft.com/en-us/library/ff848733.aspx

http://msdn.microsoft.com/en-us/library/bb630289.aspx

#8 You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItemss

ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse

ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse);

Explanation:

http://technet.microsoft.com/en-us/library/ms190273.aspx

#9 You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

Use the CAST function

Use an appropriate collation

Use a user-defined table type.

Use the DATETIME2 data type

Use the DATE data type

Use the FORMAT function

Use the DATETIME data type

Use the DATETIMEOFFSET data type

Use the VARBINARY data type

Use the TODATETIMEOFFSET function

Explanation:

http://msdn.microsoft.com/en-us/library/ms188362.aspx

#10 You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. You need to display dates and times in several international formats. What should you do?

Use the CAST function.

Use the DATETIMEOFFSET data type.

Use the FORMAT function.

Use a user-defined table type.

Use the TODATETIMEOFFSET function

Use the VARBINARY data type

Use the DATETIME2 data type.

Use the DATETIME data type.

Use the DATE data type

Use an appropriate collation.

Explanation:

http://msdn.microsoft.com/en-us/library/hh213505.aspx

#11 You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table. Which object should you use?

Stored procedure

Scalar user-defined function

Table-valued user-defined function

Inline user-defined function

#12 You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition: You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

Implement page-level compression.

Convert all indexes to Column Store indexes

Implement Unicode Compression.

Implement row-level compression.

Explanation:

http://msdn.microsoft.com/en-us/library/cc280449.aspx

http://msdn.microsoft.com/en-us/library/cc280464.aspx

http://msdn.microsoft.com/en-us/library/cc280576.aspx

http://msdn.microsoft.com/en-us/library/ee240835.aspx

#13 You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

Use a user-defined table type.

Use the TODATETIMEOFFSET function

Use the DATE data type.

Use the CAST function.

Use the DATETIME2 data type.

Use an appropriate collation.

Use the FORMAT function.

Use the VARBINARY data type

Use the DATETIME data type.

Use the DATETIMEOFFSET data type

#14 You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);

CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);

#15 You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

CROSS JOIN

CROSS APPLY

PIVOT

UNPIVOT

Explanation:

http://technet.microsoft.com/en-us/library/ms177634.aspx

#16 You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met: Future modifications to the table definition will not affect the applications’ ability to access data. The new object can accommodate data retrieval and data modification. You need to achieve this goal by using the minimum amount of changes to the existing applications. What should you create for each application?

stored procedures

table partitions

views

table-valued functions

#17 You develop a Microsoft SQL Server 2012 database. You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID. Returns a single integer to the calling application. Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

EXECUTE dbo.GetCustomerRating 1745

DECLARE @CustomerRatingByCustomer INT DECLARE @Result INT EXECUTE @Result = dbo.GetCustomerRating 1745, @CustomerRatingByCustomer

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT AS DECLARE @Result INT SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURNS @Result GO

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN @Result GO

DECLARE @CustomerRatingByCustomer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT

#18 You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?

EXECUTE AS OWNER

USE Database1

USE Database2

EXECUTE AS CALLER

Explanation:

http://msdn.microsoft.com/en-us/library/ms188354.aspx http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-on-linked-server/

#19 Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())

Explanation: Two answers will return the correct results (the «WHERE CONVERT…» and «WHERE … AND … » answers). The correct answer for Microsoft would be the answer that is most «efficient». Anybody have a clue as to which is most efficient? In the execution plan, the one that I’ve selected as the correct answer is the query with the shortest duration. Also, the query answer with «WHERE CONVERT…» threw warnings in the execution plan…something about affecting CardinalityEstimate and SeekPlan. I also found this article, which leads me to believe that I have the correct answer:

http://technet.microsoft.com/en-us/library/ms181034.aspx

#20 A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

Explanation:

http://msdn.microsoft.com/en-us/library/hh213125.aspx

http://msdn.microsoft.com/en-us/library/hh231256.aspx

#21 You are a database developer at an independent software vendor. You create stored procedures thatcontain proprietary code. You need to protect the code from being viewed by your customers. Which storedprocedure option should you use?

ENCRYPTBYKEY

ENCRYPTBYCERT

ENCRYPTION

ENCRYPTBYPASSPHRASE

http://technet.microsoft.com/en-us/library/bb510663.aspx

http://technet.microsoft.com/en-us/library/ms174361.aspx

http://msdn.microsoft.com/en-us/library/ms187926.aspx

http://technet.microsoft.com/en-us/library/ms190357.aspx

http://technet.microsoft.com/en-us/library/ms188061.aspx

#22 You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical. You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders You need to optimize transaction logging and locking for the statement. Which table hint should you use?

XLOCK

HOLDLOCK

TABLOCK

UPDLOCK

ROWLOCK

Explanation:

http://technet.microsoft.com/en-us/library/ms189857.aspx

http://msdn.microsoft.com/en-us/library/ms187373.aspx

#23 You create a stored procedure that will update multiple tables within a transaction. You need to ensure thatif the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. WhichTransact-SQL statement should you include at the beginning of the stored procedure

SET ARITHABORT ON

SET XACT_ABORT ON

BEGIN

TRY

SET XACT_ABORT OFF

SET ARITHABORT OFF

Explanation:

http://msdn.microsoft.com/en-us/library/ms190306.aspx

http://msdn.microsoft.com/en-us/library/ms188792.aspx

#24 You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role

Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

Deny Delete permissions on each table in the Sales schema for each user.

#25 You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

ALTER INDEX ALL ON OrderDetail REBUILD

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

Explanation:

http://msdn.microsoft.com/en-us/library/ms188388.aspx

#26 You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO

CREATE TABLE DocumentStore AS FileTable

CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO

CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO

Explanation:

http://msdn.microsoft.com/en-us/library/gg471497.aspx

http://msdn.microsoft.com/en-us/library/ff929144.aspx

Fin

Results

#1 You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

#3 You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to improve the performance of the view by persisting data to disk. What should you do?

#4 The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

#7 You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

#9 You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

#10 You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. You need to display dates and times in several international formats. What should you do?

#11 You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table. Which object should you use?

#12 You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition: You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

#13 You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

#15 You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

#21 You are a database developer at an independent software vendor. You create stored procedures thatcontain proprietary code. You need to protect the code from being viewed by your customers. Which storedprocedure option should you use?

#26 You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

También te puede gustar

Deja una respuesta Cancelar la respuesta