About a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this: SELECT name DocumentName, file_stream.GetFileNamespacePath() Path […]
Bob Beauchemin
SQL Server Blog
How To Return Document Property Values in Full-Text Search
Posted on: December 12, 2012 10:30 am
Hit Highlighting/Summarization product for SQL Server Full-Text Search
Posted on: November 28, 2012 6:02 pm
I've often asked about add-ins for SQL Server Full-Text Search feature. And the most requested feature for FTS has long been some way to accomodate hit highlighting. Recently, I've come across a beta for a product that does just this, along with document summarization formats for display, ThinkHighlight by Interactive Thoughts. The add-in is implemented […]
Using filetable with full-text? Lose the redundant index
Posted on: July 31, 2012 9:47 am
I'm not sure how many full-text search over filetable demos I've seen (including some I've written that I was just perusing), that do something like this. CREATE TABLE foo AS FILETABLE WITH (FILETABLE_DIRECTORY = N'ftdir'); CREATE UNIQUE INDEX fooidx ON foo(stream_id); CREATE FULLTEXT INDEX ON foo (file_stream TYPE COLUMN file_type) KEY INDEX fooidx; After looking […]
Saving the contents of a SQL Server XML column to a file
Posted on: July 12, 2012 11:41 am
While preparing for my TechEd talk on filetables and full-text search, I came across this tidbit that I thought was interesting. With the advent of XML support in databases, specifically SQL Server's XML data type in SQL Server 2005, there has been two main ways to store XML; in files on the file system, and […]
SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas
Posted on: January 17, 2012 12:01 pm
OK, back to PathName() with AlwaysOn, which I started on in the previous blog post. PathName() in SQL Server 2012 doesn't return the computer name by default ("AlwaysOn1" or "AlwaysOn2" in my example) but returns the VNN name (virtual network name). That is, it returns the availability group "listener share" name. In my case, the […]
SQL Server 2012 FileTables – AlwaysOn support and PathName()
Posted on: January 17, 2012 10:03 am
When I was covering the FileTable-specific functions and methods, I didn't mention PathName(), a filestream-related function, that (naturally) can be used with FileTables. This posting is about PathName(), but, mostly about what happens with FileTable in an AlwaysOn availability group configuration. There were some surprises there. None of the FileTable-specific functions and methods mention AlwaysOn, […]
SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods
Posted on: January 16, 2012 1:06 pm
So finally, we get to using FileTable's path_locator column with the methods of hierarchyid. To pick up where we left off, remember that hierarchyid has a method named GetLevel(). Given the following directory structure in the FileTable's share: File1.txt File2.txt SQLFiles SubdirFile1.txt SubdirFile2.txt I have a few more levels of subdirectory here, but […]
SQL Server 2012 FileTables in T-SQL part 1: functions and methods
Posted on: January 15, 2012 10:17 pm
I've been working with the SQL Server 2012 FileTable feature lately. Besides learning to appreciate the esoteric features of the NTFS file system and SMB protocol, only some of which are supported by FileTables, I've been trying to work with FileTables in SQL Server using T-SQL. This turns out to be an interesting exercise, especially […]
Of filetables, full-text and semantic search, and docidmaps
Posted on: September 6, 2011 12:45 pm
SQL Server Denali's new Filetable feature and full-text search are made for each other. Set up database for non-transactional filestream access with a directory name, create a table "AS FILETABLE" with a subdirectory name, drag over a bunch of files and turn on full-text search. Voila, instant search with two predicates and two TVFs. Even […]
