How to create a FileGroup on SQL SERVER

There has always been an intense discussion about the usage of FileGroups, and how they can help or not performance. 

A lot has been sad, and saying more is just cacofonia, so I will just say this: for my own experience, I can't say that if using FileGroups you will enhance performance, but it can be a solution for storage issues.

If you have a lot of static (read only) historical data, it will definetely improve performance.

Say you have a database called Nexus, and you need to create a FileGroup named IndexFG2, to store Indexes, you can use this:

USE master;

ALTER DATABASE nexus ADD filegroup indexfg2;

ALTER DATABASE nexus ADD FILE ( 
  NAME = newfg2, 
  filename = 'c:\tmp\IndexFG2.ndf',
  size = 6mb, 
  maxsize = 18mb, 
  filegrowth = 1 ) 
TO filegroup indexfg2;

USE nexus;

CREATE TABLE mytest2
  (
     NAME VARCHAR(100)
  ) ;

CREATE INDEX ixmytest
  ON mytest2(NAME)
  ON indexfg2;  


On this example, I used the FG to store and index, but you can create the table on it as well using "ON FileGroupName".

Cheers!

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06