Files and filegroup

Question

Files and filegroup

vinod.saraswat

SSCrazy

Points: 2395
More actions
April 19, 2011 at 6:19 am

#237867

Hi All,
I am creating filegroups say aaa. In that i am adding files as follows
a1.mdf,a2.mdf,a3.mdf
And
a1.mdf,a2.ndf,a3.ndf
What is the best practice to add / files with all .mdf extention or one .mdf and rest all .ndf
As both the options are allowed
Please Guide

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Generally, by convention, the first file in the DB (the primary file, FileID=1) has the .mdf extension, all others are .ndf. It's purely by convention, you can do as you wish, just be aware you may confuse people if DBs have multiple .mdf files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

dont create more files in a single head or single drive

|Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

vinod.saraswat SSCrazy Points: 2395 More actions · Answer 3

Thanks for the reply. That means there wont be any differece if i create a1.mdf,a2.mdf this is bcoz I am migrating all the objects from primary to user created file group does it make sense then having multiple file with .mdf

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQL doesn't care what the extensions are.

That said, stick to convention unless you have a good reason. Otherwise you'll just confuse future people.