This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysfilegroups returns one row for every filegroup in a database.  Filegroups are used as collections of files that are associated with databases and are used to help determine where data will be placed on disk.  Every database will have at least one filegroup.

The replacement for sysfilegroups is the catalog view sys.filegroups.  Similar to sysfilegroups, this catalog view returns all of the filegroups that are associated with a database.

Status Column

The compatibility view contains a single status column.  The status column contains two possible values.  These values are:

• 0×8:Read only
• 0×10: Default filegroup

Query Via sysfilegroups

After considering the status column, the query to access the data in sysfilegroups is fairly simple.  Using the query in Listing 1, we can see that the two case statements can be used to extract whether the filegroup is read only and the default filegroup.  There is an additional column, allocpolicy, that is included for completeness but offers no values.  This is an internal column which no longer has a use case in SQL Server.

--Listing 1 – Query for sys.sysfilegroups
SELECT f.groupname
,f.groupid
,CASE WHEN CONVERT(INT,f.status & 0x8) = 8 THEN 1 ELSE 0 END as is_read_only
,CASE WHEN CONVERT(INT,f.status & 0x10) = 16 THEN 1 ELSE 0 END as is_default
,f.allocpolicy
,f.status
FROM sysfilegroups f

Query via sys.filegroups

All of the columns needed to map sys.filegroups to sysfilegroups are included in the former by default.  The status column does not exist, similar to other catalog views, instead it is replaced with the columns is_read_only and is_default.  The query to provide this information is included in Listing 2.  Along with the base columns needed to replace sysfilegroups, there are a few other columns of interest.  The first are type and type_desc, these two columns are inherited from the catalog view sys.data_spaces; which is the parent catalog view for filegroups, partition schemes, and filestream data storage.  The value in sys.filegroups will always be those for filegroups.  The other column is filegroup_guid which provides a uniqueidentifier value for non-PRIMARY filegroups.

--Listing 2 – Query for sys.filegroups
SELECT name
,data_space_id
,is_read_only
,is_default
,type
,type_desc
,is_system
,filegroup_guid
FROM sys.filegroups

Summary

In this post, we discussed the use of sys.filegroups instead of sysfilegroups.  The jump between the two isn’t that extreme.  As long as you haven’t fashioned some manner to leverage the internal column allocpolicy, if should be an easy upgrade between the two.  After reading all of this, do you see any reason to continue using sysfilegroups?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysfilegroups

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysfulltextcatalogs
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs