SQL Server 中的用户定义数据类型有多酷？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/657568/
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
How cool are User Defined Data Types in SQL Server?
提问by David Vidmar
Are User Defined Data Types in SQL Server something that a intermediate SQL user should know and use?
SQL Server 中的用户定义数据类型是中级 SQL 用户应该知道和使用的吗？
What are pros and cons of using UDTs?
使用 UDT 的优缺点是什么？
采纳答案by HLGEM
Never use them is my advice. You are in a world of hurt if you ever have to change the definition. Perhaps this has improved since SQL Server 2000 and someone with more familiarity with the newer versions can tell you whether it is now safe to get in the water, but until I had confirmation of this and had checked it out myself with a test, I wouldn't put it on my production system.
永远不要使用它们是我的建议。如果你不得不改变定义，你就处于一个受伤的世界。也许自 SQL Server 2000 以来这种情况有所改善，并且更熟悉新版本的人可以告诉您现在下水是否安全，但是直到我确认这一点并通过测试自己检查出来之前，我不会不要把它放在我的生产系统上。
Check out this question for details: How to change the base type of a UDT in Sql Server 2005?
查看此问题以获取详细信息： 如何更改 Sql Server 2005 中 UDT 的基本类型？
回答by Mark Brittingham
I do notuse code-based UDTs because I don't think that the extra complexity warrants the advantages. I douse T-SQL UDTs because there's very little extra complexity so that the advantages are worth the effort. (Thanks go to Marc_s for pointing out that my original post was incomplete!)
我不使用基于代码的 UDT，因为我认为额外的复杂性不足以保证优势。我确实使用 T-SQL UDT，因为几乎没有额外的复杂性，因此这些优点值得付出努力。（感谢 Marc_s 指出我的原始帖子不完整！）
Regarding Code-based UDTs
关于基于代码的 UDT
Think of it this way: if your project has a managed code component (your app) and a database component (SQL Server) what real advantage do you gain from defining managed code in the database? In my experience? None.
可以这样想：如果您的项目有一个托管代码组件（您的应用程序）和一个数据库组件（SQL Server），那么您从在数据库中定义托管代码获得什么真正的优势？在我的经验中？没有任何。
Deployment is more difficult because you'll have to add assemblies to your DB deployment and alter these assemblies, add files, etc. within SQL Server. You'll also have to turn on the CLR in SQL Server (not a big deal but no one's proven to me that this won't have a performance/memory penalty). In the end, you'll have exactly what you would have had if you had simply designed this into your application's code. There may be some performance enhancement but it really strikes me as a case of premature optimization - especially since I don't know if the overallperformance suffers due to having the CLR on versus off.
部署更加困难，因为您必须向数据库部署添加程序集并在 SQL Server 中更改这些程序集、添加文件等。您还必须在 SQL Server 中打开 CLR（没什么大不了的，但没有人向我证明这不会有性能/内存损失）。最后，如果您只是将其设计到您的应用程序代码中，您将拥有完全相同的东西。可能会有一些性能增强，但它确实让我觉得是过早优化的一个案例 - 特别是因为我不知道整体性能是否因打开与关闭 CLR 而受到影响。
Note: I'm assuming that you would be using SQL Server's CLR to define your types. HLGEM talks about SQL Server 2000 but I'm not familiar with 2000 and thought it only had UDFs and not UDTs in externally-defined dlls (but don't quote me...I really am not familiar with it!).
注意：我假设您将使用 SQL Server 的 CLR 来定义您的类型。HLGEM 谈到了 SQL Server 2000，但我对 2000 并不熟悉，并认为它在外部定义的 dll 中只有 UDF 而没有 UDT（但不要引用我...我真的不熟悉它！）。
Regarding T-SQL UDTs
关于 T-SQL UDT
T_SQL UDTs can be defined in SQL alone (go to "Programmability | Types | User-defined Data Types" in SQL Server Management Studio). For standard UDTs I wouldin fact recommend that you master them. They are quite easy and can make your DDL more self-documenting and can enforce integrity constraints. For example, I define a "GenderType" (char(1), not nullable, holding "M" or "F") that ensures that only appropriate data is permitted in the Gender field.
T_SQL UDT 可以单独在 SQL 中定义（转到 SQL Server Management Studio 中的“可编程性 | 类型 | 用户定义的数据类型”）。对于标准的UDT我会其实建议你掌握它们。它们非常简单，可以使您的 DDL 更具自文档性，并且可以强制执行完整性约束。例如，我定义了一个“GenderType”（char(1)，不可为空，包含“M”或“F”）以确保在 Gender 字段中只允许适当的数据。
UDTs are pretty easy overall but this articlegives a pretty good example of how to take it to the next level by defining a Rule to constrain the data permitted in your UDT.
UDT 总体上非常简单，但本文提供了一个很好的示例，说明如何通过定义规则来限制 UDT 中允许的数据，从而将其提升到一个新的水平。
When I originally answered this question I was fixed on the idea of complex, code-defined types (smacks palm to forehead). So...thanks Marc.
当我最初回答这个问题时，我固定在复杂的、代码定义的类型（从手掌到额头）的想法上。所以……谢谢马克。
回答by Katherine Elizabeth Lightsey
The pro of user defined types is addressed quite wellby Alex Papadimoulis. The cons have been well stated here.
Alex Papadimoulis很好地解决了用户定义类型的优点。这里已经很好地说明了缺点。
I would also like to point out that the sp_bindrulefunction has been deprecated, as noted by Alex's post. I'm not sure when it was deprecated but it is now. In fact, rules are deprecated as a whole.
我还想sp_bindrule指出，正如 Alex 的帖子所指出的那样，该功能已被弃用。我不确定它何时被弃用，但现在是。事实上，规则作为一个整体已被弃用。
Were I to want to create a type with a restriction, I'd consider using a user defined table type with a check constraint on the appropriate column(s). This also gives me a way of building a complex data type.
如果我想创建一个有限制的类型，我会考虑使用用户定义的表类型，并在适当的列上使用检查约束。这也为我提供了一种构建复杂数据类型的方法。
回答by Werner Falkmann
I can't really recommend the use of any sql-implementation specific features that make it harder when you are growing out of mssql and are migrating to another dbms. For our dwh dbs we started on mssql, migrated to oracle and have since last year graduated to hp vertica.
我真的不能推荐使用任何特定于 sql 实现的功能，这些功能会在您脱离 mssql 并迁移到另一个 dbms 时变得更加困难。对于我们的 dwh dbs，我们从 mssql 开始，迁移到 oracle，并从去年毕业到 hp vertica。
