But alas, I'm not even sure how sp_BlitzErik gets to the answer given the code in the suggestion, like '%rock%' or Without these benefits, you're essentially just trying to optimize out string comparison. it also places the maintenance on those categories on the type.That complexity is encapsulated in the type and hidden from the user. conceals the fact that you're categories are an enumerated type.But even better you can query them like before. Now you've collapsed the strings to integer representations. Now what you want is an enum type, CREATE TYPE foo AS ENUM ('Bad', 'Idea', 'Jeans') Let's recreate his data for PostgreSQL - it's a lot cleaner to create in in PostgreSQL too. I don't agree with the solution either, though I'm not sure how to do this better in SQL Server. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. It's not there to search multiple fields. Full text search isn't there to do what you think it does. Sp_BlitzErik's answer hits on a lot of good points, but I don't think that's why you shouldn't use Full Text Search. Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :) SELECT COUNT(*) WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1ĬREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id) SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'ĪDD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1 SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY sys.messages AS m Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).įor smaller use-cases, computed columns are generally a better approach.ĬREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100)) Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |