sys.data_spaces (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contains a row for each data space. This can be a filegroup, partition scheme, or FILESTREAM data filegroup.

Column name Data type Description
name sysname Name of data space, unique within the database.
data_space_id int Data space ID number, unique within the database.
type char(2) Data space type:

FG = Filegroup

FD = FILESTREAM data filegroup

FX = Memory-optimized tables filegroup

Applies to: SQL Server 2014 through SQL Server 2017.

PS = Partition scheme
type_desc nvarchar(60) Description of data space type:

FILESTREAM_DATA_FILEGROUP

MEMORY_OPTIMIZED_DATA_FILEGROUP

Applies to: SQL Server 2014 through SQL Server 2017.

PARTITION_SCHEME

ROWS_FILEGROUP
is_default bit 1 = This is the default data space. The default data space is used when a filegroup or partition scheme is not specified in a CREATE TABLE or CREATE INDEX statement.

0 = This is not the default data space.
is_system bit Applies to: SQL Server 2012 through SQL Server 2017.

1 = Data space is used for full-text index fragments.

0 = Data space is not used for full-text index fragments.

Permissions

Requires membership in the public role. For more information, see Metadata Visibility Configuration.

See Also

Data Spaces (Transact-SQL)
Catalog Views (Transact-SQL)
sys.databases (Transact-SQL)
sys.destination_data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.partition_schemes (Transact-SQL)
Querying the SQL Server System Catalog FAQ
In-Memory OLTP (In-Memory Optimization)