Access FileTables with Transact-SQL

Applies to: SQL Server

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

INSERT Operations on 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.

UPDATE Operations on FileTables

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.

DELETE Operations on FileTables

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.

Constraints That Are Enforced for DML Operations on FileTables

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.

See Also

Load Files into FileTables
Work with Directories and Paths in FileTables
Access FileTables with File Input-Output APIs
FileTable DDL, Functions, Stored Procedures, and Views