Latest 70-433 Real Exam Download 11-20

Ensurepass

QUESTION 11

A database contains tables named Sales and SalesArchive. SalesArchive contains historical sales data. You configure Change Tracking on the Sales table. The minimum valid version of the Sales table is 10. You need to write a query to export only sales data that changed since version 10, including the primary key of deleted rows.

Which method should you use?

A. FROM SalesRIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C …

B. FROM SalesINNER JOIN CHANGETABLE (CHANGES Sales, 10) AS C …

C. FROM SalesRIGHT JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C …

D. FROM SalesINNER JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C …

Correct Answer: A

QUESTION 12

You administer a Microsoft SQL Server 2008 database that contains a stored procedure named dbo.SalesOrderDetails. The stored procedure has following definition: CREATE PROCEDURE dbo.SalesOrderDetails

@CustomerID int,

@OrderDate datetime,

@SalesOrderID intASSELECT h.SalesOrderID,

h.OrderDate, d.OrderQty, d.ProductIDFROM

Sales.SalesOrderHeader h INNER JOIN

Sales.SalesOrderDetail d

ON d.SalesOrderID = h.SalesOrderIDWHERE h.CustomerID = @CustomerID

or h.OrderDate > @OrderDate

or h.SalesOrderID > @SalesOrderIDGO Parameter values passed to the stored procedure largely

vary. You discover that the stored procedure executes quickly for some parameters but slowly for other parameters. You need to ensure that the query plan generated is optimized to provide the most consistent execution times for any set of parameters passed to the stored procedure. Which query hint should you use?

A. OPTION (KEEP PLAN)

B. OPTION (KEEPFIXED PLAN)

C. OPTION (OPTIMIZE FOR UNKNOWN)

D. OPTION (NOLOCK)

E. OPTION (RECOMPILE)

F. OPTION (FAST 25)

G. OPTION (MAXDOP 25)

H. OPTION (ROBUST PLAN)

Correct Answer: CE

QUESTION 13

You notice that a database server is responding slowly to queries. You run the following dynamic management views (DMV) query on the server. SELECT TOP (10)

wait_type,

wait_time_msFROM sys.dm_os_wait_statsORDER BY wait_time_ms DESC; The query returns a

top wait type of SOS_SCHEDULER_YIELD. You need to identify what is causing the server response issues. Which resource should you investigate first?

A. Memory

B. Network

C. CPU

D. Disk

Correct Answer: C

QUESTION 14

You administer a Microsoft SQL Server 2008 database that contains a table named dbo.[order]. There are no triggers on the table. You plan to create a stored procedure that will have the following parameters:

·@ProdId int

·@CustId int You need to ensure that the following requirements are met:

·The OrderID and ProdID values of each modified row are captured into a local table variable before data is modified.

·The ProdID is modified to @ProdID where CustID is equal to @CustId. Which Transact-SQL statement should you use?

A. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SET ProdID =@ProdId

OUTPUT DELETED.OrderID, DELETED.ProdID

INTO @OrderIDsWHERECustID = @CustId;

B. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SET ProdID = @ProdId

WHERE CustID = @CustId; INSERT into @OrderIDs

SELECT SCOPE_IDENTITY(OrderId), SCOPE_IDENTITY(ProdId);

C. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SETProdID = @ProdId

OUTPUT INSERTED.OrderID, INSERTED.ProdID

WHERE CustID = @CustId;

D. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SETProdID = @ProdId

OUTPUT INSERTED.OrderID, INSERTED.ProdID

INTO @OrderIDs

WHERE CustID = @CustId;

E. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SET ProdID = @ProdId

OUTPUT SELECT d.OrderID, d.ProdID FROM DELETED d

INTO @OrderIDs

WHERE CustID = @CustId;

F. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE dbo.[order] SET ProdID = @ProdId

OUTPUT INSERTED.OrderID, DELETED.ProdID

INTO @OrderIDs

WHERE CustID = @CustId;

G. UPDATE [order]

SET ProdID = @ProdId

OUTPUT DELETED.OrderID, DELETED.ProdID

INTO @OrderIDs (OrderID INT, ProdID INT) WHERE CustID = @CustId;

H. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);

UPDATE [order]

SET ProdID = @CustId

OUTPUT #INSERTED.OrderID, #INSERTED.ProdID

INTO @OrderIDs

WHERE CustID = @CustId;

Correct Answer: AF

QUESTION 15

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:SQLCONTOSODEFAULTDatabasesReportServerTablesdbo.InventoryColumns>

Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table. Which cmdlet should you use?

A. Get-ChildItem

B. Get-ItemProperty

C. Get-Location

D. Get-Item

Correct Answer: A

QUESTION 16

You need to ensure that tables are not dropped from your database. What should you do?

A. Create a DML trigger that contains ROLLBACK.

B. Create a DML trigger that contains COMMIT.

C. Create a DDL trigger that contains COMMIT.

D. Create a DDL trigger that contains ROLLBACK.

Correct Answer: D

QUESTION 17

Note: This
QUESTION is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one
QUESTION in the series. You administer a Microsoft SQL Server 2008 database for an inventory management system.

The application contains a product table that has the following definition: CREATE TABLE [Production].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, [Color] [nvarchar](15) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL, [Active] [bit] NOT NULL, [ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO You want to add a new field to the Product table to meet the following requirements:

·Allows user-specified information that will be added to records in the Product table.

·Supports the largest storage size needed for the field.

·Uses the smallest data type necessary to support the domain of values that will be entered by users. You need to add a field named User_Data_1 to support integer values ranging from –10 through 10.

Which SQL statement should you use?

A. ALTER TABLE [Production].[Product] ADD [User_Data_1] VARCHAR(100)

B. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT

C. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11,6)

D. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT

E. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME

F. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT

G. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMONEY

H. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLDATETIME

I. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)

J. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME2

K. ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY

L. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT

M. ALTER TABLE [Production].[Product] ADD [User_Data_1] CHAR(100)

N. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATE

O. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT

P. ALTER TABLE [Production].[Product] ADD [User_Data_1] NVARCHAR(100) Q ALTER TABLE [Production].[Product] ADD [User_Data_1] NCHAR(100)

Q. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6,11)

Correct Answer: F

QUESTION 18

You need to round the value 1.75 to the nearest whole number. Which code segment should you use?

A. Select ROUND(1.75,1.0)

B. Select ROUND(1.75,2)

C. Select ROUND(1.75,0)

D. Select ROUND(1.75,2.0)

Correct Answer: C

QUESTION 19

You are the database developer for an order-processing application. The database has the following tables: CREATE TABLE dbo.Product (ProdID INT NOT NULL PRIMARY KEY, ProdName VARCHAR(100) NOT NULL,

SalePrice MONEY NOT NULL,

ManufacturerName VARCHAR(100) NOT NULL); CREATE TABLE dbo.Customer (CustID INT NOT NULL PRIMARY KEY,

CustName VARCHAR(100) NOT NULL, CustAddress VARCHAR(200) NOT NULL, CustCity VARCHAR(100) NOT NULL, CustState VARCHAR(50) NOT NULL,

CustPostalCode VARCHAR(5) NOT NULL); CREATE TABLE dbo.[Order](OrderID INT NOT NULL PRIMARY KEY,

ProdID INT NOT NULL

REFERENCES dbo.Product(ProdId), CustID INT NOT NULL

REFERENCES dbo.Customer(CustId),

OrderDate DATETIME NOT NULL); You need to ensure that the following requirements are met:

·Data is loaded into the tables.

·Data that has been inserted will be removed if any statement fails.

·No open transactions are performed after the batch has executed. Which Transact-SQL statements should you use?

A. BEGIN TRY

SAVE TRANSACTION DataLoad

INSERT INTO dbo.Product

VALUES (1, ‘Chair’, 146.58,’Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4, ‘Desk’,1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES(1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); INSERT INTO dbo.[Order]

VALUES (1, 1, 2,’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′); COMMIT TRANSACTION DataLoad;

END TRY BEGIN CATCH

IF @@TRANCOUNT > 0 BEGIN

ROLLBACK TRANSACTION DataLoad;

END; END CATCH;

B. BEGIN TRY

BEGIN TRANSACTION

INSERT INTO dbo.Product

VALUES (1, ‘Chair’, 146.58,’Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4, ‘Desk’,1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); INSERT INTO dbo.[Order]

VALUES (1, 1, 2,’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′); COMMIT TRANSACTION;

END TRY BEGIN CATCH

IF @@TRANCOUNT > 0 BEGIN

ROLLBACK TRANSACTION; END;

END CATCH;

C. BEGIN TRY

BEGIN TRANSACTION customers; INSERT INTO dbo.Product VALUES(1, ‘Chair’,46.58, ‘Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4,’Desk’, 1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES(1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3,’Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); INSERT INTO dbo.[Order]

VALUES(1,1, 2, ’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′);

SAVE TRANSACTION customers; END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 BEGIN

ROLLBACK TRANSACTION customers;

END; END CATCH;

D. BEGIN TRY

BEGIN TRANSACTION

INSERT INTO dbo.Product VALUES(1, ‘Chair’, 146.58,’Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4, ‘Desk’,1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); INSERT INTO dbo.[Order]

VALUES (1, 1, 2,’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′); SAVE TRANSACTION;

END TRY BEGIN CATCH

IF @@TRANCOUNT > 0 BEGIN

ROLLBACK TRANSACTION; END;

END CATCH;

E. BEGIN TRY

BEGIN TRANSACTION

INSERT INTO dbo.Product VALUES(1, ‘Chair’, 146.58,’Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4, ‘Desk’,1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); INSERT INTO dbo.[Order]

VALUES (1, 1, 2, ’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′);

END TRY BEGIN CATCH

IF @@TRANCOUNT > 0 BEGIN

ROLLBACK TRANSACTION;

END; END CATCH;

F. BEGIN TRANSACTION

INSERT INTO dbo.Product

VALUES (1, ‘Chair’, 146.58, ‘Contoso’),

(2, ‘Table’,458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),

(4, ‘Desk’, 1483.25, ‘Northwind Traders’); IF @@ERROR > 0

ROLLBACK TRANSACTION;

INSERT INTO dbo.Customer

VALUES(1,’John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’,’OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’); IF @@ERROR > 0

ROLLBACK TRANSACTION;

INSERT INTO dbo.[Order] VALUES (1, 1, 2, ’09/15/2011′),

(2, 4, 2,’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3, ’10/02/2011′);

IF @@ERROR > 0

ROLLBACK TRANSACTION;

COMMIT TRANSACTION;

G. SET XACT_ABORT ON BEGIN TRANSACTION

INSERT INTO dbo.Product

VALUES (1, ‘Chair’, 146.58,’Contoso’),

(2, ‘Table’, 458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’), (4, ‘Desk’,1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);

INSERT INTO dbo.[Order] VALUES (1, 1, 2,’09/15/2011′),

(2, 4, 2, ’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3,’10/02/2011′); COMMIT TRANSACTION;

H. BEGIN TRANSACTION

INSERT INTO dbo.Product

VALUES (1, ‘Chair’, 146.58, ‘Contoso’),

(2, ‘Table’,458.36, ‘Contoso’),

(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),

(4, ‘Desk’, 1483.25, ‘Northwind Traders’);

INSERT INTO dbo.Customer

VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’), (2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),

(3, ‘Fred Thomson’, ‘100 Park Ave’,’San Francisco’, ‘CA’, ‘94172’);

INSERT INTO dbo.[Order] VALUES (1, 1, 2, ’09/15/2011′),

(2, 4, 2,’09/15/2011′),

(3, 2, 1, ’08/17/2011′),

(4, 2, 3, ’07/01/2011′),

(5, 3, 3, ’10/02/2011′);

IF @@ERROR >0

ROLLBACK TRANSACTION ELSE

COMMIT TRANSACTION;

Correct Answer: BG

QUESTION 20

You are a developer for a Microsoft SQL Server 2008 R2 database instance. You want to add functionality to an existing application that uses the Microsoft SQL Server Profiler tool to capture trace information. You need to ensure that the following requirements are met:

·Users are able to use the SQL Server Profiler tool.

·Users are able to capture trace information and read saved trace files.

·Users are granted the minimum permissions to achieve these goals. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Build List and Reorder:

Correct Answer:

Download Latest 70-433 Real Free Tests , help you to pass exam 100%.