有没有在SQL Server中使用的sql_variant超过VARCHAR什么好处？ [英] Are there any benefits to using sql_variant over varchar in SQL Server?

问题描述

我现在有一个数据库表设置如下（EAV - 业务的原因是有效的）：

I currently have a database table setup as follows (EAV - business reasons are valid):

• ID - INT（PK）


• 键 - 独特的，VARCHAR（15）


• 值 - VARCHAR（1000）

这让我可以在混合值添加到我的DATABSE以键/值对。例如：

This allows me to add in mixed values into my databse as key/value pairs. For example:

1   | 'Some Text'      | 'Hello World'
2   | 'Some Number'    | '123456'
etc.

在我的C＃代码我用用ADO.Net reader.GetString（2）; 来检索值作为一个字符串，然后让我的代码的其他地方将其转换为所需，例如... Int32.ParseInt （myObj.Value）; 。我期待在通过这可能会更改值列到 SQL_VARIANT 数据类型增强我的表，但我不知道这样做的好处是什么呢？基本上，它有什么优势将我的值列是 SQL_VARIANT 的VS VARCHAR（1000）？

In my C# code I use ADO.Net using reader.GetString(2); to retrieve the value as a string, then have my code elsewhere convert it as needed, for example... Int32.ParseInt(myObj.Value);. I'm looking at enhancing my table by possibly changing the value column to a sql_variant datatype, but I don't know what the benefit of this would be? Basically, is there any advantage to having my value column be of sql_variant vs varchar(1000)?

要更清楚，我读的地方，SQL_VARIANT被返回的数据类型为nvarchar（4000）返回给客户端使得调用（哎哟）！但是，我不能将它转换为它的类型返回之前？显然，我的代码必须加以调整，以存储该值作为对象，而不是一个字符串值。我想，什么是使用的优势/劣势 SQL_VARIANT 对在我目前的状况一些其他类型的？哦，值得一提的是，我打算存储的日期时间，字符串和数值类型的值列（INT，小数等）;我不打算在存储和BLOB或图像，或等。

To be more clear, I read somewhere that sql_variant gets returned as nvarchar(4000) back to the client making the call (ouch)! But, couldn't I cast it to it's type before returning it? Obviously my code would have to be adjusted to store the value as an object instead of a string value. I guess, what are the advantages/disadvantages of using sql_variant versus some other type in my current situation? Oh, and it is worth mentioning that all I plan to store are datetimes, strings, and numerical types (int, decimal, etc) in the value column; I don't plan on storing and blob or images or etc.

推荐答案

有关SQL变种的好处是，你可以存储几种类型的一列，您保留类型的信息

The good thing about sql variant is that you can store several types in a column and you keep the type information.

INSERT INTO MySettings值（名称，MYNAME）。 INSERT INTO
MySettings VALUES（'ShouesNumber，45）;插入MySettings值
（'MyDouble'，31.32）;

Insert into MySettings values ('Name','MyName'); Insert into MySettings values ('ShouesNumber',45); Insert into MySettings values ('MyDouble',31.32);

如果要检索的类型：

select SQL_VARIANT_PROPERTY ( value , 'BaseType' ) as DataType,* from mysettings

和你有：

Datatype Name          Value
-----------------------------
varchar  Name          MyName
int      ShoesNumber   45
numeric  MyDouble      31.32

不幸的是这有几个缺点：

Unfortunately this has several drawbacks:

1. 不是非常快


2. 不能很好的ORM框架支持

这篇关于有没有在SQL Server中使用的sql_variant超过VARCHAR什么好处？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！