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?
使用UDTs有什么优点和缺点?
4 个解决方案
#1
16
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的基本类型?
#2
13
I do not use code-based UDTs because I don't think that the extra complexity warrants the advantages. I do use 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!)
我不使用基于代码的udf，因为我不认为额外的复杂性会带来优势。我确实使用T-SQL UDTs，因为很少有额外的复杂性，所以优势是值得付出努力的。(感谢Marc_s指出我原来的帖子是不完整的!)
Regarding Code-based UDTs
关于基于代码之上
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 overall performance suffers due to having the CLR on versus off.
部署更加困难，因为必须向DB部署中添加程序集，并在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，认为它只有udf而没有udf在外部定义的dll中(但是不要引用我……)我真的不太熟悉它!
Regarding T-SQL UDTs
关于t - sql之上
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 would in 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 UDTs可以单独在SQL中定义(转到SQL Server Management Studio中的“可编程|类型|用户定义的数据类型”)。对于标准的UDTs，我实际上建议您掌握它们。它们非常简单，可以使DDL更具有自文档化性，并且可以加强完整性约束。例如，我定义了一个“GenderType”(char(1)，而不是nullable，持有“M”或“F”)，确保在性别字段中只允许适当的数据。
UDTs are pretty easy overall but this article gives 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.
当我最初回答这个问题时，我对复杂的、代码定义的类型(从手掌到前额)的概念很感兴趣。所以…谢谢你马克。
#3
6
The pro of user defined types is addressed quite well by Alex Papadimoulis. The cons have been well stated here.
Alex Papadimoulis对用户定义类型的支持做了很好的阐述。这里的缺点已经说得很清楚了。
I would also like to point out that the sp_bindrule function 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.
如果我想要创建一个具有限制的类型，我将考虑使用一个用户定义的表类型，并在相应的列上使用检查约束。这也为我提供了一种构建复杂数据类型的方法。
#4
0
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。
#1
16
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的基本类型?
#2
13
I do not use code-based UDTs because I don't think that the extra complexity warrants the advantages. I do use 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!)
我不使用基于代码的udf，因为我不认为额外的复杂性会带来优势。我确实使用T-SQL UDTs，因为很少有额外的复杂性，所以优势是值得付出努力的。(感谢Marc_s指出我原来的帖子是不完整的!)
Regarding Code-based UDTs
关于基于代码之上
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 overall performance suffers due to having the CLR on versus off.
部署更加困难，因为必须向DB部署中添加程序集，并在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，认为它只有udf而没有udf在外部定义的dll中(但是不要引用我……)我真的不太熟悉它!
Regarding T-SQL UDTs
关于t - sql之上
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 would in 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 UDTs可以单独在SQL中定义(转到SQL Server Management Studio中的“可编程|类型|用户定义的数据类型”)。对于标准的UDTs，我实际上建议您掌握它们。它们非常简单，可以使DDL更具有自文档化性，并且可以加强完整性约束。例如，我定义了一个“GenderType”(char(1)，而不是nullable，持有“M”或“F”)，确保在性别字段中只允许适当的数据。
UDTs are pretty easy overall but this article gives 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.
当我最初回答这个问题时，我对复杂的、代码定义的类型(从手掌到前额)的概念很感兴趣。所以…谢谢你马克。
#3
6
The pro of user defined types is addressed quite well by Alex Papadimoulis. The cons have been well stated here.
Alex Papadimoulis对用户定义类型的支持做了很好的阐述。这里的缺点已经说得很清楚了。
I would also like to point out that the sp_bindrule function 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.
如果我想要创建一个具有限制的类型，我将考虑使用一个用户定义的表类型，并在相应的列上使用检查约束。这也为我提供了一种构建复杂数据类型的方法。
#4
0
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。