December 6, 2012

Data Spaces

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

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

No comments:

Post a Comment

Total Pageviews