Create SQL User Defined Types in Visual Studio 2012

Question

How to create a user defined type in SQL Server using VS2012? I tried to do the step shown in the image but no thing appears.

Also I have tried to create Type using the following code but I can not create elements of that type in my table.

CREATE TYPE CartListType AS TABLE 
(
   [ItemNo]   INT            NOT NULL,
   [NameEn]   NVARCHAR (150) NULL,
   [Price]    FLOAT (53)     NULL,
   [Quantity] INT            NULL,
   [ImageSrc] VARCHAR (100)  NULL
)

The code you've shown works fine for me. So not sure what your issue is. "I can not create elements of that type" - can you show that attempt? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 20, 2014 at 9:48

Damien_The_Unbeliever · Accepted Answer · 2014-02-20 10:17:42Z

1

There's no concept of nested tables in SQL Server. So whilst the code you've shown is correct for declaring a user-defined table (UDT) type, what you cannot do is then use that type as the declared type of a column in another table. All you can use UDTs for are for variable and parameter declarations¹.

The more relational thing to do would be to just create this as a normal table with an additional column to act as a foreign key back to the Bills table. An alternative, if you really do want to store the data "in-row" (although it'll actually be stored in separate pages physically) would be to declare the column as XML, and use the XML facilities instead to store and manipulate this data.

¹ I've said something with certainty, so someone's bound to pop up and mention some third use case I've forgotten, but the general point stands.

answered Feb 20, 2014 at 10:17

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Using that type as a column wouldn't be a "nested table" - at least not in the sense as it is used in Oracle. It would be a column based on a "struct", i.e. a single value with "sub-values". A nested table (as used in Oracle) would be able to store multiple rows of that UDT. But I agree that this is better done through a 1:N relationship

Damien_The_Unbeliever Over a year ago

@a_horse_with_no_name - it seemed to resemble Oracle's nested tables to me. And we don't know whether the OP was intending to store 0, 1 or multiple rows in this column, for each outer row, if they'd have succeeded in creating this table

user330315 Over a year ago

you are right, after a closer look at the UDT, Hamed most probably wanted to create a "nested table".

Collectives™ on Stack Overflow

Create SQL User Defined Types in Visual Studio 2012

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related