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 nameData typeDescription
namesysnameName of data space, unique within the database.
data_space_idintData space ID number, unique within the database.
typechar(2)Data space type:

FG = Filegroup

FD = FILESTREAM data filegroup

FX = Memory-optimized tables filegroup

 Applies to: SQL Server 2014 through SQL Server 2016.

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



 Applies to: SQL Server 2014 through SQL Server 2016.


is_defaultbit1 = 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_systembitApplies to: SQL Server 2012 through SQL Server 2016.

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

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

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

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)

Community Additions