2

Is it possible to use user defined table type inside another user defined table type in SQL (nested user define table types).

CREATE TYPE A AS TABLE
(
    A_Id int
)

GO


CREATE TYPE B AS TABLE
(
    B_Id int,
    A_Id A --Need To Use A as data type in B
)
GO

I have senior to send the data table of rows with in a table.

Table
1.Item1
   1.Vendor1
   2.Vendor2
2.Item1
   1.Vendor1
   2.Vendor2

Please help how to send the table data within table rows from asp.net to sql. through looping or is there any simple way to send the nested table data to server.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Pradeep Kolli
  • 33
  • 1
  • 5
  • 2
    Have you tried something? Or just asking? – shadow May 26 '16 at 11:36
  • The concept of this breaks 1NF. You would be storing an entire table in a single tuple. That just defies the entire concept of relation data. And the performance of something like this would be unbelievably bad. Consider what would happen when you start nesting this with multiple levels. Ugh. – Sean Lange May 26 '16 at 13:29
  • You might want to read [my answer to a similar problem](http://stackoverflow.com/a/34832231/3094533). – Zohar Peled May 26 '16 at 13:31

1 Answers1

6

No. Why would it be? That's not how SQL Server (or any relational database, for that matter) work.

From TechNet's page on User-Defined Table Types:

Restrictions

User-defined table types have the following restrictions:

  • A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

"Nesting" in relational databases are achieved by using Foreign keys

You can't even create a foreign key constraint between two user defined table types.

What you can do is create two table types that one of them have a column to keep the id of the other, like this:

CREATE TYPE A AS TABLE
(
    A_Id int
)

GO


CREATE TYPE B AS TABLE
(
    B_Id int,
    A_Id int -- "FK"
)
GO
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 3
    I guess StackOverflow is easier than TechNet or MSDN. – shadow May 26 '16 at 11:48
  • Usually I wouldn't bother but I thought about what nesting means in this situations so I've tried creating a foreign key constraint between 2 table types and couldn't. I guess there is a reason behind it. – Zohar Peled May 26 '16 at 12:41
  • CREATE TYPE A AS TABLE ( A_Id int ) GO CREATE TYPE B AS TABLE ( B_Id int, A_Id A --Need To Use A as data type in B ) GO – Pradeep Kolli May 26 '16 at 13:05
  • 1
    @PradeepKolli That's just plain impossible. That's what I've explained in my answer. – Zohar Peled May 26 '16 at 13:15
  • Please help how to send the table data within table rows from asp.net to sql. through looping or is there any simple way to send the nested table data to server. – Pradeep Kolli May 26 '16 at 13:43
  • 1
    You might want to read [my answer to a similar problem](http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231). – Zohar Peled May 26 '16 at 13:57
  • 1
    FYI, "Why it would be?" - it is possible on PostgreSQL - actually converting a project from Postgres to SQL Server ... and this is annoying (and add the lack of native JSON support as column type...) – Shahar Hadas May 30 '20 at 02:00