locked
How to create and use an user defined type in SQL Server 2005? RRS feed

  • Question

  • I want to use a user defined type in sql server.

    Please tell me steps for creating a user defined type and use in table.

    Tuesday, April 3, 2012 5:38 AM

Answers

  • http://msdn.microsoft.com/en-us/library/ms175007.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, April 3, 2012 7:34 AM
  • What is the purpose of the user defined type? Like Uri pointed you to, there is an alias type that takes a specific SQL Server type and makes something like varchar(20) look like twentyCharacterString, but that is pretty much it.

    There is also a way to build a UDT with the CLR that is far more powerful, but far more work and management.

    Once the UDT is created, it works just like any intrinist type with no modifiers for length, precision, etc.


    Louis

    Tuesday, April 3, 2012 2:14 PM
  • It's a big dffierence what you want to do:
    1. Create an Alias. This alias can be used in Create/Alter table expression, but in reality the column will be denoted as the "real" datatype. It's bascially a typedef for SQL.
    Example:
    When you have an alias named "Text" for "NVARCHAR(20)", and use "someTextColumn Text" when creatign a table, the column "someTextColumn" will be of type "NVARCAHR(20)" - not "Text".

    2. Create a middle thing between Alias and a real type. You give a fitting name for the Alias write a "ConvertTo" and "ConvertFrom" UDF. Maybe add some Rules regarding the Values. So you can store something shown/entered as this: "555 12 38 5-123" into a 8 bit money instead of a VARCHAR(22) - wich cuts the memory needed to almost 2/3.
    Example:
    http://www.codeproject.com/Articles/20434/Telephone-Numbers-in-SQL-Server-2005-Part-1-The-Da
    http://www.codeproject.com/Articles/20596/Telephone-Numbers-in-SQL-Server-2005-Part-2-Format
    The example isn't ideal (I would split the number into it's 4 parts and store each as Int type), but the techniques/ideas are very good.

    3. Write a class for your datatype in any .NET language (C#, C++, Visual Basic, ...) and produce a CLR Assembly from that. When you create a Class Called "MyTelephoneNumberType" you can use "MyTelephoneNumberType" in a create and the cooumns wil actually be of that type.
    It's like the previous approach - it's faster (because you can do the Conversions in a Real Langauge not SQL), but also more work on your side (you need to write a class for it).
    How to:
    http://msdn.microsoft.com/en-us/library/ms131120.aspx
    Example:
    My guess is that any pre-defined type in SQL is built that way.

    Wednesday, April 4, 2012 12:35 PM

All replies

  • http://msdn.microsoft.com/en-us/library/ms175007.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, April 3, 2012 7:34 AM
  • What is the purpose of the user defined type? Like Uri pointed you to, there is an alias type that takes a specific SQL Server type and makes something like varchar(20) look like twentyCharacterString, but that is pretty much it.

    There is also a way to build a UDT with the CLR that is far more powerful, but far more work and management.

    Once the UDT is created, it works just like any intrinist type with no modifiers for length, precision, etc.


    Louis

    Tuesday, April 3, 2012 2:14 PM
  • What is the purpose of the user defined type? Like Uri pointed you to, there is an alias type that takes a specific SQL Server type and makes something like varchar(20) look like twentyCharacterString, but that is pretty much it.

    Yes. When you actually want to encode data - like writing a phone number in a money variable* - you would have to write UDF's to convert forward and backward.

    *nothing I would ever do, but it has some intersting thoughts on the thematic.

    Wednesday, April 4, 2012 8:55 AM
  • I am not sure that understood you

    SELECT   
    CONVERT(VARCHAR,CAST('4443563025'  AS MONEY),1)AS phone


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, April 4, 2012 8:59 AM
  • It's a big dffierence what you want to do:
    1. Create an Alias. This alias can be used in Create/Alter table expression, but in reality the column will be denoted as the "real" datatype. It's bascially a typedef for SQL.
    Example:
    When you have an alias named "Text" for "NVARCHAR(20)", and use "someTextColumn Text" when creatign a table, the column "someTextColumn" will be of type "NVARCAHR(20)" - not "Text".

    2. Create a middle thing between Alias and a real type. You give a fitting name for the Alias write a "ConvertTo" and "ConvertFrom" UDF. Maybe add some Rules regarding the Values. So you can store something shown/entered as this: "555 12 38 5-123" into a 8 bit money instead of a VARCHAR(22) - wich cuts the memory needed to almost 2/3.
    Example:
    http://www.codeproject.com/Articles/20434/Telephone-Numbers-in-SQL-Server-2005-Part-1-The-Da
    http://www.codeproject.com/Articles/20596/Telephone-Numbers-in-SQL-Server-2005-Part-2-Format
    The example isn't ideal (I would split the number into it's 4 parts and store each as Int type), but the techniques/ideas are very good.

    3. Write a class for your datatype in any .NET language (C#, C++, Visual Basic, ...) and produce a CLR Assembly from that. When you create a Class Called "MyTelephoneNumberType" you can use "MyTelephoneNumberType" in a create and the cooumns wil actually be of that type.
    It's like the previous approach - it's faster (because you can do the Conversions in a Real Langauge not SQL), but also more work on your side (you need to write a class for it).
    How to:
    http://msdn.microsoft.com/en-us/library/ms131120.aspx
    Example:
    My guess is that any pre-defined type in SQL is built that way.

    Wednesday, April 4, 2012 12:35 PM