sql server - How can I add and view indexes for a FILETABLE? - Stack Overflow

I have a SQL Server FILETABLE. I am NOT trying to do FULL TEXT searching. I have a query which uses the file_type column in the WHERE clause. The estimated execution plan suggests a new index. I have read elsewhere here on SO that one can create indexes for Filetables. However, I don't see that option in SSMS to do so through the GUI. So I tried via query, as below, but it doesn't seem to be created even though the execution says it was successful. Since there is no index folder under the filetable in question, where are indexes stored for this kind of table? And why isn't my index creation working? The estimated plan still says I need the same index I just tried to create.

CREATE NONCLUSTERED INDEX file_type
ON [dbo].[storedFiles] ([file_type])
INCLUDE ([stream_id],[path_locator])
GO

I have a SQL Server FILETABLE. I am NOT trying to do FULL TEXT searching. I have a query which uses the file_type column in the WHERE clause. The estimated execution plan suggests a new index. I have read elsewhere here on SO that one can create indexes for Filetables. However, I don't see that option in SSMS to do so through the GUI. So I tried via query, as below, but it doesn't seem to be created even though the execution says it was successful. Since there is no index folder under the filetable in question, where are indexes stored for this kind of table? And why isn't my index creation working? The estimated plan still says I need the same index I just tried to create.

CREATE NONCLUSTERED INDEX file_type
ON [dbo].[storedFiles] ([file_type])
INCLUDE ([stream_id],[path_locator])
GO

• sql-server
• indexing
• filetable

Share Improve this question Follow edited Jan 3 at 9:12 IMSoP 98.4k1818 gold badges132132 silver badges180180 bronze badges asked Jan 2 at 20:27 Son of ArathornSon of Arathorn 5111 silver badge99 bronze badges 4

• How did you check if the index was created? You'll need to refresh SSMS object explorer after creating an index using T-SQL. Add the query to your question. – Dan Guzman Commented Jan 2 at 20:36
• @DanGuzman, I closed SSMS and then expanded the Filetable table. I only see 5 out of the 6 normal items there. The Indexes folder is missing. That's why I asked where they are supposed to show up and pointed out I couldn't create one via GUI. – Son of Arathorn Commented Jan 2 at 21:34
• Execute this Exec sp_help file_type, see what comes back. – M.Ali Commented Jan 3 at 13:21
• I forgot all about this. Not sure why. So sorry for the tardy reply. I honestly can't even recall what I was doing when I asked this question. I just checked all of my databases that have FileTables and SSMS allows me to view and create new indexes via GUI. So I don't remember where I was seeing the problem that prompted the question. The link David Browne posted was one I had found. His query was helpful inasmuch as it does work to list current indexes. I'm now going to try and remember what this question was all about. – Son of Arathorn Commented Mar 13 at 17:14

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

First, yes this is supported.

Since a FileTable has a pre-defined and fixed schema, you cannot add or change its columns. However, you can add custom indexes, triggers, constraints, and other options to a FileTable.

https://learn.microsoft.com/en-us/sql/relational-databases/blob/create-alter-and-drop-filetables?view=sql-server-ver16#BasicsAlter

You can see that the index was created with this query:

select object_name(i.object_id) tn, i.name index_name, c.name column_name, ic.key_ordinal, ic.is_included_column, i.index_id
  from sys.indexes i
  join sys.index_columns ic 
    on i.object_id = ic.object_id
   and i.index_id = ic.index_id 
  join sys.columns c 
    on c.object_id = ic.object_id
   and c.column_id = ic.column_id
  where i.object_id = object_id('storedFiles')