Sql

Front 1

Inline function

Back 1

Front 2

Derived table

Back 2

Front 3

CTE

Back 3

Front 4

Update table add discount

Back 4

Front 5

Update tgt table with src table

Back 5

Front 6

Update tgt table with CTE

Back 6

Front 7

Update tgt table with derived table

Back 7

Front 8

Merge

Back 8

Front 9

Merge with CTE

Back 9

Front 10

Merge with derived table

Back 10

Front 11

Create Stored Proc

Back 11

Front 12

Call stored procedure

Back 12

Front 13

Insert with OUTPUT

Back 13

Front 14

Delete with OUTPUT

Back 14

Front 15

Update with OUTPUT

Back 15

Front 16

Insert into

Back 16

Front 17

Insert select

Back 17

Front 18

Insert exec

Back 18

Front 19

Select into

Back 19

Front 20

UPDATE based on a join

Back 20

Front 21

Delete statement

Back 21

Front 22

Delete based on a Join

Back 22

Front 23

DELETE with subquery

Back 23

Front 24

Delete with CTE -- so u can use order by

Back 24

Front 25

Insert with output

Back 25

Front 26

Delete with output

Back 26

Front 27

Update with output

Back 27

Front 28

Branching Logic (5) used with stored procedures

Back 28

IF/ELSE

WHILE

WAITFOR

GOTO

RETURN

Front 29

Cast

Back 29

Front 30

Data Manipulation Language (DML)

Back 30

Insert

Update

Delete (statements)

Front 31

Data Definition Language (DDL)

Back 31

Create Table

Create index (statements)

Front 32

Read-only transactions

Back 32

Single select statement. They are not part of DML or DDL transactions, but are still considered transactions in sql server.

Front 33

ACID properties of transactions

Back 33

Atomicity

Consistency

Isolation

Durability

Front 34

@@TRANCOUNT

Back 34

Front 35

XACT_STATE ()

Back 35

Front 36

Autocommit mode

Back 36

The default transaction management mode. You do not issue any surrounding transactional commands, such as begin tran, rollback tran, or commit tran.

Front 37

Implicit transaction mode

Back 37

Does not automatically commit or roll back the statement.

- - set implicit_transactions on;

Front 38

Explicit transaction mode

Back 38

You must explicitly issue the Begin Tran command to start a transaction

Front 39

Error handling methods

Back 39

@@ERROR is unstructured error handling

XACT_ABORT is semi structured

TRY/CATCH is structured error handling

Front 40

Window function

Back 40

Front 41

Window Running Total

Back 41

Front 42

Window ranking functions

Back 42

Front 43

PIVOT table operator

Back 43

Front 44

Creating Sequence

Back 44

Front 45

Union

Back 45

Front 46

Apply

Back 46

Front 47

Window Offset Functions

Back 47

Lag

Lead

First_value

Last_value

Front 48

Window Ranking Functions

Back 48

Row_Number

Rank

Dense_Rank

Ntile

Front 49

Window Aggregate Functions

Back 49

Sum

Count

Avg

Min

Max

Front 50

Windows Framing

Back 50

A filtering option that allows you to restrict the result set within the partition using delimiters.

With the rows framing option, you can use "unbounded preceding and current row" to compute the running total values from the beginning of the partition to the end.

Front 51

@@TRANCOUNT and XACT_STATE ()

Back 51

Front 52

@@ERROR

Back 52

Front 53

@@ERROR using variable

Back 53

Front 54

Concatenation

Back 54

Front 55

Predicate

Back 55

Front 56

Problems with identity

Back 56

Front 57

Benefits of sequence

Back 57