Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Access FileTables with Transact-SQL

Describes how Transact-SQL data manipulation language (DML) commands work with FileTables.

The following considerations apply to INSERT Operations on FileTables:

  • All the file attribute columns have NOT NULL constraints. If values are not explicitly set, then appropriate default values are supplied.

  • System-defined constraints are enforced if the INSERT statement sets the name, path_locator, parent_path_locator, or file attributes.

  • The application can obtain the path_locator for a file or directory by providing the file system path to the GetPathLocator (Transact-SQL) function.

[TOP]

The following considerations apply to UPDATE operations on FileTables:

  • Updates to any user-defined data are allowed.

  • System-defined constraints are enforced if the INSERT statement sets the name, path_locator, parent_path_locator, or file attributes.

  • Updates can be made to the FILESTREAM data in the file_stream column without affecting any of the other columns, including the timestamps.

[TOP]

The following considerations apply to DELETE operations on FileTables:

  • Deleting a row also removes the corresponding file or directory from the file system.

  • Deleting a row fails if the row corresponds to a directory that contains other files or directories.

[TOP]

System-defined constraints ensure that DML actions do not compromise the integrity of the file namespace hierarchy. The constraints that are enforced include the following:

  • When you set or change the name of the file or directory:

    • Windows file and directory naming conventions are enforced.

    • The uniqueness of the name in the parent directory is enforced.

  • When you set or change the location of a file or directory by setting or changing the path_locator or parent_path_locator:

    • Uniqueness is enforced.

    • The consistency of the hierarchical tree of directories and files is enforced, including the consistency of path_locator and parent_path_locator values.

  • The value of is_directory cannot be set to true when the file_stream column is not null. Data in the file_stream column indicates that the row represents a file and not a directory.

  • File attribute columns cannot be null. NOT NULL constraints are enforced with default values.

  • The value of last_access_time cannot be earlier than last_write_time and creation_time.

[TOP]

Community Additions

ADD
Show:
© 2015 Microsoft