New created table placed in additional file

Question

I want to store every new created table in an additional file located in the folder "C:\data\".

I think this has something to do with a model database, but I have less clearity on that. Can anybody suggest me a way to achieve this?

Thanks!!!

Answer 1

The answer is to create a FileGroup, and set that FileGroup as the default

ALTER DATABASE  [dscat] 
ADD FileGroup [MyDataGroup] 
ALTER DATABASE [dscat] 
ADD FILE 
    ( NAME = test1dat3,
    FILENAME = 'c:\temp\dscat_target_for_tables.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB)
TO FILEGROUP  [MyDataGroup] 

ALTER DATABASE [dscat] 
MODIFY FILEGROUP [MyDataGroup] DEFAULT

Then a simple CREATE TABLE will use the new default filegroup

CREATE TABLE TEST (kf INT primary key , df varchar(99)) 

where-as you can target a specific FileGroup (such as PRIMARY) with the ON keyword:

CREATE TABLE MyUsers (kf INT primary key , df varchar(99)) ON  [PRIMARY] 

REF a) https://msdn.microsoft.com/en-us/library/ms174979.aspx b) https://technet.microsoft.com/en-us/library/aa275464(v=sql.80).aspx