using SQL Server 2005 standard edition with SP2
使用带有SP2的SQL Server 2005标准版
I need to design a table where I will be storing a text file (~200KB) along with filename ,description and datetime.
我需要设计一个表格,我将存储一个文本文件(~200KB)以及文件名,描述和日期时间。
Should we design a table where varchar(max) and varbinary(max) data should be stored in a separate table or should column of LOB data types be part of the main table?
我们应该设计一个表,其中varchar(max)和varbinary(max)数据应该存储在一个单独的表中,还是LOB数据类型的列应该是主表的一部分?
Per this thread What is the benefit of having varbinary field in a separate 1-1 table?
根据这个线程在单独的1-1表中使用varbinary字段有什么好处?
there is no performance or operational benefits which I agree to some extent however I can see two benefits
没有我在某种程度上同意的表现或运营利益,但我可以看到两个好处
- store those into a separatable table that can be stored on a separate file group
- you can not rebuild index on a table containing lob data type ONLINE
将它们存储在可分离的表中,该表可以存储在单独的文件组中
您无法在包含lob数据类型ONLINE的表上重建索引
Any suggestions would be appreciated.
任何建议,将不胜感激。
2 个解决方案
#1
7
I would advise against separation. It complicates the design significantly for little or no benefit. As you probably know, SQL Server already stores LOBs on separate allocation units, as described in Table and Index Organization.
我会建议反对分离。它使设计复杂化很少或没有任何好处。您可能知道,SQL Server已将LOB存储在单独的分配单元上,如表和索引组织中所述。
Your first concern (separate filegroup allocation for the LOB data) can be addressed explicitly, as Mikael has already pointed out, by appropriately specifying the desired filegroup in the CREATE TABLE statement.
正如Mikael已经指出的那样,您可以通过在CREATE TABLE语句中适当指定所需的文件组来明确解决您的第一个问题(LOB数据的单独文件组分配)。
Your second concern is no longer a concern with SQL Server 2012, see Online Index Operations for Indexes containing LOB columns. Even prior to SQL Server 2012 you could reorganize indexes with LOBs without problems (and REORGANIZE is online). Given that a full index rebuild is a very expensive operation (an online rebuild must be done at the table/index level, there is no partition online rebuild options), are you sure you want to complicate the design to accommodate for something that is, on one hand, seldom required, and on the other hand, will be available when you upgrade to SQL 2012?
您的第二个问题不再是SQL Server 2012的问题,请参阅包含LOB列的索引的在线索引操作。甚至在SQL Server 2012之前,您可以使用LOB重新组织索引而不会出现问题(并且REORGANIZE在线)。鉴于完整索引重建是一项非常昂贵的操作(必须在表/索引级别进行在线重建,没有分区在线重建选项),您确定要使设计复杂化以适应某些内容,一方面,很少需要,另一方面,当您升级到SQL 2012时可用?
#2
0
I can answer your question in one simple word: Kiss.
我可以用一个简单的词来回答你的问题:吻。
Which of course stands for... Keep It Simple Stupid.
这当然代表...保持简单愚蠢。
Adding a table for is generally a no-no unless you really need one to solve a problem.
添加表通常是禁止的,除非您真的需要一个解决问题。
Generally, I disagree with splitting tables. It adds complexity to databases and code. Having useless columns in a table is a bad thing, but it's not as bad as multiple tables when you only need one.
一般来说,我不同意拆分表。它增加了数据库和代码的复杂性。在表中使用无用的列是一件坏事,但是当你只需要一个表时,它并不像多个表那么糟糕。
Cases where you would consider adding another table:
您考虑添加另一个表的情况:
- Some of your columns are BloB's of data (greater than page size) and they are rarely used and other columns with small data sizes are accessed frequently.
- If you lack a brain.
- If you are evil.
- Or... if you are trying to piss-off your coworkers.
您的某些列是BloB的数据(大于页面大小),很少使用它们,并且经常访问其他数据量较小的列。
如果你缺乏大脑。
如果你是邪恶的。
或者......如果你试图惹恼你的同事。
#1
7
I would advise against separation. It complicates the design significantly for little or no benefit. As you probably know, SQL Server already stores LOBs on separate allocation units, as described in Table and Index Organization.
我会建议反对分离。它使设计复杂化很少或没有任何好处。您可能知道,SQL Server已将LOB存储在单独的分配单元上,如表和索引组织中所述。
Your first concern (separate filegroup allocation for the LOB data) can be addressed explicitly, as Mikael has already pointed out, by appropriately specifying the desired filegroup in the CREATE TABLE statement.
正如Mikael已经指出的那样,您可以通过在CREATE TABLE语句中适当指定所需的文件组来明确解决您的第一个问题(LOB数据的单独文件组分配)。
Your second concern is no longer a concern with SQL Server 2012, see Online Index Operations for Indexes containing LOB columns. Even prior to SQL Server 2012 you could reorganize indexes with LOBs without problems (and REORGANIZE is online). Given that a full index rebuild is a very expensive operation (an online rebuild must be done at the table/index level, there is no partition online rebuild options), are you sure you want to complicate the design to accommodate for something that is, on one hand, seldom required, and on the other hand, will be available when you upgrade to SQL 2012?
您的第二个问题不再是SQL Server 2012的问题,请参阅包含LOB列的索引的在线索引操作。甚至在SQL Server 2012之前,您可以使用LOB重新组织索引而不会出现问题(并且REORGANIZE在线)。鉴于完整索引重建是一项非常昂贵的操作(必须在表/索引级别进行在线重建,没有分区在线重建选项),您确定要使设计复杂化以适应某些内容,一方面,很少需要,另一方面,当您升级到SQL 2012时可用?
#2
0
I can answer your question in one simple word: Kiss.
我可以用一个简单的词来回答你的问题:吻。
Which of course stands for... Keep It Simple Stupid.
这当然代表...保持简单愚蠢。
Adding a table for is generally a no-no unless you really need one to solve a problem.
添加表通常是禁止的,除非您真的需要一个解决问题。
Generally, I disagree with splitting tables. It adds complexity to databases and code. Having useless columns in a table is a bad thing, but it's not as bad as multiple tables when you only need one.
一般来说,我不同意拆分表。它增加了数据库和代码的复杂性。在表中使用无用的列是一件坏事,但是当你只需要一个表时,它并不像多个表那么糟糕。
Cases where you would consider adding another table:
您考虑添加另一个表的情况:
- Some of your columns are BloB's of data (greater than page size) and they are rarely used and other columns with small data sizes are accessed frequently.
- If you lack a brain.
- If you are evil.
- Or... if you are trying to piss-off your coworkers.
您的某些列是BloB的数据(大于页面大小),很少使用它们,并且经常访问其他数据量较小的列。
如果你缺乏大脑。
如果你是邪恶的。
或者......如果你试图惹恼你的同事。