静态数据库数据是否在自己的Filegroup中? [英] Should static database data be in its own Filegroup?
问题描述
我正在创建一个新的数据库,并有一堆不会改变的静态数据。如果是这样,它将是一个手动过程,它将很少发生。
这个数据是varchars和Geographies的混合。
我猜,总共可以有大约100K左右,超过4个左右的表格。
问题
- 我应该把它们放在一个只读文件组上
- 我可以在设计器中创建表并在创建过程中定义文件组吗?还是只能通过脚本?
- 一旦数据在表中(只读文件组),我可以稍后更改吗?真的很难做到吗?
谢谢。
由于各种原因,VLDB(非常大的数据库)是值得的。
对于100,000行或100 KB,我不会打扰。
这个 SQL Server支持工程团队文章讨论了一个相关的城市传说。 >
在您考虑多个文件/文件组之前,还有另一个(无法找到)您需要300 GB - 1B的数据。
但是,要具体回答
- 个人选择(没有困难和快速的规则)
- 是()在SSMS 2005设计模式中,转到Indexes / Key,数据空间指定。数据存在聚簇索引的位置。没有聚集索引,那么你只能通过
CREATE TABLE(..)ON filegroup
- 是的,但你会必须
ALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITE
与单用户独占模式下的数据库
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.
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
- Personal choice (there is no hard and fast rule)
- Yes (edit:) In SSMS 2005, design mode, go to Indexes/Key, "data space specfication". The data lives where the clustered index is. WIthout a clustered index, then you can only do it via
CREATE TABLE (..) ON filegroup
- Yes, but You'll have to
ALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITE
with the database in single user exclusive mode
这篇关于静态数据库数据是否在自己的Filegroup中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!