SQL Server 2008 中的枚举类型?

发布于 2024-08-23 11:33:38 字数 487 浏览 9 评论 0原文

SQL Server 中是否有某种机制允许类似枚举类型的功能?

例如,如果我有一个名为“UpdateStatus”的列,它通常会使用单个字母值进行设置,如下所示:

  1. D
  2. X
  3. U
  4. I

这可能等同于很多事情。这会导致混乱。另一种方法是让它成为一个字符串列,如下所示:

  1. 下载
  2. 已删除已
  3. 更新已
  4. 初始化

但这有其自身的问题。最终有人会写这样的东西:where UpdateStatus = 'Initalized'(拼写错误)。另外,我听说琴弦的断键性能并不那么好。

那么,SQL Server 是否有任何类型的枚举类型可以帮助解决这个问题?基本上我正在寻找编译时检查正在比较的值(即“初始化”)是否是值列表的一部分。

我正在使用 SQL Server 2008。

Is there some kind of mechanism in SQL Server to allow Enumerated type like functionality?

For example, if I have a column Called "UpdateStatus" it usually gets setup with single letter values like so:

  1. D
  2. X
  3. U
  4. I

This could equate to a lot of things. That leads to confusion. The alternative is to have it be a string column like this:

  1. Downloaded
  2. Deleted
  3. Updated
  4. Initialized

But that has its own problems. Eventually someone is going to write something like this: where UpdateStatus = 'Initalized' (spelled wrong). Plus I hear that keying off of strings is not all that performant.

So, is there any kind of enumerated type for SQL Server that can help out with this? Basically I am looking for compile time checking that a value being compared (ie "Initialized") is part of a list of values.

I am using SQL Server 2008.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

风为裳 2024-08-30 11:33:38

为什么没有包含代码和描述的查找表。创建此查找表的外键将导致仅使用有效的代码。

Why not have lookup table that contains the code and description. Creating a foreign key to this lookup table will result in only valid codes being used.

迷离° 2024-08-30 11:33:38

除了查找表(FK)之外,在简单的情况下,您还可以使用检查约束:

CREATE TABLE my_table ( 
    UpdateStatus VARCHAR2(11) 
      CHECK( UpdateStatus IN ('Downloaded', 'Deleted', 'Updated', 'Initialized'))
)

Besides lookup tables (FKs), in simple cases, you can use check constraints:

CREATE TABLE my_table ( 
    UpdateStatus VARCHAR2(11) 
      CHECK( UpdateStatus IN ('Downloaded', 'Deleted', 'Updated', 'Initialized'))
)
感情旳空白 2024-08-30 11:33:38

我见过的唯一方法是使用 UDF 来评估枚举的字符串表示形式是否有效。它很慢,很痛苦,而且通常不值得,但至少你有办法大声失败而不是默默失败。

请记住,您不能在 UDF 中 RAISERROR,因此您必须故意导致错误,并单独记录。

最终,目前,问题的“完美”解决方案是从另一方着手——您可以通过代码优先的 ORM 来实现这种心态,这将允许您在代码中使用本机枚举,并且迁移时将正确创建相应的 SQL 查找。

希望我们能尽快获得枚举,我们感觉有点被忽视了。

The only way that I've seen this done is by using a UDF to evaluate whether or not the enum's string representation is valid. It's slow, it's painful, and usually not worth it, but at least you have a way to fail loudly instead of silently.

And remember, you can't RAISERROR in a UDF so you have to cause an intentially cause an error, and log separately.

Ultimately, at the moment, the 'perfect' solution to the problem would be to approach from the other side -- you can achieve this mentality with a code-first ORMs, which would allow you to use native enums in your code, and the corresponding SQL lookups will be created properly in migration.

Here's to hoping we get enums soon, we're feeling a little left out.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文