tech@faiz
  SQL Server Cheat Sheet -3
 
SQL Server

SQL Server Cheat Sheet -3
 

 


LOGICAL PROCESSING ORDER OF SELECT

1. FROM table

2. ON join condition

3. JOIN table

4. WHERE clauses

5. GROUP BY columns

6. WITH CUBE / WITH ROLLUP

7. HAVING condition

8. SELECT columns

9. DISTINCT

10. ORDER BY columns

11. TOP % or number


CTES

; WITH cteName ( columnList )

AS ( SELECT statement )

SELECT columns

FROM cteName

INNER JOIN table ON condition


RECURSIVE CTES

; WITH cteName ( columnList )

AS ( -- Anchor statement:

SELECT columns FROM table

UNION ALL

-- Recursion statement:

SELECT columns FROM table

INNER JOIN cteName ON …

)

SELECT columns

FROM cteName


OVER AND PARTITION BY

/* Aggregate functions include COUNT, MIN, MAX, AVG, ROW_COUNT(), etc. */

SELECT

agg_func(col1) OVER(),

agg_func(col1)

OVER(PARTITION BY col2),

columns

FROM table


XML TRICK: LIST OF DETAILS

/* Table2 holds detail rows for Table1; e.g., order details to order headers. */

SELECT columns,

colname = STUFF(

( SELECT ','

+ Name

FROM Table2

WHERE Table1.ID = Table2.ID

ORDER BY Name

FOR XML PATH('')

), 1, 1, '')

FROM Table2


EXCEPT/INTERSECT

SELECT col1, col2 FROM Table1

EXCEPT

SELECT col3, col4 FROM Table2

SELECT col1, col2 FROM Table1

INTERSECT

SELECT col3, col4 FROM Table2


MERGE

DECLARE @Changes

TABLE(Change VARCHAR(20))

; MERGE INTO DestTable

USING

( SELECT from sourceTable

) AS Source ( columnList )

ON DestTable.ID = Source.ID

WHEN MATCHED THEN

Action on destination

/* E.g., UPDATE SET col1 = 1 */

WHEN NOT MATCHED BY TARGET|SOURCE

Action on destination

/* E.g., INSERT (col1) VALUES(1) */

OUTPUT $action INTO @Changes

 

 

 

 


 
  Today, there have been 2 visitors (12 hits) on this page!  
 
Free Domain This site was last updated Monday, 23 January 2017
Copyright © 2006-2017 smfaizhaider. All rights reserved.