Leverage file groups - SQL Server Tutorial

- [Instructor] We just saw that keeping data files separate from log files can have a positive impact on the performance of a SQL Server database. But SQL Server can also use multiple data files that work together to store the information for the database. SQL Server data files store all of the user-created objects, including tables and indexes. By creating multiple file groups, you can divide these resources up in order to place them on separate locations as well. For instance, having an index on a separate physical disk drive from the table that it references allows SQL Server to read and write to both objects simultaneously. Let's see how to configure this in Management Studio. I can create multiple file groups on a new database by right clicking on the Databases folder in Object Explorer and choosing New Database. Then, in the New Database window, I'll come all the way to the bottom and press the Add button. This will allow me to add in a new database file to the system that I'm about to set up. Below Logical Name, I can click to add in a new logical name for the file. I'll call this SecondaryFileGroup. Then, under the Filegroup category, we can click where it says PRIMARY, click on the dropdown menu, and then choose <new filegroup> to create a filegroup to add this file to. I'll call this FileGroup2. Go ahead and press OK to create the new filegroup and we'll create the file within that filegroup. Now you can continue going through the process of creating this database, and we'll have a secondary filegroup that we can add files to. Instead what I want to do is press Cancel to not create this database. So what if you already have an existing database and want to add in a new filegroup to divide up your objects across drives? You can do that with T-SQL commands and the ALTER DATABASE statement. Let's take a look at that by creating a new database manually, and I'll call mine FileGroupExample. I'll go ahead and highlight these lines here on my code sample, and press execute. That'll create a new database based off of the default specification of the master database within my server instance. Then, we can alter that database to include a new filegroup. I'm going to alter database FileGroupExample and we'll add in a new filegroup called IndexFG. Go ahead and highlight those lines and execute them. So now my database has an additional filegroup. Now I need to create the additional file to actually store that data in. Once again, we'll alter the database FileGroupExample and we'll add in a data file here, using the ADD FILE line. The name of the file will be IndexData. The file name will be the path to the data, and right now I'm going to use the C drive, and I've got a temporary folder set up already called TempDatabases that I'll save it into, and the name of the file will be IndexData.ndf. In SQL Server, secondary files will have the .ndf extension as opposed to the primary files .mdf extension. I'll set the initial size to 10 megabytes, the maximum size to 100 megabytes, and we'll allow the file to grow in 10 megabyte increments. Then we'll add this file to the filegroup that we just created called IndexFG. I'll highlight these lines and execute 'em. I get the message that the command was completed successfully, so I'm ready to move on to the next step. Next I just want to take a quick peak at the current location of all the data and log files we have associated with the database that we just set up. I'm going to run the same SELECT statement that we saw in the previous movie. It's going to select the columns name, physical name, and state description from a system table called sys.master_files. From all of the objects or all the records in that data table, we're going to filter down to just where the database ID is equal to the FileGroupExample database that we've been working with. I'll execute these lines and we'll see three records. We've got the main data file called FileGroupExample and it's at this location here. Just going to expand this column a little bit. And you can see it ends in the .mdf file extension. The log file for our database lives at the same location with the .ldf extension. And we have that additional secondary data file that ends in the .ndf location that we saved into the TempDatabases folder. So those are the three different files that we have associated with our current database. Now let's start adding in some objects. I'm going to create a new table called dbo.Employees and it'll have a couple of different fields. The EmployeeID field will serve as the identity column for the data table and it'll be set to the PRIMARY KEY. And by doing so, SQL Server is automatically going to create an index based off of this column. Next we'll have the EmployeeName field, EmployeePhone, and EmployeeSalary. I'll go ahead and highlight these lines and execute 'em to create the table. Notice that I didn't say anything about which filegroup to place this table in. Because of that, it's going to place it in the default filegroup, which is the main .mdf file for the database. Next, I'm going to create an index, but this time we'll specify a specific filegroup. We'll create an index called IX_EmployeeName and we'll create it on the employees table based off of the EmployeeName field. Then we'll specify that we want this to be on the IndexFG, which is the index filegroup that we just created. I'll highlight these four lines and execute them to create the index. Then I'll scroll down just a little bit further to reveal this SELECT statement. Here we're going to check the location of the database's indexes by referencing a couple of system tables. We're going to be using the sys.indexes table, the sys.filegroups table, and the sys.all_objects table. From these tables we'll pull out a couple of different fields. We'll pull the name and type fields from the sys.all_objects tables, the name and index_id fields from the sys.indexes table, and the name field here from the sys.filegroups table. Finally, we'll filter out all the records to just the records where the type is equal to U, which stands for user-defined tables. Let's go ahead and highlight these lines and execute them. And we'll see we get two different records returned. These represent the two different indexes that are currently present in this database. So now we know we have the employees table as well as its PRIMARY KEY index built into the primary filegroup for the database, which is currently residing inside of this standard SQL Server folder. We also created an EmployeeName index into a completely separate filegroup called IndexFG. Now in my system, I actually stored them on the same physical hard drive. So having multiple filegroups on the same physical disk can benefit the organization of the database, but it won't increase performance since the drive will still be accessing each file one at a time. By moving filegroups to separate drives and intelligently dividing database objects between those drives, input/output bottlenecks can be reduced and the performance of the database can be enhanced. Now before I wrap up this exercise, I want to make sure that I scroll down and clean up the server instance. We'll go ahead and switch into tempdb, and then we'll drop the database FileGroupExample.