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.