\nOnce 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" tab on any Office Document, or jpg file (especially if you have a GPS camera) or MP3 files, or…other files like [your favorite file type goes here]. Wikipedia defines extended properties as "a file system feature that enables users to associate computer files with metadata not interpreted by the filesystem". To find some extended properties viewers and searchers, I simply did a search on "Windows OS Extended Properties". The APIs to enumerate these properties are the Windows Shell APIs.\n<\/p>\n
\nIn SQL Server Denali CTP1, you can use a new feature of Fulltext Search to search data stored in FTS, not only on content, but if the IFilter exposes it, on extended properties as well. According to BOL, you first need to create SEARCH_PROPERTY_LISTs and add the extended properties you'd like to search on:\n<\/p>\n
\nCREATE SEARCH PROPERTY LIST DocumentProperties;
\nGO
\nALTER SEARCH PROPERTY LIST DocumentProperties
\n ADD 'Title'
\n WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
\n PROPERTY_DESCRIPTION = 'System.Title – Title of the item.' );
\nGO — add more properties\n<\/p>\n
\nAnd alter the fulltext index to use your search property list:\n<\/p>\n
\nALTER FULLTEXT INDEX ON Production.Document
\n SET SEARCH PROPERTY LIST DocumentProperties
\n WITH NO POPULATION;
\nGO\n<\/p>\n
\nALTER FULLTEXT INDEX ON Production.Document
\n START FULL POPULATION;\n<\/p>\n
\nthe available metadata looks like this:\n<\/p>\n
\nselect * from sys.registered_search_properties
\nselect * from sys.registered_search_property_lists
\nselect * from sys.dm_fts_index_keywords_by_property\n<\/p>\n
\nThen you'd use them with the improved FTS functions:\n<\/p>\n
\nSELECT Document FROM Production.Document WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');\n<\/p>\n
\nCool, eh? Now Office 2007 and above IFilters support these properties on Office docs. What is needed now is a way to extract property lists using the Windows Shell APIs. And more IFilters that support this. And….given that there's already FTS support over FILESTREAM columns, a way to easily expose the native filesystem as a set of tables with filestream columns.\n<\/p>\n
\nWinFS, anyone…?<\/p>\n","protected":false},"excerpt":{"rendered":"
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" […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,31],"tags":[],"class_list":["post-586","post","type-post","status-publish","format-standard","hentry","category-full-text-search","category-sql-server-2012"],"yoast_head":"\n