Ensurepass

QUESTION 131

You are given a database design to evaluate. All of the tables in this database should have a clustered index. You need to determine the tables that are missing a clustered index by using the system catalog views. Which Transact-SQL statement should you use?

 

A.

SELECT name AS table_name

FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 0 ORDER BY name;

B.

SELECT name AS table_name

FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 0 ORDER BY name;

C.

SELECT name AS table_name

FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 0 AND OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 1 ORDER BY name;

D.

SELECT name AS table_name

FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 1 AND OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 1 ORDER BY name;

 

Correct Answer: A

 

 

 

QUESTION 132

You need to identify which tables are referenced by name in a stored procedure that does not use dynamic SQL. Which catalog view should you use?

 

A.

sys.procedures

B.

INFORMATION_SCHEMA.TABLES

C.

INFORMATION_SCHEMA.ROUTINES

D.

sys.sql_expression_dependencies

 

Correct Answer: D

 

 

QUESTION 133

You need to manually raise an error.

 

The error message that will be returned will display a message that contains parameters 1, 2, 3

 

sp_addmessage @msgnum = 6000,

 

@severity = 16,

 

@msgtext = N’Your message (%d), (%d), (%d)’;

 

GO

 

You have to raise message to get correct message.. ‘Your message (1), (2), (3).’

 

Which statement will you use?

 

RAISE_ERROR(60000, 16, 1, 1, 2, 3)

 

 

Correct Answer:

RAISE_ERROR(60000, 16, 1, 1, 2, 3)

 

 

QUESTION 134

You are reviewing a trigger in the database which was deployed with the folowing script:

 

EXECUTE AS USER = ‘BuildUser’

 

GO

 

CREATE TRIGGER Inventory.TR_Stock ON Inventory.Stock

 

FOR INSERT, UPDATE, DELETE

 

EXECUTE AS SELF

 

AS …

 

A user ‘WebUser’ insert rows into Inventory.Stock table. You need to identify under which security context the trigger will execute.

 

A.

DBO

B.

Inventory

C.

WebUser

D.

BuildUser

 

Correct Answer: D

 

 

QUESTION 135

Your database contains Products and Orders tables. You need to write a query which return ProductID of the products which have not been placed in any order. Which operator can you use?

 

A.

Union

B.

Union ALL

C.

Intersect

D.

Exclude

 

Correct Answer: D

 

 

QUESTION 136

You are the database developer for an order-processing application.

 

After a customer places an order, a confirmation message must be sent to the customer.

 

The following Transact-SQL batch has been run in the database:

 

image015

 

You need to place the message in the EmailSendQueue for the email system to process.

 

Which Transact-SQL batch should you use?

 

image016

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 137

You administer a Microsoft SQL Server database named AdventureWorks2008R2.

 

The database has a table that has the following definition:

 

image017

 

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:

 

Which Transact-SQL statement should you use?

 

image018

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

 

QUESTION 138

HOTSPOT

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:

 

What should you do? (To answer, select the appropriate option or options in the answer area.)

 

image020

 

Correct Answer:

image022

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 139

You are the administrator of a SQL Server database. Database table modifications and additions must occur only between 11:00 P.M.and midnight. You need to ensure that if database table modifications or additions are attempted at any other time, an error is raised and the attempt is not successful. Which Transact-SQL statement should you use?

 

image023

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

 

QUESTION 140

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:

 

image024

 

The index has the following definition:

 

image026

 

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?

 

image028

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

F.

Option F

G.

Option G

H.

Option H

I.

Option I

J.

Option J

K.

Option K

L.

Option L

M.

Option M

N.

Option N

O.

Option O

P.

Option P

Q.

Option Q

 

Correct Answer: B

 

Instant Access to Download Latest Complete Collection of Microsoft 70-433 Real Exam

Try Microsoft 70-433 Free Demo