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 my songs (in any musical genre) in "album" order. You wouldn't play movements of a symphony out of order, order-sensitivity adds to the enjoyment. Finally realized that the player was not ordering songs by filename like my old player did, but by the extended attributes in the file (Title, Authors, Track, etc). There's lots of these in common multimedia files, have a look at the "Propeties" page in Windows Explorer if you have pictures taken with a digital camera.

What does this have to do with SQL Server, you ask? In SQL Server Denali, they added another full-text predicate to distinguish searches on specific properties. In previous versions, these properties would show up in a search but you couldn't distinguish "Author property contains bob" from "document contains bob". In Denali, you can.

Normally, you might not store items like pictures directly in a database just to search on property metadata. With the upcoming filetable feature (non-transactional access to filestreams, standard table layout, filestream storage) it might be worth your while just to have a cheap property/content search. BTW, "upcoming" means filetable doesn't work in CTP1, though they showed the feature at SQLPASS. But FTS + properties work.

Having figured out the reason for this feature (I think, you tell me if I'm wrong), in the next entry, I'll show you how to use it.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.