May 28, 2009 at 3:30 pm
Hi,
I was wondering if someone could assist me in trying to understand why I'm unable to create a full-text index that uses an indexed view (which contains a varbinary(max) for file content and varchar(5) column for the extension to invoke the appropriate filter).
1) I was able to create the indexed view without issue
2) I'm able to specify base tables and for the file content column, I'm able to specify the associated data type column in a full-text index.
3) I'm not able to select the data type column for the file content column when I choose my indexed view instead for a full-text index.
The reason I'm trying to use an indexed view vs. my tables is because I want to do a search across multiple tables in a single query and have a unified rank. In additional, it could be my ignorance but I received significant performance degradation when i supplied two CONTAINS function calls with an 'OR' together in my WHERE clause but rather each call individually performed in less than a second.
Ideally, all i want to accomplish is a efficient query that can look within my file content as well as name and description columns as metadata for the file. This is the query I'm running...
SELECT
p.Name,
p.Description,
f.Content, -- binary file content
f.Extension -- data type column
FROM Publication p
INNER JOIN PublicationFile pf on p.PublicationID = pf.PublicationID
INNER JOIN [File] f on f.FileID = pf.FileID
WHERE
(CONTAINS(f.*, 'myterm') OR CONTAINS(p.*, 'myterm'))
Publication - contains all the metadata about a particular defined set of 'content'. I
PublicationFile - basic join table that links up the metadata to all the file formats available
File - contains the content in specific formats like doc, docx, pdf as well as the extension
I guess I'll start with that and see if anyone has any thoughts and can elaborate if I'm missing necessary details. I'm open to reasonable options (i.e., suggestions to use Google's product or major schema changes can't be evaluated as an option)
Kind Regards,
Scott
May 28, 2009 at 4:24 pm
The Fulltext predicates are not allowed in Indexed Views however what you are trying to do is best to use two columns one for the binary version .doc, .docx, .pdf another in Nvarchar(max) for the text version so you could use LIKE predicat or Fulltext to search for the files either by name or ID.
http://msdn.microsoft.com/en-us/library/ms191432.aspx
Kind regards,
Gift Peddie
May 28, 2009 at 4:25 pm
Well...in case anyone else bumps into a similar issue, I did end up solving it but wasn't exactly as "complex" as I thought it would be. I hate calling things a bug when I don't 100% know what is going on under the hood but the issue seemed to be related to SQL Management Studio 2008.
Again, it was accepting my setup for base tables but as soon as I tried an indexed view, it just completely ignored it and the dialog closed. When I'd go back into the properties, my full-text index wouldn't be configured.
Ultimately, I wrote the T-SQL script to create my full-text index and it took it just fine and built the index.
May 28, 2009 at 4:29 pm
Right, I understand that and good point to make based on the sql I supplied. My actual T-SQL SELECT for the view didn't make any reference to FREETEXT or CONTAINS functions. I guess i probably didn't state myself very well as I was trying to explain two different scenarios that I tried. One was just setting up tables with full-text indexes for those (which that is what that query was for) and the other was creating an indexed view.
I really appreciate your help. I solved my immediate problem which, again, seemed to be management studio related.
May 28, 2009 at 4:34 pm
Well it is actually a bug you could create a fulltext index in a view because it is one of the pre defined not allowed.
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy