在单独的1-1表中具有varbinary字段的好处是什么？ [英] What is the benefit of having varbinary field in a separate 1-1 table?
问题描述
我需要将二进制文件存储在SQL Server 2005的varbinary（max）列中，如下所示：
FileInfo
- FileInfoId int，PK，identity
- FileText varchar（max）（可以为null）
- FileCreatedDate日期时间等。
FileContent
- FileInfoId int，PK，FK
- FileContent varbinary（max）
FileInfo有一对一与FileContent的关系。当没有要上传的文件时，FileText是被使用的，只有文本将被手动输入。我不知道什么百分比的项目将有一个二进制文件。
我应该创建第二个表。两台桌面设计会有什么性能改进？有没有任何合理的好处？
我发现
通过将LOB明确存储在单独的表中，绝对没有任何东西。您只需添加不必要的复杂性，因为以前的原子更新必须将它们现在分配到两个单独的表中，使应用程序和应用程序事务结构复杂化。
如果LOB内容是整个文件，那么也许您应该考虑升级到SQL 2008并使用 FILESTREAM 。
I need to store binary files in a varbinary(max) column on SQL Server 2005 like this:
FileInfo
- FileInfoId int, PK, identity
- FileText varchar(max) (can be null)
- FileCreatedDate datetime etc.
FileContent
- FileInfoId int, PK, FK
- FileContent varbinary(max)
FileInfo has a one to one relationship with FileContent. The FileText is meant to be used when there is no file to upload, and only text will be entered manually for an item. I'm not sure what percentage of items will have a binary file.
Should I create the second table. Would there be any performance improvements with the two table design? Are there any logical benefits?
I've found this page, but not sure if it applies in my case.
There is no performance nor operational advantage. Since SQL 2005 the LOB types are already stored for you by the engine in a separate allocation unit, a separate b-tree. If you study the Table and Index Organization of SQL Server you'll see that every partition has up to 3 allocation units: data, LOB and row-overflow:
A LOB field (varchar(max), nvarchar(max), varbinary(max), XML, CLR UDTs as well as the deprecated types text, ntext and image) will have in the data record itself, in the clustered index, only a very small footprint: a pointer into the LOB allocation unit, see Anatomy of a Record.
By storing a LOB explicitly in a separate table you gain absolutely nothing. You just add unneeded complexity as former atomic updates have to distribute themselves now into two separate tables, complicating the application and the application transaction structure.
If the LOB content is an entire file then perhaps you should consider upgrade to SQL 2008 and using FILESTREAM.
这篇关于在单独的1-1表中具有varbinary字段的好处是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
