Scribd Logo
Upload

Welcome to Scribd!

  • 90 views

    Forgotten T-SQL Cheat Sheet: L P O Select XML T: L D

    Uploaded by

    Jivan Goyal
    1. This document provides a cheat sheet of logical processing order and syntax examples for various T-SQL constructs such as SELECT statements, CTEs, recursive CTEs, EXCEPT/INTERSECT, MERGE statements, and XML tricks. 2. Syntax examples are given for logical processing order in a SELECT statement, common table expressions (CTEs), recursive CTEs, SET operations like EXCEPT and INTERSECT, MERGE statements, and using XML to concatenate multiple rows into a comma separated list. 3. The document is intended as a quick reference for SQL Server developers and includes attribution to the original author and license.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Forgotten T-SQL Cheat Sheet: L P O Select XML T: L D

    Uploaded by

    Jivan Goyal
    90 views1 page
    1. This document provides a cheat sheet of logical processing order and syntax examples for various T-SQL constructs such as SELECT statements, CTEs, recursive CTEs, EXCEPT/INTERSECT, MERGE statements, and XML tricks. 2. Syntax examples are given for logical processing order in a SELECT statement, common table expressions (CTEs), recursive CTEs, SET operations like EXCEPT and INTERSECT, MERGE statements, and using XML to concatenate multiple rows into a comma separated list. 3. The document is intended as a quick reference for SQL Server developers and includes attribution to the original author and license.

    Original Title

    ForgottenTSQLCheatSheet[1]

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    1. This document provides a cheat sheet of logical processing order and syntax examples for various T-SQL constructs such as SELECT statements, CTEs, recursive CTEs, EXCEPT/INTERSECT, MERGE statements, and XML tricks. 2. Syntax examples are given for logical processing order in a SELECT statement, common table expressions (CTEs), recursive CTEs, SET operations like EXCEPT and INTERSECT, MERGE statements, and using XML to concatenate multiple rows into a comma separated list. 3. The document is intended as a quick reference for SQL Server developers and includes attribution to the original author and license.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    90 views1 page

    Forgotten T-SQL Cheat Sheet: L P O Select XML T: L D

    Uploaded by

    Jivan Goyal
    1. This document provides a cheat sheet of logical processing order and syntax examples for various T-SQL constructs such as SELECT statements, CTEs, recursive CTEs, EXCEPT/INTERSECT, MERGE statements, and XML tricks. 2. Syntax examples are given for logical processing order in a SELECT statement, common table expressions (CTEs), recursive CTEs, SET operations like EXCEPT and INTERSECT, MERGE statements, and using XML to concatenate multiple rows into a comma separated list. 3. The document is intended as a quick reference for SQL Server developers and includes attribution to the original author and license.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 1

    Forgotten T-SQL Cheat Sheet

    LOGICAL PROCESSING ORDER OF SELECT XML TRICK: LIST OF D ETAILS


    1. FROM table /* Table2 holds detail
    2. ON join condition rows for Table1; e.g., order details to
    3. JOIN table order headers. */
    4. WHERE clauses
    5. GROUP BY columns SELECT columns,
    6. WITH CUBE / WITH ROLLUP colname = STUFF(
    7. HAVING condition ( SELECT ','
    8. SELECT columns + Name
    9. DISTINCT FROM Table2
    10.ORDER BY columns WHERE Table1.ID = Table2.ID
    11.TOP % or number ORDER BY Name
    FOR XML PATH('')
    ), 1, 1, '')
    CTES FROM Table2
    ; WITH cteName ( columnList )
    AS ( SELECT statement ) EXCEPT/INTERSECT
    SELECT columns
    FROM cteName SELECT col1, col2 FROM Table1
    INNER JOIN table ON condition EXCEPT
    SELECT col3, col4 FROM Table2
    RECURSIVE CTES SELECT col1, col2 FROM Table1
    ; WITH cteName ( columnList ) INTERSECT
    AS ( -- Anchor statement: SELECT col3, col4 FROM Table2
    SELECT columns FROM table…
    UNION ALL MERGE
    -- Recursion statement:
    SELECT columns FROM table… DECLARE @Changes
    INNER JOIN cteName ON … TABLE(Change VARCHAR(20))
    )
    SELECT columns ; MERGE INTO DestTable
    FROM cteName USING
    ( SELECT from sourceTable
    ) AS Source ( columnList )
    OVER AND PARTITION BY ON DestTable.ID = Source.ID
    /* Aggregate functions include COUNT,
    MIN, MAX, AVG, ROW_COUNT(), etc. */ WHEN MATCHED THEN
    Action on destination
    SELECT /* E.g., UPDATE SET col1 = 1 */
    agg_func(col1) OVER(),
    agg_func(col1) WHEN NOT MATCHED BY TARGET|SOURCE
    OVER(PARTITION BY col2), Action on destination
    columns /* E.g., INSERT (col1) VALUES(1) */
    FROM table…
    OUTPUT $action INTO @Changes

    SELECT * FROM @Changes


    http://www.MidnightDBA.com/Jen
    Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.

    You might also like