Data Spaces

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.data_spaces
• sys.destination_data_spaces
• sys.filegroups
• sys.partition_schemes

sys.data_spaces

data space. This can be a filegroup, partition scheme, or FILESTREAM data filegroup.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Name of data space, unique within the database.
data_space_id	•	•	•	•	int	Data space ID number, unique within a database.
type	•	•	•	•	char(2)	Data space type: FG = Filegroup PS = Partition scheme
type_desc	•	•	•	•	nvarchar(60)	Description of data space type: ROWS_FILEGROUP PARTITION_SCHEME
is_default	•	•	•	•	bit	1 = This is the default data space. This 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	1 = Data space is used for full-text index fragments. 0 = Data space is not used for full-text index fragments.

TSQL

Sql 2005
SELECT [name], [data_space_id], [type], [type_desc], [is_default] FROM sys.data_spaces
Sql 2008
SELECT [name], [data_space_id], [type], [type_desc], [is_default] FROM sys.data_spaces
Sql 2008 R2
SELECT [name], [data_space_id], [type], [type_desc], [is_default] FROM sys.data_spaces
Sql 2012
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [is_system] FROM sys.data_spaces

Back to Top

---

sys.destination_data_spaces

data space destination of a partition scheme.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
partition_scheme_id	•	•	•	•	int	ID of the partition-scheme that is partitioning to the data space.
destination_id	•	•	•	•	int	ID (1-based ordinal) of the destination-mapping, unique within the partition scheme.
data_space_id	•	•	•	•	int	ID of the data space to which data for this scheme's destination is being mapped.

TSQL

Sql 2005
SELECT [partition_scheme_id], [destination_id], [data_space_id] FROM sys.destination_data_spaces
Sql 2008
SELECT [partition_scheme_id], [destination_id], [data_space_id] FROM sys.destination_data_spaces
Sql 2008 R2
SELECT [partition_scheme_id], [destination_id], [data_space_id] FROM sys.destination_data_spaces
Sql 2012
SELECT [partition_scheme_id], [destination_id], [data_space_id] FROM sys.destination_data_spaces

Back to Top

---

sys.filegroups

data space that is a filegroup.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Inherited From	Description
name	•	•	•	•	sysname	sys.data_spaces	Name of data space, unique within the database.
data_space_id	•	•	•	•	int	sys.data_spaces	Data space ID number, unique within a database.
type	•	•	•	•	char(2)	sys.data_spaces	Data space type: FG = Filegroup PS = Partition scheme
type_desc	•	•	•	•	nvarchar(60)	sys.data_spaces	Description of data space type: ROWS_FILEGROUP PARTITION_SCHEME
is_default	•	•	•	•	bit	sys.data_spaces	1 = This is the default data space. This 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.
filegroup_guid	•	•	•	•	uniqueidentifier		GUID for the filegroup. NULL = PRIMARY filegroup
log_filegroup_id	•	•	•	•	int		Reserved for future use. In Microsoft SQL Server 2005, the value is NULL.
is_read_only	•	•	•	•	bit		1 = Filegroup is read-only. 0 = Filegroup is read/write.
is_system				•	bit	sys.data_spaces	1 = Data space is used for full-text index fragments. 0 = Data space is not used for full-text index fragments.

TSQL

Sql 2005
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [filegroup_guid], [log_filegroup_id], [is_read_only] FROM sys.filegroups
Sql 2008
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [filegroup_guid], [log_filegroup_id], [is_read_only] FROM sys.filegroups
Sql 2008 R2
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [filegroup_guid], [log_filegroup_id], [is_read_only] FROM sys.filegroups
Sql 2012
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [is_system], [filegroup_guid], [log_filegroup_id], [is_read_only] FROM sys.filegroups

Back to Top

---

sys.partition_schemes

Data Space that is a partition scheme, with type = PS.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Inherited From	Description
name	•	•	•	•	sysname	sys.data_spaces	Name of data space, unique within the database.
data_space_id	•	•	•	•	int	sys.data_spaces	Data space ID number, unique within a database.
type	•	•	•	•	char(2)	sys.data_spaces	Data space type: FG = Filegroup PS = Partition scheme
type_desc	•	•	•	•	nvarchar(60)	sys.data_spaces	Description of data space type: ROWS_FILEGROUP PARTITION_SCHEME
is_default	•	•	•	•	bit	sys.data_spaces	1 = This is the default data space. This 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.
function_id	•	•	•	•	int		ID of partition function used in the scheme.
is_system				•	bit	sys.data_spaces	1 = Data space is used for full-text index fragments. 0 = Data space is not used for full-text index fragments.

TSQL

Sql 2005
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [function_id] FROM sys.partition_schemes
Sql 2008
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [function_id] FROM sys.partition_schemes
Sql 2008 R2
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [function_id] FROM sys.partition_schemes
Sql 2012
SELECT [name], [data_space_id], [type], [type_desc], [is_default], [is_system], [function_id] FROM sys.partition_schemes

Back to Top

---