June 5, 2015 at 12:29 am
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.
June 5, 2015 at 3:27 am
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
June 5, 2015 at 3:31 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply