Ensurepass

QUESTION 81

You have been tasked to write a query to select one million rows. You need to optimize the query to return the first 50 rows as quickly as possible. What query hint should you use?

 

A.

FAST 50

B.

MAXDOP 50

C.

OPTIMIZE FOR @ROWS=50

D.

TABLE HINT(table, INDEX(50))

 

Correct Answer: A

Explanation:

FAST number_rows

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

 

 

QUESTION 82

Click the exhibit button.

 

image013

 

You have the following query:

 

SELECT EmployeeID, ManagerID, LoginID

 

FROM dbo.Employees

 

WHERE ManagerID = 1500

 

ORDER BY ManagerID;

 

You have been tasked to force the query to use the execution plan in the exhibit.

 

You need to use an appropriate hint to perform the task.

 

Which hint should you use?

 

A.

INDEX(0)

B.

INDEX(1)

C.

INDEX(PK_Employees)

D.

INDEX(IX_Employees)

 

Correct Answer: D

 

 

QUESTION 83

You are working with a SQL Server 2008 instance that is configured to use the

Latin1_General_CS_AS collation. You create a database by using the following statements.

 

CREATE DATABASE TestDB COLLATE Estonian_CS_AS;

 

GO

 

USE TestDB;

 

GO

 

CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );

 

You implement a temporary table named #TestTempTab that uses the following code.

 

use TestDB;

 

GO

 

CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );

 

INSERT INTO #TestTempTab

 

SELECT * FROM TestPermTab;

 

You need to identify which collation will be assigned to #TestTempTab. Which collation will be assigned?

 

A.

No-collation

B.

Estonian_CS_AS

C.

Latin1_General_CS_AS

D.

The collation selected by the Windows system locale of the server

 

Correct Answer: C

Explanation:

When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for the newly created temporary table from the SQL Server instance default.

You can use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

 

 

 

 

QUESTION 84

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 an index on the Surname column.

B.

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

C.

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

D.

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.

 

Correct Answer: C

Explanation:

— Add computed columns with different collations.

ALTER TABLE Person

ADD Surname_RU AS Surname COLLATE Cyrillic_General_CI_AS,

Surname_JP AS Surname COLLATE Japanese_CI_AS_KS;

— Create an index on the computed columns.

CREATE NONCLUSTERED INDEX IX_Person_Surname_RU ON Person (Surname_RU);

CREATE NONCLUSTERED INDEX IX_Person_Surname_JP ON Person (Surname_JP);

GO

 

 

QUESTION 85

You have an application that is used by international clients. All clients connect by using Windows Authentication. You need to ensure that system and user-defined error messages are displayed in the localized language for the clients. What should you do? (Each correct answer represents part of the solution. Choose two.)

 

A.

Use @@LANGUAGE function

B.

Use default language for each login

C.

Use @lang parameter of sp_addmessage

D.

Use the “set language” option of sp_configure

 

Correct Answer: BC

Explanation:

sp_configure is used to specify the default language for all newly created logins. CREATE LOGIN expression has DEFAULT_LANGUAGE = language option. It specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. sp_addmessage stores a new user-defined error message in an instance of the SQL Server Database Engine.

One of the options is ‘language. It is the language for this message, that is the language in which message is written. When language is omitted, the language is the default language for the session.

 

 

QUESTION 86

Your server collation is SQL_Latin1_General_CP1_CI_AS. You have a database named Contoso that has a collation setting of SQL_Scandinavian_Cp850_CI_AS. You create and populate a temporary table #Person from table dbo.Person in Contoso using the following statements:

 

use MyDB;

 

CREATE TABLE #Person (LastName nchar(128));

 

INSERT INTO #Person SELECT LastName FROM dbo.Person;

 

You then run the following command:

 

SELECT * FROM dbo.Person a JOIN #Person b

 

ON a.LastName = b.LastName;

 

This command returns the following error:

 

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Scandinavian_Cp850_CI_AS” in the equal to operation. You need to resolve the collation conflict.

 

Which Transact-SQL statement should you use?

 

A.

CREATE TABLE #Person (LastName nvarchar(128) SPARSE);

B.

CREATE TABLE #Person (LastName nvarchar(128) COLLATE database_default);

C.

CREATE TABLE #Person (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);

D.

CREATE TABLE tmpPerson (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);

 

Correct Answer: B

 

 

QUESTION 87

You have a SQL Server 2008 database. You have not installed a MAPI client. You need to send e-mail from a stored procedure. Which system stored procedure should you use?

 

A.

xp_sendmail

B.

xp_startmail

C.

sp_send_dbmail

D.

sysmail_start_sp

 

Correct Answer: C

 

 

QUESTION 88

You are using Database Mail to deliver email notification and are notified that an employee has not been receiving emails. You need to determine if any email notifications sent by Database Mail have been unsuccessful. Which object from the msdb database should you use?

 

A.

msdb.dbo.sysmail_event_log

B.

msdb.dbo.sysmail_sentitems

C.

msdb.dbo.sysmail_unsentitems

D.

msdb.dbo.sysmail_faileditems

 

Correct Answer: D

Explanation:

sysmail_faileditems

Contains one row for each Database Mail message with the failed status. Use this view to determine which messages were not successfully sent.

 

 

QUESTION 89

You have been tasked to delete a number of Database Mail messages that have been sent. You need to delete all the emails that were sent more than one month ago. Which Transact-SQL statements should you run?

 

A.

DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo

B.

DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo

C.

DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo,’Success’

D.

DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,’Sent’

 

Correct Answer: D

Explanation:

sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ] [ , [ @sent_status = ] ‘sent_status’ ]

Permanently deletes e-mail messages from the Database Mail internal tables.

 

[ @sent_before= ] ‘sent_before’

Deletes e-mails up to the date and time provided as the sent_before argument. sent_before is datetime with NULL as default. NULL indicates all dates.

 

[ @sent_status= ] ‘sent_status’

Deletes e-mails of the type specified by sent_status. sent_status is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed. NULL indicates all statuses.

 

Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete email messages from the Database Mail tables. An optional argument allows you to delete only older e- mails by providing a date and time. E-mails older than that argument will be deleted. Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all messages, use @sent_before = getdate().

 

 

QUESTION 90

You have a table named Books that has columns named BookTitle and Description. There is a full-text index on these columns. You need to return rows from the table in which the word ‘computer’ exists in either column. Which code segment should you use?

 

A.

SELECT *

FROM Books

WHERE FREETEXT(*,’computer’)

B.

SELECT *

FROM Books

WHERE BookTitle LIKE ‘%computer%’

C.

SELECT *

FROM Books

WHERE BookTitle = ‘%computer%’

OR Description = ‘%computer%’

D.

SELECT *

FROM Books

WHERE FREETEXT(BookTitle,’computer’)

 

Correct Answer: A

 

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

Try Microsoft 70-433 Free Demo