为什么我们需要 SQL Server 中的辅助数据文件？
在 SQL Server 2005 上创建新数据库时，我总是忽略此选项，只是因为我们可以忽略一些我们不理解的内容并保持原样。 （我不太喜欢 DBA）
所以现在我很好奇它是关于什么的。
根据您的经验，您认为我们什么时候需要将辅助数据文件添加到我的数据库中？为什么需要它？
I always ignore this option when creating a new database on SQL Server 2005, simply because we can ignore something that we do not understand and leave it as it is. (I'm not so into DBA)
so now I am curious what it is about.
From your experience, when do you think we need to add secondary data files to my database and why do we need it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在很多情况下这都是有用的 - 首先，出于可用性原因，最好只将系统数据保留在主数据文件中（对于 Sql2k5 及更高版本，只要主数据文件可用，数据库就可以联机，允许您修复/恢复/等非系统数据，同时拥有尽可能多的在线数据）。使用辅助文件的其他一些情况：
Lots of cases where this can be useful - to start, for availability reasons it's always best to keep only system data in your primary data file (with Sql2k5 and up, so long as the primary data file is available, the database can be brought online, allowing you to repair/restore/etc. non-system data while having as much online as possible). Some other cases to use secondary file(s):
除了这里其他回复已经提到的性能方面之外，还有一个安全问题。
数据库的目录视图（跟踪表、列、权限和所有系统内容）始终位于主数据文件中，您无法更改它。
如果您可以将主数据文件中的系统目录数据分离出来，并将用户数据放入辅助文件中，则主文件会更小，更新和插入也会少得多，从而减少了因坏盘等原因而损坏的可能性部门被最小化。
如果您的系统目录视图（及其基础表）被损坏或破坏，您的整个数据库将被毁掉 - 因此您肯定希望损坏主数据文件的机会最小。
对于中小型数据库来说，这可能不是一个大问题，但在较大的设置中可能是需要考虑的一个问题。
Besides the performance aspects already mentioned by the other replies here, there's also a safety issue.
The database's catalog views (that keep track of the tables, columns, permissions and all that system stuff) are always located in the primary data file and you can't change that.
If you can separate out that system catalog data in the primary data file, and put your user data into a secondary file, the primary file is smaller, gets a lot less updates and inserts, and thus the chance of corruption by e.g. a bad disk sector is minimized.
If your system catalog views (and their underlying tables) are damaged or destroyed, your entire database will be toast - so you definitely want to have the least chance of damaging that primary data file.
That's probably not a big issue in a smaller to medium size database, but might be a point to consider in a larger setup.
重要原因之一是 I/O。对数据进行分区的能力意味着 I/O 现在分布在多个驱动器/lun/等上。这可以大大提高性能。
One of the big reasons is I/O. The ability to partition your data means that I/O is now spread across multiple drives/luns/etc. which can boost performance substantially.