Latest 70-433 Real Exam Download 61-70

Ensurepass

QUESTION 61

You have a server named Contoso with multiple databases. You have been tasked to write a PowerShell script to determine which databases on the server are larger than 100GB.

You open PowerShell from SQL Server Management Studio. You create two variables as follows: PS SQLSERVER:SQLContoso> $MultipleOfGB = 1024 * 1024 PS SQLSERVER:SQLContoso>

$Server = Get-Item

You need to determine which script will produce the desired list of databases. What script should you use?

A. $Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -match 100GB} | Select-Object Name, DatabaseSize

B. $Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -gt 100GB} | Select-Object Name, Size

C. $Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -match 100GB} | Select-Object Name, Size

D. $Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -gt 100GB} | Select-Object Name, DatabaseSize

Correct Answer: B


QUESTION 62

You are updating a database table. You need to partition the table to store only the last 1000 rows of data in the table. What should you do?

A. Create the partition function, the partition scheme, and the distributed partitioned view.

B. Create the partition function and the partition scheme, and update the table.

C. Add a secondary file to the primary filegroups, update the table, and create the distributed partitioned view.

D. Create the partition function, update the table, and create a filtered index.

Correct Answer: B


QUESTION 63

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 named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a

non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

The table 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, [ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO The index has the following definition: CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber] ON [Production].[Product] ( [ProductNumber] ASC) ON [PRIMARY]GO The Production.Product table contains 1 million rows. You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class. You need to refresh statistics on the Production.Product table.

Which Transact-SQL statement should you use?

A. ALTER INDEX AK_Product_ProductNumber ON Production.Product REORGANIZE

B. SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’Production.Product’), NULL, NULL, NULL)

C. ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON

D. UPDATE INDEX AK_Product_ProductNumber ON Production.Product SET (STATISTICS_NORECOMPUTE = ON)

E. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD Partition = 1

F. SELECT * FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Production.Product’), NULL, NULL)

G. SELECT * FROM sys.indexes where name=N’Production.Product’

H. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class <> null

I. EXEC sys.sp_configure ‘index create memory’, 1

J. SELECT * FROM STATS WHERE name=’AK_Product_ProductNumber’

K. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD

L. . UPDATE STATISTICS Production.Product

M. ALTER STATISTICS Production.Product

N. DBCC SHOW_STATISTICS (‘Production.Product’, AK_Product_ProductNumber)

O. SELECT * FROM SYS.STATS WHERE name=’AK_Product_ProductNumber’

P. CREATE STATS ProductClass_Stats ON Production.Product (Name, ProductNumber, Class) WHERE

Class is not null WITH SAMPLE 100 PERCENT

Q. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class is not null

Correct Answer: L


QUESTION 64

You have two tables named dbo.CurrentProducts and dbo.ArchiveProducts. You have the following query: SELECT ProductID, Name FROM dbo.CurrentProductsUNION ALLSELECT ProductID, NameFROM dbo.ArchiveProducts; You need to predict the list of products that the query will produce. Which list of products should the query return?

A. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts but not in both.

B. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.

C. Products that have a matching ProductID and Name in dbo.CurrentProducts or dbo.ArchiveProducts.

D. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed only once.

Correct Answer: B


QUESTION 65

You need to identify which products will be inserted when you execute the following code block. BEGIN TRANSACTION

INSERT INTO Product (ProductName) VALUES (‘food’) BEGIN TRANSACTION

INSERT INTO Product (ProductName)) VALUES (‘beverage’)

COMMIT TRANSACTIONROLLBACK TRANSACTION Which products will be inserted?

A. None

B. Beverage

C. Food and beverage

D. Food

Correct Answer: A


QUESTION 66

You administer a Microsoft SQL Server database named AdventureWorks2008R2. The database has a table that has the following definition: CREATE TABLE Production.Location(

Name nvarchar(100) NOT NULL PRIMARY KEY, StartDate datetime2 NOT NULL

CHECK (StartDate >= ‘2011-01-01’)) You plan to implement custom error handling for INSERT commands. The error number for a duplicate key is 2627. The error number for a NULL violation is 515. You need to ensure that an INSERT statement meets the following requirements:

·If a duplicate row is detected during insertion, no error message must be raised.

·For NULL errors, the prefix "NULL:" must be added to the message; all others errors must begin with "OTHER:".

·Return all errors as error number 50000. Which Transact-SQL statement should you use?

A. INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-18′)

IF @@error = 515

RAISERROR (‘NULL:’ + Error_Message(),16,1)

B. DECLARE @msg nvarchar(2000)

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-18′)

IF @@error = 515 BEGIN

SET @msg = ‘NULL:’ + Error_Message()

RAISERROR (@msg,16,1)ENDELSEBEGINSET @msg = ‘OTHER:’ + Error_Message() RAISERROR (@msg,16,1)END

C. BEGIN TRY

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)

END TRY BEGIN CATCH

IF ERROR_NUMBER() <> 2627 BEGIN

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER()

WHEN 515 THEN ‘NULL:’

ELSE ‘OTHER:’ END + ERROR_MESSAGE()RAISERROR (@msg,16,1)

END END CATCH

D. BEGIN TRY

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)

END TRY BEGIN CATCH

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER(

WHEN 515 THEN ‘NULL:

‘ELSE ‘OTHER:’ END + ERROR_MESSAGE() RAISERROR

(@msg,16,1) END CATCH

E. BEGIN TRY

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)

END TRY BEGIN CATCH

IF ERROR_NUMBER() = 2627 BEGIN

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER()

WHEN 515 THEN ‘NULL:’

ELSE ‘OTHER:’ END + ERROR_MESSAGE() RAISERROR (@msg,16,1)

END END CATCH

F. BEGIN TRY

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2001-10-10′)

END TRY BEGIN CATCH

IF ERROR_NUMBER() <> 2627 BEGIN

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER()

WHEN 515 THEN ‘NULL:’

ELSE ‘OTHER:’ END + ERROR_MESSAGE()RAISERROR 50010 @msg

END END CATCH

G. BEGIN TRY

INSERT Production.Location (Name,StartDate) SELECT ‘Paint’,’2011-10-10′

WHERE NOT EXISTS (SELECT *

FROM Production.Location as L WHERE L.Name = ‘Paint’)

END TRY BEGIN CATCH

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER()

WHEN 515 THEN ‘NULL:’

ELSE ‘OTHER:’ END + ERROR_MESSAGE()RAISERROR (@msg,16,1)

END CATCH

H. BEGIN TRY

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)

END TRY BEGIN CATCH

IF ERROR_NUMBER() = 2627 BEGIN

DELETE FROM Production.Location WHERE Name = ‘Paint’

INSERT Production.Location (Name,StartDate) VALUES (‘Paint’,’2011-10-10′)

END ELSE BEGIN

DECLARE @msg nvarchar(2000)

SET @msg = case ERROR_NUMBER()

WHEN 515 THEN ‘NULL:’

ELSE ‘OTHER:’ END + ERROR_MESSAGE() RAISERROR (@msg,16,1)

END END CATCH

Correct Answer: CG


QUESTION 67

You are the database administrator for an order management system. The database has the following two schemas:

·The dbo schema that is used by the main data processing group

·A Reporting schema that is used by the reporting group The application contains a product table that has the following definition: CREATE TABLE dbo.Product( ProductID INT,

ProductName VARCHAR(100), SalePrice MONEY,

ManufacturerName VARCHAR(150)); The application contains an order table that has the following definition: CREATE TABLE dbo.[Order]( OrderID INT,

ProductID INT, CustomerID INT,

OrderDate DATETIME2); The application also contains a customer table that has the following definition: CREATE TABLE dbo.Customer

(CustomerID INT, CustomerName VARCHAR(100), [Address] VARCHAR(200),

City VARCHAR(100), [State] VARCHAR(50),

ZipCode VARCHAR(5)); You want to load a subset of data from the product and order tables in the dbo schema to a new ProductOrderHistory table in the Reporting schema.

You need to ensure that the table meets the following requirements:

·Includes rows for every product that has been manufactured by "Contoso".

·Includes rows for every product that was ordered from March 1, 2011 to May 31, 2011. The Reporting.ProductOrderHistory columns have the following types: CustomerName VARCHAR(100)ProductName VARCHAR(100)SalePrice MONEYManufacturerName VARCHAR(150)ProductOrderDate DATETIME Which Transact-SQL query should you use?

A. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO Reporting.ProductOrderHistory FROM dbo.product p

LEFT JOIN (dbo.[order] o

INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID) ON p.ProductID = o.ProductID WHERE p.ManufacturerName = ‘Contoso’

OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)

B. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate as ProductOrderDate

INTO Reporting.ProductOrderHistory FROM dbo.product p

INNER JOIN dbo.[order] o ON p.ProductID = o.ProductID INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName = ‘%Contoso%’

OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)

C. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO Reporting.ProductOrderHistory FROM dbo.Product p

LEFT JOIN (dbo.[Order] o

INNER JOIN dbo.Customer c ON o.CustomerID = c.CustomerID) ON p.ProductID = o.ProductID WHERE p.ManufacturerName = ‘Contoso’

OR o.OrderDate BETWEEN ‘20110301’ AND ‘20110531’;

D. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO reporting.ProductOrderHistory FROM dbo.product p

LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID

LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName = ‘Contoso’

OR (o.OrderDate between ‘20110301’ AND ‘20110531 23:59:59.999’);

E. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO TABLE reporting.ProductOrderHistory FROM dbo.product p

LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID

LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName LIKE ‘%contoso%’

OR o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’

F. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate as ProductOrderDate

INTO reporting.ProductOrderHistory FROM dbo.product p

RIGHT JOIN dbo.[order] o ON p.ProductID = o.ProductID INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName = ‘Contoso’

OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)

G. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO reporting.ProductOrderHistory FROM dbo.product p

CROSS JOIN dbo.[order] o ON p.productID = o.productID CROSS JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName = ‘Contoso’

OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)

H. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate

INTO reporting.ProductOrderHistory FROM dbo.product p

LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID

LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID WHERE p.ManufacturerName = ‘Contoso’

OR o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’

Correct Answer: AD


QUESTION 68

You have tables named Sales.SalesOrderDetails and Sales.SalesOrderHeader. You have been tasked to update the discount amounts for the sales of a particular salesperson.

You need to set UnitPriceDiscount to .1 for all entries in Sales.SalesOrderDetail that only correspond to SalesPersonID 290.

Which Transact-SQL statement should you use?

A. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 WHERE EXISTS (SELECT * FROM

Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290);

B. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290;

C. UPDATE d

SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d

INNER JOIN Sales.SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesPersonID = 290;

D. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d WHERE EXISTS (SELECT *

FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290);

Correct Answer: C


QUESTION 69

You have a third-party application that inserts data directly into a table. You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints. You need to ensure that the new columns do not break the third-party application. What should you do?

A. Create an INSTEAD OF INSERT trigger.

B. Create a stored procedure.

C. Create a DDL trigger.

D. Create an AFTER INSERT trigger

Correct Answer: A


QUESTION 70

You are a developer for a Microsoft SQL Server 2008 R2 database instance that hosts an application. You are designing some new tables. You need to choose the most appropriate data types. Which data types should you use? (To answer, drag the appropriate data type to the correct data in the answer area. Each data type may be used once, more than once, or not at all. Each data may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

1. XML

2.

nvarchar(max)

3.

varbinary(max) FILESTREAM

4.

datetime2

5.

timestamp none.gif

DataXML data validated by an XSDDatetimeInternational text data longer than 5,000 charactersXML data that must be indexed and searched by using XPATH queriesXML data that must be preserved exactly as it was receivedMP3 audio filesThe date and time a row was last modifiedHTML fragments

A.

B.

C.

D.

Correct Answer:

 

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