How To Return Document Property Values in Full-Text Search

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 […]

Hit Highlighting/Summarization product for SQL Server Full-Text Search

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

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 […]

I’ve got the last talk at TechEd and its almost LAST CALL

I've been at TechEd North America in Orlando this week and have seen some amazing new things. Upcoming operating systems, new hardware, and a detailed technical followup on the Azure (cloud) announcements from last week, including some Windows Azure SQL Database (was SQL Azure) upcoming enhancements. I've spend quite a bit of time at the […]

Full-text query plans and stats in Denali

I've been looking at full-text query plans in Denali lately, and looking at the SQL query plan for the full-text part, there isn't much to see (modulo docid lookup or lack of it, see a couple of posts ago). Everything seemed to be hidden behind the TableValuedFunction iterator for FulltextMatch function, and although there's the usual […]

Of filetables, full-text and semantic search, and docidmaps

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 […]

SQL Server Denali Fulltext Properties – Where?

When I originally wrote the SQL Server Denali full-text search on properties cheap demo, I used property sets with properties (GUIDs) that I got from the SQL Server BOL example (although the example itself didn't work). As it says in BOL, CREATE SEARCH PROPERTY LIST creates an empty property list. Which got me to wondering, where […]

SQL Server Denali – FTS on properties, the cheap demo

So here's a cheap demo of FTS property search in Denali. For the motivation for this feature, see the previous blog entry. I'm a minimalist (minimalism is a teaching aid), so its just the basics. In some places, I may be doing more than is minimally required for clarity. First off, I didn't use the […]

SQL Server Denali – FTS on properties, the motivation

Years after its inception, I think I've figured out what WinFS was supposed to do. It sometime takes me a while. I got a new mp3 player (I don't have appliances at my house with cute monikers that start with the letter "i") and wondered why it sometime played songs "out of order". I like […]

Using SQL Server Denali Full-Text Search With Extended Properties

Once upon a time, there was to be a SQL Server-based file system for Windows. This file system would not only let you search on file properties like filename and file length, but also on what's known as "Extended Properties". For an idea of what extended properties refers to, have a look at the "Properties" […]