You have a database named Database1.
You execute the following code:
You have the following query. (Line numbers are included for reference only.)
Users report that the query takes a long time to complete.
You create a full-text index for the Notes column.
You need to recommend changes to the query to reduce the amount of time it takes for the
query to complete.
Which code segment should you use to replace line 03?
A.
WHERE FREETEXT(notes, ‘%call%’) AND
B.
INNER JOIN FREETEXTTABLE(dbo.table1, notes, ‘call’)
AS t2 ON dbo.table1.ID = t2.key WHERE
C.
WHERE CONTAINS(notes, ‘call*’) AND
D.
WHERE CONTAINS(notes,*%call%’> AND
Explanation:
C
% – not supported
C only search text begin with “call” rather than containing “call” which is the original script intended. B is the right answer
I simulated the case.
B is not the right answer
‘key’ is a reserved keyword (https://msdn.microsoft.com/de-de/library/ms187787.aspx) so the join returnes a syntax error
The following Statement would return rows with ‘caller’ or ‘call’ in the notes column
select FirstName + ‘ ‘ + LastName as name, Notes
from dbo.table1
inner join freetexttable(dbo.table1, notes, ‘caller’)
as t2 on dbo.table1.id = t2.key where
LastContactDate >=’1/1/2010′;
But if you replace ‘Caller’ with ‘Call’
…
inner join freetexttable(dbo.table1, notes, ‘call’)
…
then you will only get the row with the ‘call’ (the row with ‘caller’ would be ignored)
So I think ‘C’ is the right Answer and should read:
select FirstName + ‘ ‘ + LastName as name, Notes
from dbo.table1
where contains(notes, ‘”call*”‘) and
LastContactDate >=’1/1/2010’;
sry as mentioned key is a reserved keyword so you have to write:
…
inner join freetexttable(dbo.table1, notes, ‘caller’)
as t2 on dbo.table1.id = t2.[key] where
…
but this doesn’t effect the results
freetext() and freetexttable() work really close, but they search for work forms (for example it consider “call” and “calls”, but not “caller”).
So it’s not a like “% call%” definitely.
I think correct answer must be C with corrected syntax.