Querying Microsoft Sql Server 2012 70-461

Front 1

Query Hint Syntax

Back 1

SELECT <select list>

[FROM <table list> [JOIN ON] <predicate>]

[WHERE <predicate>]

[GROUP BY <group by list>]

[ORDER BY <order by list>]

OPTION (<query hint list>);

Front 2

Table Hint Syntax

Back 2

SELECT <select list>

FROM <table list>

WITH <table hint list>

[...]

Front 3

Join Hint Syntax

Back 3

SELECT <select list>

FROM table1

[LEFT|RIGHT][OUTER|INNER] JOIN table2 [ON ]

[...]

Front 4

MERGE Syntax

Back 4

MERGE <target table>

USING <source table>

ON <marge predicate>

[WHEN MATCHED [AND <predicate>]

THEN {UPDATE SET...|DELETE} ]

[WHEN NOT MATCHED [AND <predicate>]

THEN INSERT... ]

[WHEN NOT MATCHED BY SOURCE [AND <predicate>]

THEN {UPDATE SET...|DELETE} ]

Front 5

UPDATE Syntax

Back 5

UPDATE table

SET column = value

[FROM sourcetable]

[WHERE <predicate>]

Front 6

PIVOT Syntax

Back 6

WITH pivotdata AS

(

SELECT

grouping column

,spreading column

,aggregate column

FROM sourcetable

)

SELECT groupingcolumn, <distinct spreading values list>

FROM pivotdata

PIVOT (AGGFn(aggregatecolumn)

FOR spreadingcolumn IN (<distinct sreading values list>))

AS alias;

Front 7

CREATE TRIGGER Syntax

Back 7

CREATE TRIGGER triggername

ON {table|view}

[WITH ENCRYPTION, EXECUTE AS ]

{FOR | AFTER | INSTEAD OF}

{INSERT | UPDATE | DELETE}

AS

<sql to execute>

Front 8

CREATE PROCEDURE Syntax

Back 8

CREATE PROC procname

[@param1 datatype [=DEFAULT value] [OUT]

[,...] ]

[WITH ENCRYPTION, RECOMPILE, EXECUTE AS ]

AS

<sql to execute>

Front 9

CREATE VIEW Syntax

Back 9

CREATE VIEW viewname

[WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA]

AS

<select statement>

[WITH CHECK OPTION]

Front 10

CREATE INDEX Syntax

Back 10

CREATE [UNIQUE] [NON|CLUSTERED] INDEX indexname

ON objectname (column [ASC|DESC] [,...])

[INCLUDE <column list>]

Front 11

ALTER INDEX Syntax

Back 11

ALTER INDEX indexname

ON objectname

[REBUILD | DISABLE | REORGANIZE]

Front 12

CREATE TABLE Syntax

Back 12

CREATE TABLE tablename

(columnname datatype [CONSTRAINT, DEFAULT, IDENTITY, NOT|NULL, etc]

[,...])

Front 13

CREATE FUNCTION Syntax

Back 13

CREATE FUNCTION fnname

[(@param [AS...] [,...])]

RETURNS [@variable | TABLE | datatype]

[WITH ENCRYPTION | SCHEMABINDING | EXECUTE AS user]

AS

[BEGIN]

RETURN [<select statement>]

[END]

Front 14

Window Function Syntax

Back 14

* ONLY in SELECT or ORDER BY clauses

AGGFn(column) OVER (PARTITION BY [ORDER BY ] [ROWS|RANGE clause] )

Front 15

OFFSET-FETCH Syntax

Back 15

...

ORDER BY OFFSET n ROWS FETCH {FIRST|NEXT} n ROWS

Front 16

CREATE SEQUENCE Syntax

Back 16

CREATE SEQUENCE seqname

AS int

[START WITH n]

[INCREMENT BY n]

[MINVALUE n]

[MAXVALUE n]

[NO|CYCLE]

[NO|CACHE]

Front 17

@@TRANCOUNT Value Meanings

Back 17

Output: integer

Gives the transaction level, or number of number of nested transaction deep where 1 is outermost transaction.

Max is 32.

Front 18

XACT_STATE() Value Meanings

Back 18

0 = no open transactions

1 = commitable open transaction

-1 = uncommitable open transaction

Front 19

TRY/CATCH Blocks

Back 19

BEGIN TRAN

BEGIN TRY

<sql to try>

[COMMIT]

END TRY

BEGIN CATCH

[ROLLBACK]

[THROW]

END CATCH

Front 20

[TRY_]CAST Syntax

Back 20

[TRY_]CAST (espression AS datatype)

Front 21

[TRY_]CONVERT Syntax

Back 21

[TRY_]CONVERT ( datatype, expression [, style])

Front 22

[TRY_]PARSE Syntax

Back 22

[TRY_]PARSE (string AS datatype [USING culture])

Front 23

FORMAT Syntax

Back 23

FORMAT (value, format [,culture])

Front 24

Set Operator Syntax

Back 24

[SELECT <select list> FROM ( ]

<select statement for set 1>

[UNION|INTERSECT|EXCEPT|UNION ALL]

<select statement for set 2>

[ ) AS alias]

[ORDER BY...]

Front 25

GROUPING SETS, CUBE, ROLLUP Syntax

Back 25

SELECT...

GROUP BY {GROUPING SETS <grouping set lists>|CUBE|ROLLUP}

Front 26

Common Table Expression Syntax

Back 26

WITH cte AS

(

<select statement>

)

SELECT

FROM cte [...]

Front 27

ADD CONSTRAINT Syntax (ALTER)

Back 27

ALTER TABLE tablename WITH CHECK

ADD CONSTRAINT constraintname ...

{FOREIGN KEY(column) REFERENCES...

|DEFAULT('value') FOR (column)

|etc.}

Front 28

TRUNCATE Syntax

Back 28

TRUNCATE TABLE tablename

Front 29

DECLARE Syntax

Back 29

DECLARE @variable AS datatype [=value]

[,...]

Front 30

DECLARE CURSOR Syntax

Back 30

DECLARE cursorname CURSOR

FOR

<select statement>

Front 31

RAISERROR Syntax

Back 31

RAISERROR (message, severity, state)

[WITH NOWAIT]

Front 32

THROW Syntax

Back 32

THROW [errornum, message, state]

*always severity = 16

all parameters are variables or none

no parameters will throw original error message