Latest 70-433 Real Exam Download 121-130

Ensurepass

QUESTION 121

You administer a Microsoft SQL Server 2008 database named AdventureWork that contains a table named Production.Product.

The table has the following definition: CREATE TABLE [Production].[Product]( [ProductID] [int] IDENTITY(1,1) NOT NULL,

[ProductName] [nvarchar](50) NOT NULL, [ListPrice] [money] NOT NULL, [ProductionDate] [datetime] NULL, [UserCreated] [nvarchar](128) NOT NULL,

[DateCreated] [datetime] NOT NULL DEFAULT GETDATE(), CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC) ON [PRIMARY]) ON [PRIMARY]GO You want to add a new product named Widget and a list price of 10.50 U.S. dollars to the product table. You need to add a record for the product information. You also need to set the DateCreated field to the current date and the UserCreated field to your Windows login identification name. Which Transact-SQL statement should you use?

A. INSERT INTO [Production].[Product](ProductID, ProductName, ListPrice, UserCreated, DateCreated) values(1, ‘Widget’, 10.50, system_user, GETDATE())

B. INSERT INTO [Production].[Product](ProductName, ListPrice, UserCreated) values(‘Widget’,10.50, system_user)

C. INSERT INTO [Production].[Product](ProductName, ListPrice, UserCreated,ProductionDate) values(‘Widget’, 10.50, user_id(), GETDATE())

D. INSERT INTO [Production].[Product](ProductName, ListPrice, DateCreated) values(‘Widget’,10.50, GETDATE())

E. INSERT INTO [Production].[Product](ProductID, ProductName, ListPrice, UserCreated, DateCreated)

select 1, ‘Widget’, 10.50, system_user, GETDATE()

F. INSERT INTO [Production].[Product](ProductName, ListPrice, UserCreated) select ‘Widget’,10.50, system_user

G. INSERT INTO [Production].[Product](ProductName, ListPrice, UserCreated, ProductionDate) select’Widget’, 10.50, user_id(), GETDATE()

H. INSERT INTO [Production].[Product](ProductName, ListPrice, DateCreated) select ‘Widget’,10.50, GETDATE()

Correct Answer: BF


QUESTION 122

You have a table named Person that contains a nvarchar column named Surname. The Person table currently has a clustered index on PersonID. The Surname column contains Russian and Japanese characters. The following code segment will be used to search by Surname.

IF @lang =’Russian’ SELECT PersonID, Surname FROM Person

WHERE Surname = @SearchName COLLATE Cyrillic_General_CI_AS IF @lang = ‘Japanese’ SELECT PersonID, Surname

FROM Person

WHERE Surname = @SearchName COLLATE Japanese_CI_AS_KS You need to enable SQL Server to perform an index seek for these queries. What should you do?

A. Create a computed column for each collation that needs to be searched. Create an index on each computed column.

B. Create a new column for each collation that needs to be searched and copy the data from the

Surname column. Create an index on each new column.

C. Create a computed column for each collation that needs to be searched. Create an index on the Surname column.

D. Create an index on the Surname column.

Correct Answer: A


QUESTION 123

You create a stored procedure that contains proprietary formulas. You need to ensure that no users are able to access Microsoft SQL Server Management Studio to view the definition of the stored procedure. Which code segment should you use?

A. CREATE PROCEDURE Sales.uspFormulaWITH EXECUTE AS SELF

B. CREATE PROCEDURE Sales.uspFormula WITH RECOMPILE

C. CREATE PROCEDURE Sales.uspFormula WITH EXECUTE AS OWNER

D. CREATE PROCEDURE Sales.uspFormulaWITH ENCRYPTION

Correct Answer: D


QUESTION 124

You administer a Microsoft SQL Server 2008 R2 instance configured to use Windows Authentication.

The database contains a table named CustomerTransaction that has the following definition: CREATE TABLE dbo.CustomerTransaction(CustomerTransactionId int NOT NULL PRIMARY KEY, CustomerID int NOT NULL, TransactionAmount money NOT NULL ) You define the following table: CREATE TABLE dbo.CustomerWarningLog(

CustomerWarningLogId int NOT NULL identity PRIMARY KEY, CustomerId int NOT NULL,

Balance money NOT NULL,

LogT ime datetime2(0) NOT NULL,

LogUserName nvarchar(128) NOT NULL) You need to ensure that the following requirements are met:

·An entry is logged in CustomerWarningLog when a customer’s account balance is less than 100.00.

·The TransactionLogUserName is set to the login name of the user who modifies the CustomerTransaction table. Which Transact-SQL statement or statements should you use?

A. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.

CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

IF UPDATE(CustomerID)

WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()

FROM TotalForCustomer WHERE TotalCTE.Amount < 100 GO

B. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateTrigger ON dbo.CustomerTransaction FOR INSERT, UPDATE

AS

IF UPDATE(CustomerID) or UPDATE(TransactionAmount) WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), USER_NAME()

FROM TotalForCustomerWHERE TotalCTE.Amount < 100 GO

C. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

DECLARE @CustomerId int, @Balance money WITH TotalCTE

AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

SELECT @CustomerId = CustomerId,@Balance = TransactionAmount FROM TotalForCustomer

WHERE TotalCTE.Amount < 100

IF @@rowcount > 0

INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogT ime,LogUserName) SELECT @CustomerId, @Balance, SYSDATETIME(), SUSER_SNAME()

GO

D. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

IF UPDATE(CustomerID) or UPDATE(TransactionAmount)

WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()

FROM TotalForCustomer WHERE TotalCTE.Amount < 100 GO

E. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

DECLARE @CustomerId int, @Balance money WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

SELECT @CustomerId = CustomerId,@Balance = TransactionAmount FROM TotalForCustomer

WHERE TotalCTE.Amount < 100

IF @CustomerId IS NULL

INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogTime, LogUserName) SELECT @CustomerId, @Balance, SYSDATETIME(), USER_NAME()

GO

F. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted) GROUP BY CustomerId)

INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()

FROM TotalForCustomerWHERE TotalCTE.Amount <= 100 GO

G. CREATE TRIGGER dbo.CustomerTransaction_UpdateDeleteTrigger ON dbo.CustomerTransaction

FOR UPDATE, DELETE AS

WITH TotalCTE AS

(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE TransactionAmount < 100

AND CustomerId in (SELECT CustomerId

FROM deleted) GROUP BY CustomerId)

INSERT dbo.CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()

FROM TotalForCustomer GO

H. CREATE TRIGGER dbo.CustomerTransaction_InsertUpdateDeleteTrigger ON dbo.CustomerTransaction

FOR INSERT, UPDATE, DELETE AS

WITH TotalCTE AS(SELECT CustomerId, SUM(TransactionAmount) AS Balance FROM dbo.CustomerTransaction

WHERE CustomerId in (SELECT CustomerId

FROM inserted UNION ALL

SELECT CustomerId FROM deleted)

GROUP BY CustomerId)

INSERT dbo. CustomerWarningLog(CustomerId, Balance, LogT ime, LogUserName) SELECT CustomerId, Balance, SYSDATETIME(), SUSER_SNAME()

FROM TotalForCustomer WHERE TotalCTE.Amount < 100 GO

Correct Answer: DH


QUESTION 125

You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction.

You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail.

Which option should you enable?

A. REMOTE_PROC_TRANSACTIONS

B. ARITHABORT

C. XACT_ABORT

D. IMPLICIT_TRANSACTIONS

Correct Answer: C


QUESTION 126

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 observe that the average fragmentation for the AK_Product_ProductNumber index is 24 percent.

You need to reduce fragmentation. You need to achieve this goal without blocking access to table data. Which Transact-SQL statement should you use?

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

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

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

D. ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON

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

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

G. ALTER INDEX AK_Product_ProductNumber ON Production.Product REORGANIZE

H. CREATE STATS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class is not nullWITH SAMPLE 100 PERCENT

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

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

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

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

M. UPDATE STATISTICS Production.Product

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

O. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD

P. ALTER STATISTICS Production.Product

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

Correct Answer: G


QUESTION 127

You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer.

The table has the following definition: CREATE TABLE Customer (CustomerID int NOT NULL PRIMARY KEY,

FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL,

CustomerAddress varchar(1024)) The database also has a table named CustomerExclusionList. Data will be added to the CustomerExclusionList table regularly.

The CustomerExclusionList table has the following definition: CREATE TABLE CustomerExclusionList

(FirstName varchar(255) NOT NULL,

LastName varchar(255) NOT NULL) You need to create a view that returns all records and columns of the Customer table that are not present in the CustomerExclusionList table. Which

Transact-SQL statement should you use?

A. CREATE VIEW vw_ValidCustomer AS SELECT FirstName,LastName

FROM Customer c EXCEPT

SELECT FirstName,LastName FROM CustomerExclusionList

B. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel. LastName

C. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

LEFT OUTER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName

WHERE cel.FirstName IS NULL

D. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName INNER JOIN CustomerExclusionList cel ON c.LastName = cel.LastName

E. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

EXCEPT

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel. LastName

F. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INTERSECT

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel. LastName

G. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

LEFT OUTER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName

WHERE cel.FirstName IS NOT NULL

H. CREATE VIEW vw_ValidCustomer AS

SELECT CustomerID,FirstName,LastName,CustomerAddress FROM Customer c

EXCEPT

SELECT CustomerID,FirstName,LastName,CustomerAddress FROM CustomerExclusionList

Correct Answer: CE


QUESTION 128

You work for a company that provides marketing data to other companies. You have the following Transact-SQL statement: DECLARE @CustomerDemographics XMLSET

@CustomerDemographics=N'<CustomerDemographics>

<Customer CustomerID="1" Age="21" Education="High School">

<IsCoffeeDrinker>0</IsCoffeeDrinker>

</Customer>

<Customer CustomerID="2" Age="27" Education="College">

<IsCoffeeDrinker>1</IsCoffeeDrinker>

<IsFriendly>1</IsFriendly>

</Customer>

<Customer CustomerID="3" Age="35" Education="Unknown">

<IsCoffeeDrinker>1</IsCoffeeDrinker>

<IsFriendly>1</IsFriendly>

</Customer></CustomerDemographics>’DECLARE @OutputAgeOfCoffeeDrinkers XMLSET

@OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query(‘

for $output in /child::CustomerDemographics/child::Customer[( child::IsCoffeeDrinker[1] cast as xs:boolean ? )]

return

<CoffeeDrinkingCustomer>

{ $output/attribute::Age }

</CoffeeDrinkingCustomer>’)SELECT @OutputAgeOfCoffeeDrinkers You need to determine the result of the query. What result should you expect?

A. <CoffeeDrinkingCustomer Age="27" />

<CoffeeDrinkingCustomer Age="35" />

B. <CustomerDemographics>

<Customer>

<CoffeeDrinkingCustomer Age="21"/>

</Customer>

</CustomerDemographics>

C. <CustomerDemographics>

<Customer>

<CoffeeDrinkingCustomer Age="27"/>

</Customer>

<Customer>

<CoffeeDrinkingCustomer Age="35"/>

</Customer>

</CustomerDemographics>

D. <CoffeeDrinkingCustomer Age="21" />

Correct Answer: A


QUESTION 129

You have a SQL Server database. The database contains two schemas named Marketing and Sales. The Marketing schema is owned by a user named MarketingManager. The Sales schema is owned by a user named SalesManager. A user named John must be able to access the Sales.Orders table by using a stored procedure named Marketing.GetSalesSummary. John is not granted a SELECT permission on the Sales.Orders table. A user named SalesUser does have SELECT permission on the Sales.Orders table.

You need to implement appropriate permissions for John and the stored procedure Marketing.GetSalesSummary. What should you do?

A. Marketing.GetSalesSummary should be created by using the EXECUTE AS CALLER clause.John should be granted IMPERSONATE permission for the user named SalesUser.

B. Marketing.GetSalesSummary should be created by using the EXECUTE AS OWNER clause.John should be granted EXECUTE WITH GRANT OPTION on Marketing.GetSalesSummary.

C. Marketing.GetSalesSummary should be created by using the EXECUTE AS ‘SalesUser’ clause.John should be granted EXECUTE permission on Marketing.GetSalesSummary.

D. Marketing.GetSalesSummary should be created without an EXECUTE AS clause.John should be granted SELECT permission on the Sales.Orders table.

Correct Answer: C


QUESTION 130

You use a table that stores movie information in your database. The table has the following schema: CREATE TABLE Movies( MovieID nchar(4) NOT NULL,

T itle nvarchar(25) NOT NULL, MovieDescription nvarchar(50) NULL,

Rating smallint NOT NULL CONSTRAINT Check_Rating CHECK (Rating between 1 AND 7),

CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED (

[MovieID] ASC

))GO You plan to insert a new record for a movie that will be released by using the following information:

Column Data MovieID M999

T itle

Movie to be released MovieDescription

This movie is yet to be released Rating

0

You need to ensure that the following requirements are met:

·Only this movie record gets a Rating of 0.

·Other movie records that will be inserted in the future must get a Rating of 1 through 7. 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.)

Exhibit:

clip_image001

A. ALTER TABLE Movies NOCHECK CONSTRAINT Check_Rating;GO

B. ALTER TABLE Movies WITH CHECK CONSTRAINT Check_Rating;GO

C. ALTER TABLE Movies DROP CONSTRAINT Check_Rating;GO

D. ALTER TABLE Movies CHECK CONSTRAINT Check_Rating;GO

E. ALTER TABLE Movies WITH NOCHECK CONSTRAINT Check_Rating;GO

F. INSERT INTO Movies VALUES (‘M999′,’Movie To Be Released’,’This movie yet to be released’,0)GO

G. INSERT INTO Movies VALUES (‘M999′,’Movie To Be Released’,’This movie yet to be released’, DEFAULT)GO

Correct Answer: ABEFEF

 

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