BRUSH-UP WITH SQL DATABASE : Very Basic

When a SQL database is created, two or more files are created to support that database. These files are the Primary Data File and the Transaction Log File. Primary Data Files have an extension of ".MDF" and Transaction Log Files have an extension of ".LDF". A database may also have Secondary Data Files, having an ".NDF" extension. (Note: You can actually use any extension for your files but these are recommended for consistency.)

Every database must have a Primary Data File. This file contains the system tables for the database, pointers to any other files used by the database, and can also contain data. Secondary files contain any data that did not fit in the Primary. Databases do not necessarily have a secondary file, but may have one or more. The Transaction Log File contains all of the log information that may be used to recover a database. Each database has a Transaction Log File, though there may be more than one.

File Group is a important segment of the SQL database,

If, you are wondering WHY ? Read below

Imagine you have a database which is about 20 GB and its keeps growing, but there is only two tables that are getting updated frequently.

Then,

Instead of Backing the FULL DATABASE you can just put those tables on the file group and just backup the file group, reason : why should you backup the whole database when only TWO TABLE is getting updated ?

Save Time and Space.

Lets see how to create a File Group

Creating FILE GROUP
1) Open Enterprise Manager.
2) Open your Local instance.
3) Right click on database ---Choose---create NEW.
4) Give a suitable Name---Click ok.
5) Right click on the database that you just created---Choose Properties.
6) From the Menu---Click on File Group TAB.
7) By default you will see Primary FILE GRP, Create a New FILE GRP underneath that and name it as ---SECONDARY.
8) Click on Ok....Secondary File GRP is now created.
9) Right click the database you created this time --click on ---- Data files.
10) Finally, Create a secondary file (.NEFF) and give it a separate path and select SECONDARY under File Group.

Now, go ahead and create tables, these tables will now be listed under secondary file group rather then Primary.


That's it!

Disclaimer: This information is purely my understanding and views.