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;
57 Cards in this Set
- Front
- Back
Inline function |
|
|
Derived table |
|
|
CTE |
|
|
Update table add discount |
|
|
Update tgt table with src table |
|
|
Update tgt table with CTE |
|
|
Update tgt table with derived table |
|
|
Merge |
|
|
Merge with CTE |
|
|
Merge with derived table |
|
|
Create Stored Proc |
|
|
Call stored procedure |
|
|
Insert with OUTPUT |
|
|
Delete with OUTPUT |
|
|
Update with OUTPUT |
|
|
Insert into |
|
|
Insert select |
|
|
Insert exec |
|
|
Select into |
|
|
UPDATE based on a join |
|
|
Delete statement |
|
|
Delete based on a Join |
|
|
DELETE with subquery |
|
|
Delete with CTE -- so u can use order by |
|
|
Insert with output |
|
|
Delete with output |
|
|
Update with output |
|
|
Branching Logic (5) used with stored procedures |
IF/ELSE WHILE WAITFOR GOTO RETURN |
|
Cast |
|
|
Data Manipulation Language (DML) |
Insert Update Delete (statements) |
|
Data Definition Language (DDL) |
Create Table Create index (statements) |
|
Read-only transactions |
Single select statement. They are not part of DML or DDL transactions, but are still considered transactions in sql server. |
|
ACID properties of transactions |
Atomicity Consistency Isolation Durability |
|
@@TRANCOUNT |
|
|
XACT_STATE () |
|
|
Autocommit mode |
The default transaction management mode. You do not issue any surrounding transactional commands, such as begin tran, rollback tran, or commit tran. |
|
Implicit transaction mode |
Does not automatically commit or roll back the statement. - - set implicit_transactions on; |
|
Explicit transaction mode |
You must explicitly issue the Begin Tran command to start a transaction |
|
Error handling methods |
@@ERROR is unstructured error handling XACT_ABORT is semi structured TRY/CATCH is structured error handling |
|
Window function |
|
|
Window Running Total |
|
|
Window ranking functions |
|
|
PIVOT table operator |
|
|
Creating Sequence |
|
|
Union |
|
|
Apply |
|
|
Window Offset Functions |
Lag Lead First_value Last_value |
|
Window Ranking Functions |
Row_Number Rank Dense_Rank Ntile |
|
Window Aggregate Functions |
Sum Count Avg Min Max |
|
Windows Framing |
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. |
|
@@TRANCOUNT and XACT_STATE () |
|
|
@@ERROR |
|
|
@@ERROR using variable |
|
|
Concatenation |
|
|
Predicate |
|
|
Problems with identity |
|
|
Benefits of sequence |
|