Nested tables and user defined data types in sql server

• narengc

  Old Hand

  Points: 325

  More actions

  June 5, 2015 at 12:29 am

  #297447

  I have a requirement of creating nested tables in SQL server. Can any one guide me how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.

  Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.

  Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address

  Address (Nested table): with columns : Street, Town, Dist, State

  This was done in oracle using Nested tables and user defined data types. Can any one suggest me what is alternative for this in SQL server. How can I achive this requirement in SQL server.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 5, 2015 at 3:27 am

  #1803254

  As you have found out, nested tables are not supported in SQL Server.

  You can use an XML column with a schema (typed XML).

  Read more about it here: https://www.simple-talk.com/sql/learn-sql-server/introduction-to-xml-schema/

  BTW, nested tables are usually a bad idea and often are a strong indicator of a design mistake.

  -- Gianluca Sartori

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 5, 2015 at 3:31 am

  #1803255

  Make it a separate table and join them in your queries.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass