sys.external_file_formats (Transact-SQL)

APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Contains a row for each external file format in the current database for SQL Server, SQL Database, and SQL Data Warehouse.

Contains a row for each external file format on the server for Parallel Data Warehouse.

Column Name Data Type Description Range
file_format_id int Object ID for the external file format.
name sysname Name of the file format. in SQL Server and SQL Data Warehouse, this is unique for the database. In Parallel Data Warehouse, this is unique for the server.
format_type tinyint The file format type. DELIMITEDTEXT, RCFILE, ORC, PARQUET
field_terminator nvarchar(10) For format_type = DELIMITEDTEXT, this is the field terminator.
string_delimiter nvarchar(10) For format_type = DELIMITEDTEXT, this is the string delimiter.
date_format nvarchar(50) For format_type = DELIMITEDTEXT, this is the user-defined date and time format.
use_type_default bit For format_type = DELIMITED TEXT, specifies how to handle missing values when PolyBase is importing data from HDFS text files into SQL Data Warehouse. 0 - store missing values as the string 'NULL'.

1 - store missing values as the column default value.
serde_method nvarchar(255) For format_type = RCFILE, this is the serialization/deserialization method.
row_terminator nvarchar(10) For format_type = DELIMITEDTEXT, this is the character string that terminates each row in the external Hadoop file. Always '\n'.
encoding nvarchar(10) For format_type = DELIMITEDTEXT, this is the encoding method for the external Hadoop file. Always 'UTF8'.
data_compression nvarchar(255) The data compression method for the external data. For format_type = DELIMITEDTEXT:

- 'org.apache.hadoop.io.compress.DefaultCodec'
- 'org.apache.hadoop.io.compress.GzipCodec'

For format_type = RCFILE:

- 'org.apache.hadoop.io.compress.DefaultCodec'

For format_type = ORC:

- 'org.apache.hadoop.io.compress.DefaultCodec'
- 'org.apache.hadoop.io.compress.SnappyCodec'

For format_type = PARQUET:

- 'org.apache.hadoop.io.compress.GzipCodec'
- 'org.apache.hadoop.io.compress.SnappyCodec'

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

See Also

sys.external_data_sources (Transact-SQL)
sys.external_tables (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL)