filegroups..something bizarre

[divinyl]

Hi all

I'm trying to understand files and filegrouping in SQL server. I thought that filegroups were a logical container of files. What i've somehow managed to do is create a table on a filegroup in which i forgot to create a file. Now looking into the table properties i can see that it is part of the "test" filegroup" but there are no files defined in this filegroup. So.... where does this table exist??? I thought perhaps it might put it into the default filegroup but it doesn't indicate this in the UI.

Can anyone shed some light on this?

Div

 

[Catadmin]

You are correct. A filegroup is a container for files. When you create additional filegroups, each has a default secondary file in it with which to store data unless you specifically create secondary files with a specific name in that additional filegroup.

Objects get created in the filegroup that is specified as the default filegroup at that time, but on any file within that group depending on which file actually has the space for it.

BTW, The primary purpose of having multiple files in a single filegroup is to divide the I/O for your data/objects access amongst multiple logical or physical drives (think of it like a striped RAID array). If you have no need of this functionality, you really don't need to worry about it.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[divinyl]

Thanks CatAdmin! Didn't know about the default secondary file but that makes sense..

We are going to use filegrouping in an upcoming project hence the research now - and as you said, the purpose will be to divide the files across multiple drives for better performance.

Thanks,
Div

 

[Catadmin]

Glad I could help. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"