Category: sql server search
jpro1000 on Mon, 12 May 2014 21:04:36
anyone know if I can leverage the power of full text indexes to use as a global database search? We need to be able to search many different tables with many joins. This is very slow to search them now. Could I perhaps make a sql view with all my joins etc then create a full text search index on this view to speed it up? The other option is a de-normalized table, but managing that will be a pain. Any suggestions would be greatly appreciated!
Fanny Liu on Wed, 14 May 2014 14:23:06
Yes. You can create full-text search on a table or view. But it must have a unique, single-column, non-nullable index.
Before you create full-text search, you should create indexed view:
CREATE VIEW Full_text_View WITH SCHEMABINDING AS (... ) CREATE UNIQUE CLUSTERED INDEX Index_name ON Full_text_View (column_name)Regards,
jpro1000 on Tue, 27 May 2014 18:56:48
Thanks! However, indexed views seem to have very strict requirements. I need unions and they are not allowed in indexed views it seems... any ideas of a workaround off hand?