none
Nested tables

    Question

  • Dear friends,
    I am interested in nested tables. Does SQL Server.support this objects? Where I can find some simple exemple of nested tables? I suppose, this objects are more typical for Oracle.
    Thanks a lot,
    Anna
    Saturday, November 16, 2013 9:00 PM

Answers

  • but I don't see practical meaning of nested/derivated tables.

    I also don't see much value in nested tables.  Instead, I would use normalized database tables (e.g. 3NF) with a one-to-many relationship.  There is no need to embed the data of a one-to-many relationship within the table itself as that sort of design is inflexible.

    Using derived tables, common table expressions and other more advanced SQL querying techniques are powerful ways to return data in a flexible way for a variety of purposes.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, November 17, 2013 12:58 PM

All replies

  • Hi Anna,

    If you are talking of nested tables in TSQL then in SQL Server we call it as derived tables. Below are the details:

    Derived tables are result sets used as table sources in a query. You can add a derived table to a query in the Diagram Pane.

    To add a derived table to a query
    Open an existing query or create a new query.

    Right-click the Diagram Pane and choose Add New Derived Table.

    A new table with the name derived tbl_N is added, and the derived table's SELECT statement is added to the query's FROM clause.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Saturday, November 16, 2013 9:10 PM
  • I am interested in nested tables. Does SQL Server.support this objects? Where I can find some simple exemple of nested tables? I suppose, this objects are more typical for Oracle.

    A assume you mean the equivalent of nested tables in Oracle?  This is basically a column with a complex type and/or repeating value.  In SQL Server, you could implement it as XML like the example below or as a SQLCLR type.

    CREATE TABLE dbo.nested_table(
    	ID int NOT NULL
    		CONSTRAINT pk_nested_table PRIMARY KEY
    	,nested_data xml
    	);
    
    INSERT INTO dbo.nested_table VALUES (1, '<c>A</c>');
    INSERT INTO dbo.nested_table VALUES (2, '<c>B</c><c>C</c>');
    INSERT INTO dbo.nested_table VALUES (3, '<c>D</c><c>E</c><c>F</c>');
    
    SELECT ID, nested_data.nested_value.value('.', 'char(1)') AS nested_value
    FROM dbo.nested_table
    CROSS APPLY nested_data.nodes('/c') AS nested_data(nested_value);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 16, 2013 9:17 PM
  • Sorry, I din't make it.
    Icon "Show Diagram Pane" is inactive. Is there some detailed description of this way?
    Thanks for help,
    Anna
    Saturday, November 16, 2013 9:37 PM
  • Open SSMS. Open a new query pane. Right click on the Query Pane and click on Design Query in Editor option. The Query designer window appears. Right click on the Diagram Pane and select the Add New derived table option. Refer the below snapshot:

    Below is an example of Nested table (Derived Table)

    SELECT     name
    FROM         (SELECT     spid, DB_NAME(dbid) AS name
                           FROM          sys.sysprocesses
                           WHERE      (spid > 50)) AS derivedtbl_1


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005


    Saturday, November 16, 2013 10:15 PM
  • No. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, November 17, 2013 12:12 AM
  • Thanks friends,
    but I don't see practical meaning of nested/derivated tables.
    Anna
    Sunday, November 17, 2013 10:48 AM
  • but I don't see practical meaning of nested/derivated tables.

    I also don't see much value in nested tables.  Instead, I would use normalized database tables (e.g. 3NF) with a one-to-many relationship.  There is no need to embed the data of a one-to-many relationship within the table itself as that sort of design is inflexible.

    Using derived tables, common table expressions and other more advanced SQL querying techniques are powerful ways to return data in a flexible way for a variety of purposes.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, November 17, 2013 12:58 PM