静态数据库数据应该位于其自己的文件组中吗？
我正在创建一个新的数据库，并且有一堆不会改变的静态数据。 如果确实如此，这将是一个手动过程，而且这种情况很少发生。
该数据是 varchar 和 Geography 的混合。
我猜总共大约有 100K 左右，超过 4 张左右的桌子。
问题
- 我应该将它们放在只读文件组中吗？
- 我可以在设计器中创建表并在创建过程中定义文件组吗？ 或者只能通过脚本实现？
- 一旦数据进入表中（在只读文件组上），我以后可以更改它吗？ 真的很难做到吗？
谢谢。
I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely.
This data is a mix of varchars and Geographies.
I'm guessing it could be around 100K or so in total, over 4 or so tables.
Questions
- Should I put these on a READ ONLY filegroup
- Can I create the tables in the designer and define the filegroup during creation? Or is it only possible via a script?
- Once the data is in the table (on a read only filegroup), can I change it later? Is it really hard to do that?
thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
出于各种原因，对于 VLDB（超大型数据库）来说这是值得的。
对于 100,000 行或 100 KB，我不会打扰。
SQL Server 支持工程团队 文章讨论了相关的“都市传说”之一。
还有另一种（找不到），您需要 300 GB - 1B 的数据，然后才应考虑多个文件/文件组。
但是，要具体回答
CREATE TABLE (..) ON filegroup来完成此操作。ALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITE使用数据库处于单用户独占模式It is worth it for VLDB (very large databases) for assorted reasons.
For 100,000 rows or 100 KB, I wouldn't bother.
This SQL Server support engineering team article discusses one of the associated "urban legends".
There is another one (can't find it) where you need 300 GB - 1B of data before you should consider multiple files/filegroups.
But, to answer specifically
CREATE TABLE (..) ON filegroupALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITEwith the database in single user exclusive mode将数据放入只读空间不太可能造成伤害，但我不确定您会获得显着的收益。 只读文件组（或 Oracle 中的表空间）可以给您带来 2 个优势； 每次进行完整备份时备份的数量更少，并且数据的安全性更高（例如，它不能被错误更改，通过其他工具访问数据库等）。 对于较大的数据库来说，备份优势最为明显，因为备份窗口很紧，因此投入少量精力来排除文件组是很有价值的。 安全性取决于站点、数据等的性质（如果您确实从常规备份中排除只读空间，请确保您在任何保留的备份磁带上获得一份副本。我倾向于备份一次只读空间一个月。）
我对设计师不熟悉。
更改为只读或从只读更改并不麻烦。
It is unlikely to hurt to put the data in to a read only space but I am unsure you will gain significantly. A read-only file group (or tablespace in Oracle) can give you 2 advantages; less to back-up each time a full backup is taken and a higher level of security over the data (e.g. it cannot be changed by a bug, accessing the DB via another tool, etc). The backup advantage is most true with larger DBs where backup windows are tight so putting a small amount of effort into excluding file groups is valuable. The security one depends on the nature of the site, data, etc. (if you do exclude the read-only space from regular backups make sure you get a copy on any retained backup tapes. I tend to backup up read-only spaces once a month.)
I am not familiar with designer.
Changing to and from read only is not onerous.
我认为你在这里读到的任何内容都可能是猜测，除非你有任何证据表明它已经被实际尝试和推荐过——对我来说，这看起来像是一个新颖但不太可能的想法。 您是否有理由怀疑传统做法不会令人满意？ 尝试一下并找出答案应该相当容易。 如果有机会，请发布您的结果。
I think anything you read here is likely to be speculation, unless you have any evidence that it's been actually tried and recommended - to me it looks like a novel but unlikely idea. Do you have some reason to suspect that conventional practices will be unsatisfactory? It should be fairly easy to just try it and find out. Post your results if you get a chance.