QUESTION 91
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)
Answer: D
QUESTION 92
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
Answer: C
QUESTION 93
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.
Answer: C
QUESTION 94
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
Answer: BC
QUESTION 95
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);
Answer: B
QUESTION 96
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
Answer: C
QUESTION 97
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
Answer: D
QUESTION 98
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’
Answer: D
QUESTION 99
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’)
Answer: A
QUESTION 100
You need to configure Full-Text Search to ignore specific words. Which Full-Text Search component should you use?
A. iFilter
B. Stoplist
C. Thesaurus file
D. Word breakers
Answer: B
If you want to pass Microsoft 70-433 successfully, donot missing to read latest lead2pass Microsoft 70-433 practice tests.
If you can master all lead2pass questions you will able to pass 100% guaranteed.