locked
Creating Types as Objects RRS feed

  • Question

  • I am a newbie to SQL Server. i want to create custom data type for name. i know how to do that in oracle but when i use the same syntax gives error. Please help me with this.


    Oracle Syntax : - 

    CREATE OR REPLACE TYPE Name AS OBJECT
    (

    firstname VARCHAR2(80),
    lastname  VARCHAR2(80)

    );

    How can do this in SQL server. 

    Sunday, January 4, 2015 7:34 AM

Answers

  • So here is a key for your success in SQL Server: SQL Server is not Oracle. Oracle is not SQL Server.

    Solutions that work and commonly in Oracle are not always possible in SQL Server, or are very exotic. And vice versa.

    This is such an example. You can create a such a type in SQL Server, but you would have to create a CLR type, that is, you need to implement it in C#. CLR UDTs (UDT = User-Defined Type) is quite an exotic feature in SQL Server, which I rarely see in the wild. The exception is the spatial data types, geography and geometry, which ships with SQL Server.

    Note: Vaibhav referred you to the syntax for creating table types, but I think he misunderstood what you looking for. Or he knows Oracle better than I do, and I am the one who is doing the misunderstanding. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Charlie Liao Friday, January 9, 2015 9:01 AM
    Sunday, January 4, 2015 3:54 PM
  • User Defined Types in SQL Server is an exotic feature and unless you need a table type for the stored procedure parameter, you better not use this feature and use "primitive" types.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Charlie Liao Friday, January 9, 2015 9:01 AM
    Sunday, January 4, 2015 7:05 PM

All replies

  • CREATE TYPE Name AS TABLE
    ( 
    firstname VARCHAR(80),
    lastname  VARCHAR(80)
    );

    Note: It works only on SQL 2008 and above versions.

    For more details, Refer - CREATE TYPE (Transact - SQL)

    EDIT: I changed Varchar2 to Varchar  (Appropriate SQL Data type)


    -Vaibhav Chaudhari



    Sunday, January 4, 2015 7:58 AM
  • See an example here

    http://visakhm.blogspot.in/2010/02/aggregating-data-over-time-slots.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 4, 2015 9:20 AM
  • So here is a key for your success in SQL Server: SQL Server is not Oracle. Oracle is not SQL Server.

    Solutions that work and commonly in Oracle are not always possible in SQL Server, or are very exotic. And vice versa.

    This is such an example. You can create a such a type in SQL Server, but you would have to create a CLR type, that is, you need to implement it in C#. CLR UDTs (UDT = User-Defined Type) is quite an exotic feature in SQL Server, which I rarely see in the wild. The exception is the spatial data types, geography and geometry, which ships with SQL Server.

    Note: Vaibhav referred you to the syntax for creating table types, but I think he misunderstood what you looking for. Or he knows Oracle better than I do, and I am the one who is doing the misunderstanding. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Charlie Liao Friday, January 9, 2015 9:01 AM
    Sunday, January 4, 2015 3:54 PM
  • User Defined Types in SQL Server is an exotic feature and unless you need a table type for the stored procedure parameter, you better not use this feature and use "primitive" types.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Charlie Liao Friday, January 9, 2015 9:01 AM
    Sunday, January 4, 2015 7:05 PM