When I try to use multiple search terms in a CONTAINS clause I get no results.
SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*,'"Detroit"') AND CONTAINS([Address].*,'"48226"')
OPTION(QUERYTRACEON 9481)
SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*, ' "Detroit" AND "48226" ')
OPTION(QUERYTRACEON 9481)
The first query returns almost 20k rows. The second, which should be logically the same returns nothing. When I try the same test on my 2012 server, it returns the same number of rows, so I'm pretty sure it isn't syntax.
My version is Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) – 12.0.4449.0 (X64)
Apr 13 2016 12:41:07
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )
There is no stopword list set up for this fulltext index.
The QUERYTRACEON 9481 forces the optimizer to use the cardinality estimator for SQL Server 2012 – this solves another issue we have had with FULLTEXT indexes and TOP in 2014, but makes no difference in this case; the queries return the same number of rows with or without it.
Best Answer
Turns out these are not logically the same; the second requires the results to be in the same column, while the first is across all columns