Monday, March 19, 2012

does MSSQL have the ability to do "named row types?"

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?sumGirl (emebohw@.netscape.net) writes:
> 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?

In SQL 2000, no.

In SQL 2005, which currently is in beta, you can define structured types
in CLR languages like Visual Basic or C#, but it's not really the same
thing as the Informafix feature. I assume that in the Informix case you
can still say:

SELECT fname FROM sometable WHERE ...

and thus refer to the individual columns directly. With a CLR type in
SQL2005 you would have to say:

SELECT name.fname FROM sometable WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

No comments:

Post a Comment