SQL Server 文件和文件组
我想不出为什么我们需要在一个文件组中包含多个文件的任何原因。 我之所以想到这种方式是因为我们可以从T-SQL（最终用户）级别控制文件组，但无法从T-SQL（最终用户）级别控制文件组中的各个文件。 对于为什么仍然需要文件有什么评论或想法吗？
提前致谢， 乔治
I can not think of any reasons why we need to have multiple files inside a file group. The reason why I think of this way is we can control from T-SQL (end user) level about file group, but can not control from T-SQL (end user) level about individual files of a file group. Any comments or ideas why files are still needed?
thanks in advance,
George
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
每个文件组包含多个文件仅出于以下原因有用：
有一个“都市传说”，SQL Server 每个文件只使用 1 个线程，因此文件的数量应该与 CPU 的数量相匹配。 然而，这是错误的，如 Microsoft 在此讨论了。
从历史上看，还有另一个原因。 信不信由你，在 SQL Server 4.2 到 7 的时代，sql server 有时安装在 FAT32 文件系统上，该文件系统有 4 GB 的文件限制。 将文件链接在一起（我们现在称之为文件组）的能力是解决文件系统限制并允许在基于 FAT 的安装上使用大于 4gig 的数据库的一种方法。
Having multiple files per file group is only useful for the following reasons:
There is 'urban legend' that SQL Server uses only 1 thread per file, so that the number of files should match the number of CPU's. This is however false, as discussed by Microsoft here.
Historically, there is another reason. Believe it or not in the days of SQL Server 4.2 through 7 sql server was sometimes installed on FAT32 file systems which had a 4 gig file limit. The ability to chain files together (in what we now call file groups) was a way to work around file system limitations and allow DBs larger than 4gigs on FAT based installs.
老线程，我知道，但这对我来说是有意义的：当时 Windows 文件系统 FAT32 中的最大文件大小是 2GB。 如果你的数据库文件变大了，你就完蛋了（我的 MS Access 数据库就发生过一次）。 因此，他们允许定义最大文件大小（例如：2GB），并且您可以添加更多文件。 如果您的数据库增长并且超出了最大大小，则下一个文件将被填充，直到填满，依此类推。 所有这些文件都可以作为一个文件组进行寻址。 您可以通过选择文件组来定义表数据位置，但您看不到表数据最终将位于该文件组中的哪个文件中。 您所知道的是，您的表数据可能最终位于文件组中的任何文件中。
通过这种“分割”，您的文件系统永远不会看到大于最大文件大小（此处：2GB）的文件，尽管数据库中的表可能大很多倍。
如今，设置多个文件对于将大型数据文件“切成”较小的部分以进行基于文件的备份非常有用（询问您的网络管理员他们想要什么，因为在备份期间将大型（例如 1TB）文件写入分区需要很长时间，即使在快速情况下也是如此RAID。所有其他写入操作都需要等待很长时间。较短的等待间隔可以让高优先级操作更快地执行。
如果您关心同一个表的并行访问，请考虑水平分区，如 http://msdn.microsoft.com/en-us/library/ms188730%28v=sql.105%29.aspx。 这允许将表的数据分布在不同的硬盘上，例如“磁盘 R: 上一月的所有销售”、“磁盘 S: 上二月的所有销售”，而无需创建单独的表。 在表分区过程中，您可以定义哪个部分应进入哪个文件组。
old thread, i know, but here is what makes sense to me: back in the day max file size in windows filesystem FAT32 was 2GB. If your database-file got bigger you were screwed (happened to me with a MS Access-Database once). Hence they allowed to define a max filesize (like: 2GB) and You could add more files. If your database grew and the max size got exceeded the next file got filled until that was full and so on. All those files can be addressed as one filegroup. You can define a tables data-location by choosing a filegroup, but you don't see in which file within that filegroup the tabledata will end up. All You know is that your tables data can end up in any of the files within the filegroup.
By this "splitting" Your filesystem never sees a file larger than the max filesize (here: 2GB) although tables in Your Database can be many times larger.
Today setting up multiple files can be useful to have large datafiles "chopped" into smaller pieces for filebased backup (ask your network admins what they want, because during backup writing a large (like 1TB) file into a partition takes long, even in fast RAID. All other writing operations would need to wait a long time. Shorter waiting intervals let high prioritized operations come to execution quicker).
If You care for parallel access of the same table consider horizontal partitioning as in http://msdn.microsoft.com/en-us/library/ms188730%28v=sql.105%29.aspx. this allows to spread the data of a table over different harddisks, like "all sales of January on disk R:", "all sales of February on disk S:", without creating separate tables. During the procedure of partitioning of a table You can define which part shall go to what filegroup.
我可以提供很长的解释，但 MSDN 做得很好 这里。 您可能不需要在一个文件组中包含多个文件，但并非每个人都这样。
I could provide a long explanation but MSDN does a good job of it here. It may be that you specifically don't need to have more than one file in a file group, but that is not true of everybody.