FileTable Schema

FileTable Schema

 

Applies To: SQL Server 2016

Describes the pre-defined and fixed schema of a FileTable.

File attribute nametypeSizeDefaultDescriptionFile system accessibility
path_locatorhierarchyidvariableA hierarchyid that identifies the position of this item.The position of this node in the hierarchical FileNamespace.

Primary key for the table.
Can be created and modified by setting the Windows path values.
stream_id[uniqueidentifier] rowguidcolA value returned by the NEWID() function.A unique ID for the FILESTREAM data.Not applicable.
file_streamvarbinary(max)

 filestream
variableNULLContains the FILESTREAM data.Not applicable.
file_typenvarchar(255)variableNULL.

A create or rename operation in the file system populates the file extension value from the name.
Represents the type of the file.

This column can be used as the TYPE COLUMN when you create a full-text index.

 file_type is a persisted computed column.
Calculated automatically. Cannot be set.
Namenvarchar(255)variableGUID value.The file or directory name.Can be created or modified by using Windows APIs.
parent_path_locatorhierarchyidvariableA hierarchyid that identifies the directory that contains this item.The hierarchyid of the containing directory.

 parent_path_locator is a persisted computed column.
Calculated automatically. Cannot be set.
cached_file_sizebigintThe size in bytes of the FILESTREAM data.

 cached_file_size is a persisted computed column.
Although the cached file size is automatically kept up to date, it can go out of sync in unusual circumstances. To calculate the exact size, use the DATALENGTH() function.
creation_timedatetime2(4)

 not null
8 bytesCurrent time.The date and time that the file was created.Calculated automatically. Can also be set by using Windows APIs.
last_write_timedatetime2(4)

 not null
8 bytesCurrent time.The date and time that the file was last updated.Calculated automatically. Can also be set by using Windows APIs.
last_access_timedatetime2(4)

 not null
8 bytesCurrent time.The date and time that the file was last accessed.Calculated automatically. Can also be set by using Windows APIs.
is_directorybit

 not null
1 byteFALSEIndicates whether the row represents a directory. This value is calculated automatically, and cannot be set.Calculated automatically. Cannot be set.
is_offlinebit

 not null
1 byteFALSEOffline file attribute.Calculated automatically. Can also be set by using Windows APIs.
is_hiddenbit

 not null
1 byteFALSEHidden file attribute.Calculated automatically. Can also be set by using Windows APIs.
is_readonlybit

 not null
1 byteFALSERead-only file attribute.Calculated automatically. Can also be set by using Windows APIs.
is_archivebit

 not null
1 byteFALSEArchive attribute.Calculated automatically. Can also be set by using Windows APIs.
is_systembit

 not null
1 byteFALSESystem file attribute.Calculated automatically. Can also be set by using Windows APIs.
is_temporarybit

 not null
1 byteFALSETemporary file attribute.Calculated automatically. Can also be set by using Windows APIs.

Create, Alter, and Drop FileTables

Community Additions

ADD
Show:
© 2016 Microsoft