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
This shouldn’t be right.
FREETEXT and CONTAINS do not support %
CONTAINS supports * wildcard (only at the end of the word, not at the beginning), but the it should be in double quotes.
So correct answer would be C, but with double quotes:
WHERE CONTAINS(notes, ‘”call*”’) AND
If * wildcard is used inside single quotes of CONTAINS predicate the the * wildcard is consired as part of the word: CONTAINS(column, ‘aword*’) will search for the aword* word.
Any wildcard inside single quotes of FREETEXT predicate are removed and only the word is considered to do the search.
So it means its A Right?
Yes, A is the correct answer. You can try it on a database.
use AdventureWorks2008R2;
go
create table table1 (
ID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Notes nvarchar(max) null,
LastContactDate datetime null,
CONSTRAINT PK_table1_id PRIMARY KEY CLUSTERED (ID ASC)
);
GO
INSERT INTO table1 (FirstName, LastName, Notes, LastContactDate) values
(‘John’, ‘Doe’, ‘Common name to desigate a man whose identity is unknown’, ’01/01/2010′);
INSERT INTO table1 (FirstName, LastName, Notes, LastContactDate) values
(‘Jane’, ‘Doe’, ‘Common name to desigate a woman whose identity is unknown’, ’10/05/2010′);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
create fulltext index on table1(Notes)
key index PK_table1_id on ft;
select FirstName + ‘ ‘ + LastName as name
from table1
where freetext(Notes, ‘%man%’) and
LastContactDate >=’1/1/2010′;
drop table table1;
drop fulltext catalog ft;
drop fulltext catalog ft;
When you put in this example such row you will see the difference.
INSERT INTO table1 (FirstName, LastName, Notes, LastContactDate) values
(‘Johna’, ‘Doea’, ‘Common name to desigate a mana whose identity is unknown’, ’01/01/2010′);
i think the correct answer is sth like that
SELECT FirstName + ‘ ‘ + LastName as name
FROM table1
WHERE CONTAINS(Notes, ‘ “man*” ‘);
like here https://msdn.microsoft.com/en-us/library/ms187787.aspx in example C.
Agree with jml and HR_OS_B.The correct answer should be C with double quotes
The answer is A. Microsoft did not make any error in placing call* in single quotes (‘ ‘). For FREETEXT (notes, ‘%call%’), it would search for keywords such as call, calling, called, calls.
If option C was meant to be the correct answer, it would have been properly written in the answer option as ‘ “call*” ‘.
>> For FREETEXT (notes, ‘%call%’), it would search for keywords such as call, calling, called, calls.
But not caller, callllll, etc…
so it’s wrong
A