Home > SQL Server > Filegroup_ID, Filegroup_Name and FileGroupProperty functions – SQL Server

Filegroup_ID, Filegroup_Name and FileGroupProperty functions – SQL Server


File Groups are named collections of files in SQL Server to organize data files. There are more information available to share about file group. But, I will explain about it in my future blog. In this blog post, I am going to show you how to use file group functions in SQL Server. These functions are more useful, if you write an application or T-SQL to perform administration tasks.

FileGroup_ID(‘File group name’) : This function returns filegroup identification number for a given filegroup name. It returns an integer value.

FileGroup_Name(File group id) : This function returns filegroup name for a given filegroup identification number. It returns a Nvarchar value or length 128

FileGroupProperty(‘File group name’, ‘Property’) : This function returns filegroup property value of a given file group name and property name. Available property names for this function are IsReadOnly, IsUserDefinedFG or IsDefault. It returns an integer value 1 = true, 0 = false or NULL = error.

We can also use catalog view to get all information about database file group.

sys.filegroups : This catalog view returns filegroup information.

sys.sysfilegroups : This is a system table used to get the file group information. This will be removed from future SQL server version. So, use catalog view in the new application design.

Implementation

I have already created a database with few user defined filegroups as shown below

pic1

1. FileGroup catalog view and system table query example

pic2

2. Using filegroup functions

pic3

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: