Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
32 Cards in this Set
- Front
- Back
Query Hint Syntax |
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>); |
|
Table Hint Syntax |
SELECT <select list> FROM <table list> WITH <table hint list>[...] |
|
Join Hint Syntax |
SELECT <select list> FROM table1 [LEFT|RIGHT][OUTER|INNER] JOIN table2 [ON ] [...] |
|
MERGE Syntax |
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} ] |
|
UPDATE Syntax |
UPDATE table SET column = value [FROM sourcetable] [WHERE <predicate>] |
|
PIVOT Syntax |
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; |
|
CREATE TRIGGER Syntax |
CREATE TRIGGER triggername ON {table|view} [WITH ENCRYPTION, EXECUTE AS ] {FOR | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} AS <sql to execute> |
|
CREATE PROCEDURE Syntax |
CREATE PROC procname [@param1 datatype [=DEFAULT value] [OUT] [,...] ] [WITH ENCRYPTION, RECOMPILE, EXECUTE AS ] AS <sql to execute> |
|
CREATE VIEW Syntax |
CREATE VIEW viewname [WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA] AS <select statement> [WITH CHECK OPTION] |
|
CREATE INDEX Syntax |
CREATE [UNIQUE] [NON|CLUSTERED] INDEX indexname ON objectname (column [ASC|DESC] [,...]) [INCLUDE <column list>] |
|
ALTER INDEX Syntax
|
ALTER INDEX indexname ON objectname [REBUILD | DISABLE | REORGANIZE] |
|
CREATE TABLE Syntax |
CREATE TABLE tablename (columnname datatype [CONSTRAINT, DEFAULT, IDENTITY, NOT|NULL, etc] [,...]) |
|
CREATE FUNCTION Syntax |
CREATE FUNCTION fnname [(@param [AS...] [,...])] RETURNS [@variable | TABLE | datatype] [WITH ENCRYPTION | SCHEMABINDING | EXECUTE AS user] AS [BEGIN]
RETURN [<select statement>] [END] |
|
Window Function Syntax |
* ONLY in SELECT or ORDER BY clauses AGGFn(column) OVER (PARTITION BY [ORDER BY ] [ROWS|RANGE clause] ) |
|
OFFSET-FETCH Syntax |
... ORDER BY OFFSET n ROWS FETCH {FIRST|NEXT} n ROWS |
|
CREATE SEQUENCE Syntax |
CREATE SEQUENCE seqname AS int [START WITH n] [INCREMENT BY n] [MINVALUE n] [MAXVALUE n] [NO|CYCLE] [NO|CACHE] |
|
@@TRANCOUNT Value Meanings |
Output: integer Gives the transaction level, or number of number of nested transaction deep where 1 is outermost transaction. Max is 32. |
|
XACT_STATE() Value Meanings |
0 = no open transactions 1 = commitable open transaction -1 = uncommitable open transaction |
|
TRY/CATCH Blocks |
BEGIN TRAN BEGIN TRY <sql to try>
[COMMIT] END TRY BEGIN CATCH
[ROLLBACK] [THROW] END CATCH |
|
[TRY_]CAST Syntax |
[TRY_]CAST (espression AS datatype) |
|
[TRY_]CONVERT Syntax |
[TRY_]CONVERT ( datatype, expression [, style]) |
|
[TRY_]PARSE Syntax |
[TRY_]PARSE (string AS datatype [USING culture]) |
|
FORMAT Syntax |
FORMAT (value, format [,culture]) |
|
Set Operator Syntax |
[SELECT <select list> FROM ( ] <select statement for set 1> [UNION|INTERSECT|EXCEPT|UNION ALL] <select statement for set 2> [ ) AS alias] [ORDER BY...] |
|
GROUPING SETS, CUBE, ROLLUP Syntax |
SELECT... GROUP BY {GROUPING SETS <grouping set lists>|CUBE|ROLLUP} |
|
Common Table Expression Syntax |
WITH cte AS ( <select statement> ) SELECT FROM cte [...] |
|
ADD CONSTRAINT Syntax (ALTER) |
ALTER TABLE tablename WITH CHECK ADD CONSTRAINT constraintname ... {FOREIGN KEY(column) REFERENCES... |DEFAULT('value') FOR (column) |etc.} |
|
TRUNCATE Syntax |
TRUNCATE TABLE tablename |
|
DECLARE Syntax |
DECLARE @variable AS datatype [=value] [,...] |
|
DECLARE CURSOR Syntax |
DECLARE cursorname CURSOR FOR <select statement> |
|
RAISERROR Syntax |
RAISERROR (message, severity, state) [WITH NOWAIT] |
|
THROW Syntax |
THROW [errornum, message, state] *always severity = 16 all parameters are variables or none no parameters will throw original error message |