Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    71

    Unanswered: is there a "user defined row type" ability in MSSQL server?

    Hi all. We have a mix of informix and mssql server and I want to know if something we do in informix has an analogous feature in MSSQL. We can define a "row type" in informix, like so:

    create row type name_1(fname char(20),lname char(20));

    The when we create any table that includes a first and last name, we do so using this row type like so:

    create table sometable(name name_1, some column,...etc)

    This allows us to set a standard for certain common fields and avoids having different developers build the same type of field in more than one way, different lengths, etc.

    Is there a similar function in MSSQL server?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No. SQL Server 2000 supports user defined functions, but not user defined data types.

    The best way that I've found to do this is to create a table with just an arbitrary PK (usually an identity column) and the columns needed to implement the new datatype. Then have your developers add an FK column wherever they would use the user defined datatype. It isn't quite as simple to code this way, but relationally it is much more sound so the two seem to balance each other.

    -PatP

  3. #3
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55
    I do not know about creating row types in SQL server 7, but you can surely create your own data type i.e., user defined datatypes, using sp_addtype command.
    for example
    EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'

    The datatype telephone has been created. You can use it in any table

    CREATE table customers
    (
    customer_name varchar(30),
    telephone_number telephone
    )

    Roshmi Choudhury

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    There's also a sql_variant data type, and a table variable. Not sure if there's something ugly to come up with using either one or worse: both.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The user defined datatypes created by sp_addtype are scalars (single columns). So are sql_variant columns (and they are really messy too). What WiccaChic seems to want is the ability to define multiple columns that are treated as one (like a Pascal record or a C struct).

    A table variable does exactly what they want, but you can't put the darned thing (as a whole anyway) into another table!

    The best answer that I've found for doing this so far is to create a stand alone table with the appropriate columns and a surrogate primary key, then make a foreign key reference whereever I want to use it in another table.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •