\nI'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.\n<\/p>\n
\nCREATE TABLE foo AS FILETABLE WITH (FILETABLE_DIRECTORY = N'ftdir');
\nCREATE UNIQUE INDEX fooidx ON foo(stream_id);
\nCREATE FULLTEXT INDEX ON foo (file_stream TYPE COLUMN file_type) KEY INDEX fooidx;\n<\/p>\n
\nAfter looking at this a few times, I couldn't beleive there wasn't some kind of key contraint\/index on the stream_id column defined as part of the filetable definition. And indeed there is. There's a UNIQUE constraint already that casues a nonclustered unique index to be built to enforce it. So why the extra nonclustered index, just to use full-text? A couple of extra lines of code should be able to tell us what the filetable named it's constraint\/index, and we can use that name in the full-text index creation instead. And we won't need the redundant index.\n<\/p>\n
\nBut wait…we don't even have to do that.\n<\/p>\n
\nCREATE TABLE… AS FILETABLE takes some additional parameters that make the extra code unnecessary. For our purposes, we only need to name the key constraint that corresponds to the KEY INDEX we're using in the full-text index. So…\n<\/p>\n
\nCREATE TABLE foo AS FILETABLE WITH (FILETABLE_DIRECTORY = N'ftdir', FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=UQ_stream_id);
\nCREATE FULLTEXT INDEX ON foo (file_stream TYPE COLUMN file_type) KEY INDEX UQ_stream_id;\n<\/p>\n
\nloses the redundant index. There are also parameters to specify the FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME and FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME as well, if you need them, to make it easier to define foreign keys. Although the filetable's related objects are fixed at creation time, you can add constraints to a filetable after it is created.\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,18,31],"tags":[],"class_list":["post-473","post","type-post","status-publish","format-standard","hentry","category-filetable","category-full-text-search","category-sql-server-2012"],"yoast_head":"\n