January 14, 2014 at 3:24 pm
Is it true that you can only have mdf files in the Primary Group?
If I created a new File Group, called Group123, I can't move AdventureWorks2008.mdf from Primary to Group123?
If this is true, Clustered Indexes can only be stored in Primary file groups, so the Primary file group will grow larger as the table grows larger even if you have other files in other file groups?
I might be confused... Thanks in advance!
January 14, 2014 at 7:42 pm
smallmoney (1/14/2014)
Is it true that you can only have mdf files in the Primary Group?If I created a new File Group, called Group123, I can't move AdventureWorks2008.mdf from Primary to Group123?
If this is true, Clustered Indexes can only be stored in Primary file groups, so the Primary file group will grow larger as the table grows larger even if you have other files in other file groups?
I might be confused... Thanks in advance!
Firstly, the file extension "mdf" is simply a convention - you could use any file extension you wish for any of the files used for a database. It might get a little confusing it you choose to use your own convention but SQL Server won't care.
As for moving objects such as tables and indexes to other file groups, this has nothing what so ever to do with the name of the physical files. A clustered index may be created in any file group you wish.
What is stored in the primary file group (and in particular the first file which is typically the ".mdf" file) is the definition of the objects in the database. For most of us, this would be a few mega bytes depending how many tables, views, sproc etc and the size of the definition of the views and sprocs.
January 15, 2014 at 12:17 am
smallmoney (1/14/2014)
Is it true that you can only have mdf files in the Primary Group?
No. You could call a database file Readme.txt if you like, SQL won't care. The file extensions are a convention, nothing more.
If I created a new File Group, called Group123, I can't move AdventureWorks2008.mdf from Primary to Group123?
Files can never move between filegroups
If this is true, Clustered Indexes can only be stored in Primary file groups
Not true. Tables and indexes can be stored in any filegroup.
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
January 15, 2014 at 3:43 am
smallmoney (1/14/2014)
Is it true that you can only have mdf files in the Primary Group?
No, that's complete twaddle :Whistling:
smallmoney (1/14/2014)
If I created a new File Group, called Group123, I can't move AdventureWorks2008.mdf from Primary to Group123?
That's because the first file created in the PRIMARY filegroup when the database was created cannot be moved.
smallmoney (1/14/2014)
If this is true, Clustered Indexes can only be stored in Primary file groups
Not true.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply