在 SQL Server 中使用 sql_variant 而不是 varchar 有什么好处吗? [英] Are there any benefits to using sql_variant over varchar in SQL Server?

查看:44
本文介绍了在 SQL Server 中使用 sql_variant 而不是 varchar 有什么好处吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我目前有一个数据库表设置如下(EAV - 商业原因有效):

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

  • Id - int (PK)
  • 键 - 唯一,varchar(15)
  • 值 - varchar(1000)

这允许我将混合值作为键/值对添加到我的数据库中.例如:

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 数据类型来增强我的表,但我不知道这样做有什么好处?基本上,将我的 value 列设为 sql_variantvarchar(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 与其他类型相比有什么优点/缺点?哦,值得一提的是,我打算在 value 列中存储日期时间、字符串和数字类型(int、decimal 等);我不打算存储和 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.

插入 MySettings 值 ('Name','MyName');插入MySettings 值 ('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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆