Latest 70-433 Real Exam Download 81-90

Ensurepass

QUESTION 81

You are tasked with creating a workload that will be used by the Database Engine Tuning Advisor (DTA). You need to create a workload in an appropriate format. Which format should you choose? (Each correct answer represents a complete solution. Choose three.)

A. XML File

B. Transact-SQL Script

C. SQL Server Event Log

D. SQL Server Transaction Log

E. SQL Server Profiler Trace File

F. Performance Counter Log File

Correct Answer: ABE


QUESTION 82

You administer a SQL Server 2008 instance. The instance contains a database table named

Sales.SalesOrderDetail.

The table has the following definition: CREATE TABLE [Sales].[SalesOrderDetail]( [SalesOrderID] [int] NOT NULL,

[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL,

[ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL,

[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))), [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ( [SalesOrderID] ASC,

[SalesOrderDetailID] ASC)WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]) ON [PRIMARY]GO

The table includes the following index: CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] (

[ProductID] ASC) ON [PRIMARY]GO You need to enable row compression for the index. Which Transact-SQL statement or statements should you use?

A. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH (FILLFACTOR = 50)

GO

B. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]REBUILD WITH (DATA_COMPRESSION = ROW) GO

C. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]REORGANIZE WITH (DATA_COMPRESSION = ROW) GO

D. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]REORGANIZE WITH (DATA_COMPRESSION = ON) GO

E. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]REBUILD WITH (ALLOW_ROW_LOCKS = ON, MAXDOP = 50)GO

F. CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)WITH (DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = OFF, DROP_EXISTING

= ON) ON [PRIMARY]GO

G. CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)WITH (DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON, DROP_EXISTING =

OFF) ON [PRIMARY]GO

H. SELECT 50 AS fill_factorFROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Sales.SalesOrderDetail’), NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = object_id AND a.index_id = b.index_idWHEREname=’IX_SalesOrderDetail_ProductID’GO

Correct Answer: BF


QUESTION 83

You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4. Which Transact-SQL statement should you use?

A. NTILE(100)

B. NTILE(25)

C. NTILE(4)

D. NTILE(1)

Correct Answer: C


QUESTION 84

You administer a Microsoft SQL Server 2008 database that includes a table named Products. The Products table has the following schema: CREATE TABLE dbo.Products(

ProductID nchar(4) NOT NULL, ProductName nvarchar(50) NULL, ProductDescription nvarchar(50) NULL, UnitCost money NULL,

UnitPrice money NULL,

CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (

ProductID ASC

)) GO You create a User-Defined Function (UDF) in the same database.

The UDF has the following schema: CREATE FUNCTION dbo.CalculateProductProfit(@ProductID nchar(4))RETURNS MoneyWITH SCHEMABINDINGASBEGIN

DECLARE @Profit Money;

SELECT @Profit = UnitPrice – UnitCost FROM dbo.Products WHERE ProductID = @ProductID; RETURN @Profit;END You need to meet the following requirements:

·Ensure that the UnitPrice column does not accept NULL values.

·Use the CalculateProductProfit() UDF.

·Avoid accidental drop or change of the Product table. Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Build List and Reorder:

clip_image001

Correct Answer:

clip_image002


QUESTION 85

You are required to modify a table named Sales.SalesOrder. The table has change tracking enabled on it. You need to disable change tracking prior to modifying the Sales.SalesOrder table. Which Transact-SQL statement should you use?

A. EXEC sys.sp_cdc_disable_db

B. ALTER TABLE Sales.SalesOrderDISABLE CHANGE_TRACKING

C. ALTER DATABASE ContosoSET CHANGE_TRACKING = OFF

D. EXEC sys.sp_cdc_disable_table@source_schema = N’Sales’,@source_name = N’SalesOrder’,@capture_instance = N’Sales_SalesOrder’

Correct Answer: B


QUESTION 86

You want to delete a User-Defined Function (UDF) named ufnGetProductListPrice from the AdventureWorks2008R2 database. You need to perform the following tasks before executing DROP FUNCTION on ufnGetProductListPrice:

·V iew a list of objects that depend on ufnGetProductListPrice.

·V iew a list of objects on which ufnGetProductListPrice depends. What should you do? (To answer, select the appropriate option or options in the answer area.)

Work Area

Exhibit:

clip_image003

A. This is the Answer

Correct Answer: A


QUESTION 87

You are a database developer for your organization. You create an application that uses a Transact- SQL variable to store user input data. The database collation is case-insensitive. The variable is constructed as shown in the following statement: DECLARE @content varchar(64) SELECT

@content = ‘The Advanced Research Projects Agency Network (ARPANET), was the worlds first operational packet switching network and the core network of a set that came to compose the global Internet.’ You need to implement a keyword search that meets the following requirements:

·Searches for the existence of the word ARPANET within the user-entered content.

·If the search term is found, the statement must return its starting position, and 0 otherwise.

·Performs a case-sensitive search for the given search term. Which Transact-SQL statement should you use?

A. SELECT CHARINDEX(UPPER’arpanet’, @content, 1)GO

B. SELECT CHARINDEX(‘ARPANET’, @content COLLATE Latin1_General_CS_AS)GO

C. SELECT CHARINDEX(‘arpanet’, @content)GO

D. SELECT CHARINDEX(‘ARPANET’, @content, COLLATE Latin1_General_CS_AS)GO

E. SELECT CHARINDEX(@content, ‘ARPANET’, COLLATE Latin1_General_CS_AS)GO

F. SELECT CHARINDEX(‘ARPANET’, @content COLLATE Latin1_General_CS_AS, 0)GO

G. SELECT CHARINDEX(@content COLLATE Latin1_General_CS_AS, ‘ARPANET’, 0)GO

H. SELECT CHARINDEX(@content, ‘ARPANET’)GO

Correct Answer: BF


QUESTION 88

You create a database named AdventureWorks. You want to create a new table to store customer reviews for all products within the database. The table must meet the following requirements:

·Stores information for the Product ID, Customer ID, Rating, and Reviews columns.

·The Reviews column can contain NULL values.

·The Reviews column is optimized to store NULL values. Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Build List and Reorder:

clip_image004

Correct Answer:

clip_image005


QUESTION 89

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 UNCOMMITTED

D. READ COMMITTED

Correct Answer: A


QUESTION 90

You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1. You need to write a query to insert a new order into the Orders table for CustomerID

45 with today’s date and a cost of 89.00. Which statement should you use?

A. INSERT INTO Orders(OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, DEFAULT, 89.00);

B. INSERT INTO Orders(OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, CURRENT_TIMESTAMP, 89.00);

C. INSERT INTO Orders(CustomerId, OrderDate, Cost) VALUES (45, CURRENT_TIMESTAMP, 89.00);

D. INSERT INTO Orders(CustomerId, OrderDate, Cost) VALUES (45, DEFAULT, 89.00);

Correct Answer: C


 

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