SQL 我应该使用 SQL_Variant 数据类型吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9039455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should I use SQL_Variant data type?
提问by SQL Learner
using SQL Server 2005 with SP4 and I am designing a database table.
使用带有 SP4 的 SQL Server 2005，我正在设计一个数据库表。
Here is the table DDL
这是表 DDL
CREATE TABLE CPSync4D.ProjectProfilerOption
(
ProjectProfilerOptionID INT IDENTITY(1,1) CONSTRAINT PK_ProjectProfilerOption_ProjectProfilerOptionID PRIMARY KEY
,ProjectID INT CONSTRAINT FK_ProjectProfilerOption_Project_ProjectID FOREIGN KEY(ProjectID) REFERENCES CPSync4D.Project(ProjectID) ON DELETE CASCADE
,ProfilerOptionID TINYINT CONSTRAINT FK_ProjectProfilerOption_ProfilerOption_ProfilerOptionID FOREIGN KEY(ProfilerOptionID) REFERENCES CPSync4D.ProfilerOption (ProfilerOptionID)
,ProfilerOptionValue sql_variant NOT NULL
)
Go
profileroptionvalue column can hold either a string upto 30 characters, integer or decimal values e.g. values are "ProfilerValueType", or 12.52 or 20 etc. (no more than than two decimals and integer values are less than 100)
profileroptionvalue 列可以包含最多 30 个字符的字符串、整数或十进制值，例如值是“ProfilerValueType”，或 12.52 或 20 等（不超过两位小数，整数值小于 100）
Should I use sql_variant or varchar(30)...? I never used sql_variant before and not sure any implication of not using in terms of database design.
我应该使用 sql_variant 还是 varchar(30)...？我以前从未使用过 sql_variant 并且不确定在数据库设计方面不使用的任何含义。
Any pitfalls of using sql_variant...with .net code
使用 sql_variant...与 .net 代码的任何陷阱
采纳答案by Lieven Keersmaekers
10 reasons to explicitly convert SQL Server data types
As a general rule, you should avoid using SQL Server's sql_variant data type. Besides being a memory hog, sql_variant is limited:
- Variants can't be part of a primary or foreign key. (this doesn't hold as of SQL Server 2005. See update below)
- Variants can't be part of a computed column.
- Variants won't work with LIKE in a WHERE clause.
- OLE DB and ODBC providers automatically convert variants to nvarchar(4000) — ouch!
To avoid problems, always explicitly convert sql_variant data types as you use them. Use any method you please, just don't try to work with an unconverted sql_variant data type.
作为一般规则，您应该避免使用 SQL Server 的 sql_variant 数据类型。除了是一个内存猪，sql_variant 是有限的：
- 变体不能是主键或外键的一部分。（这在 SQL Server 2005 中不成立。请参阅下面的更新）
- 变体不能是计算列的一部分。
- 变体不能在 WHERE 子句中与 LIKE 一起使用。
- OLE DB 和 ODBC 提供程序自动将变体转换为 nvarchar(4000) — 哎哟！
为避免出现问题，请始终在使用时显式转换 sql_variant 数据类型。请使用任何方法，只是不要尝试使用未转换的 sql_variant 数据类型。
I haven't used sql_variantbefore but with these restrictions and performance implications in mind, I would first look at alternatives.
我以前没有使用sql_variant过，但考虑到这些限制和性能影响，我会先看看替代方案。
Following would be my most to least prefered solution
以下将是我最不喜欢的解决方案
- Simply create three different columns. 3 Different data types (should) mean 3 different ways of interpreting it both at the client side and server side.
- If that is not an option, use a
VARCHARcolumn so you can at least useLIKEstatements. - Use the
sql_variantdata type.
- 只需创建三个不同的列。3 不同的数据类型（应该）意味着在客户端和服务器端有 3 种不同的解释方式。
- 如果这不是一个选项，请使用一
VARCHAR列，以便您至少可以使用LIKE语句。 - 使用
sql_variant数据类型。
EditCudo's to ta.speot.is
将Cudo's编辑为 ta.speot.is
Variants canbe part of a primary of foreign key
A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. This is 900 bytes
唯一键、主键或外键可以包含 sql_variant 类型的列，但构成特定行键的数据值的总长度不应超过索引的最大长度。这是 900 字节
回答by Jeff Moden
I know my answer is a bit late but the table being made here looks a bit like an application configuration table. As an alternative to the suggestions given, let's think about not limiting ourselves to 30 or even 8000 characters. Let's also make it a bit more self contained and user definable.
我知道我的回答有点晚了，但这里制作的表格看起来有点像应用程序配置表。作为给出的建议的替代方案，让我们考虑不要将自己限制为 30 甚至 8000 个字符。让我们也让它更加自包含和用户可定义。
With those thoughts in mind, why not save the "profile" information as an XML data type which would even allow multiple levels of settings? You probably wouldn't need such columns as ProfilerOptionID anymore and might be able to get this down to one simple control table.
考虑到这些想法，为什么不将“配置文件”信息保存为 XML 数据类型，它甚至允许多级设置？您可能不再需要 ProfilerOptionID 之类的列，并且可能能够将其归结为一个简单的控制表。
回答by David Bridge
Its worth noting that it is not possible to copy the sql_variant column implicitly.
值得注意的是，不能隐式复制 sql_variant 列。
e.g. Create a backup schema of CPSync4D.ProjectProfilerOption called CPSync4D.ProjectProfilerOption_bkp
例如，创建一个名为 CPSync4D.ProjectProfilerOption_bkp 的 CPSync4D.ProjectProfilerOption 备份模式
and then
进而
Insert into CPSync4D.ProjectProfilerOption_bkp
(
ProjectProfilerOptionID
,ProjectID
,ProfilerOptionID
,ProfilerOptionValue
)
SELECT
ProjectProfilerOptionID
,ProjectID
,ProfilerOptionID
,ProfilerOptionValue
FROM CPSync4D.ProjectProfilerOption
All of the values for ProfilerOptionValue in the backup table will be varchar
备份表中 ProfilerOptionValue 的所有值都将是 varchar
Note also: I have been told that the SQL_Variant cannot be used in replication but this is not true. Certainly it can be done with SQL 2008 R2 (that I am using) because I have just done it but this may have been true for older versions(I don't have any older versions to check with so cannot confirm or deny this).
另请注意：有人告诉我 SQL_Variant 不能用于复制，但事实并非如此。当然，它可以用 SQL 2008 R2（我正在使用）来完成，因为我刚刚完成了它，但这可能适用于旧版本（我没有任何旧版本可供检查，因此无法确认或否认这一点）。
What is true though, is that if you do replicate a table with a SQL Variant in it and have a lot of data and then something goes wrong and you need to fix the data manually, then you might have a nasty piece of SQL to write. This is because when copying the data you cannot copy with several base types in the same copy statement. I guess that replication doesn't have this issue because it does not copy multiple rows (with the obvious exception of the snapshot but that uses bcp).
但事实是，如果您确实复制了一个包含 SQL Variant 的表并且有大量数据，然后出现问题并且您需要手动修复数据，那么您可能需要编写一段令人讨厌的 SQL . 这是因为在复制数据时，您不能在同一个复制语句中使用多个基本类型进行复制。我猜复制没有这个问题，因为它不复制多行（快照明显例外，但使用 bcp）。
ps. I realise this is an old post but put this here for other future visitors with the same question.
附：我意识到这是一篇旧帖子，但将其放在这里供其他未来有相同问题的访客使用。
