question

Everywhere I look I find it categorically stated that when FullText searching SQLServer is Case-Insensitive. However, I've found a reproducible example of where it is seemingly not. In the below code, only 2 of the 3 " asp.net" entries are returned where as all "test" entries are returned by the contains statement. Has anyone seen this type of behaviour before? And more to the point can anyone explain it? Many Thanks, James USE AdventureWorks2012 CREATE TABLE CaseSensitivtyTest ( ID INT IDENTITY(1, 1) , Textcol VARCHAR(MAX) ) CREATE UNIQUE INDEX CaseSensitivtyTestIndex ON CaseSensitivtyTest(ID); CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON CaseSensitivtyTest(Textcol) KEY INDEX CaseSensitivtyTestIndex WITH STOPLIST = SYSTEM; INSERT INTO dbo.CaseSensitivtyTest ( Textcol ) VALUES ( ' asp.net' ) , ( ' Asp.Net' ), ( ' ASP.NET' ) --Wait two seconds for the FT catalog to catch up SELECT * FROM CaseSensitivtyTest WHERE CONTAINS ( textcol, ' ASP.NET' ) INSERT INTO dbo.CaseSensitivtyTest ( Textcol ) VALUES ( 'test' ), ( 'Test' ), ( 'TEST' ) SELECT * FROM CaseSensitivtyTest WHERE CONTAINS ( textcol, 'TEST' ) DROP FULLTEXT INDEX ON dbo.CaseSensitivtyTest DROP FULLTEXT CATALOG ft DROP TABLE CaseSensitivtyTest