Dropping FullText in SQL Server

Many months ago I had watched a Tech Ed demo by Bob Beauchemin, an active blogger over at SQL Skills on how to setup FileTable in SQL Server 2012. Links to Part 1 - Filestreams and filetables in SQL Server 2012 and to Part 2 - Integrating SQL Server Filetables, Property Search, and FTS/Semantc Search.

Before going any further I want to ensure 100% credit to Bob's wonderful keen insight on FileTable, FileStream and SQL Server.

The issue at hand:
Oddly enough I had a warning when altering my FileTable in SQL Server 2012 today. Normally it is a simple process of two commands:

Unable to modify table. Table contains at least one fulltext index. 

Ok so when all else fails use T-SQL

DROP FULLTEXT INDEX ON MYDB..MyFileTable
GO
DROP FULLTEXT CATALOG MyFileTableFullTextCatalog
GO

Ran the commands and got the same error. This happened because I shared an Index pre-made by SQL Server when I built the FileTable which Bob explains in more detail in his post.

stream_id unique index.

No big deal...I saw that one coming at least. Apparently when SSMS is making changes to a table it creates a temp table and places a session lock on the table. Fair enough. Rebooted the instance ran the commands above and problem solved.

The Reason:
This is to get around a defect with Entity Framework not having compatibility with HeiarchyId as a  SQL Data Type. The EF team stated in their blog that EF6 probably won't have support for this data type either. EF will support a read only view though, say something like this:

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MyFileTable]
AS
----
SELECT
MyFT.stream_id,
--Transform and omit path locator and path locator parent.
FileTableRootPath() + MyFT.file_stream.GetFileNamespacePath() AS FileNamespacePath, 
MyFT.name, 
-- Omit these two fields to strings
--MyFT.path_locator,
--MyFT.parent_path_locator, 
-- Remaining normal fields.
MyFT.file_type, MyFT.cached_file_size, MyFT.creation_time, MyFT.last_write_time, 
MyFT.last_access_time, MyFT.is_directory, MyFT.is_offline, MyFT.is_hidden, MyFT.is_readonly, MyFT.is_archive, MyFT.is_system, MyFT.is_temporary
FROM MyFileTableNameInDbo AS MyFT
----
GO

For the moment this will work. I will expand on this after I give it more thought. Perhaps it would be better to use a AFTER UPDATE Trigger to a real USER_TABLE with just the stream_id (PK), the path_locator as a string via FileNamespacePath in the script above. Finally I will include the name of the file. This way I can use foreign key constraints (which views do not support).

This new "real" table can only be updated by this trigger. The downside is that the EF will not support the additional features offered by FileTable (yet!)

Sources:

• [1]Using filetable with full-text? Lose the redundant index
• [2]Entity Framework 6