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?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|||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|||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.|||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