Notes on SQL 3

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2

INSERT Statement

To cause DEFAULT constraints to "execute" on a table while inserting, it is possible to do this: INSERT INTO Table VALUES ('value 1', 'value 2', DEFAULT). Additionally, specifying all the columns in the table removes the need to explicitly state the column name for each value.

For batch insertion into an existing table: INSERT SELECT; for temporary/permanent table creation: SELECT INTO.

Advanced DML - OUTPUT Clause

Allows things like these (for INSERT, UPDATE and DELETE statements):

DELETE FROM [Table]
OUTPUT deleted.* INTO [output_table]
WHERE Condition

However, there are limitations: output_table cannot

• Have enabled triggers defined on it.


• Participate on either side of a foreign key constraint.


• Have CHECK constraints or enabled rules.

Advanced DML - MERGE Statement

The syntax should be clear enough as to what this does. I couldn't find much worth mentioning without going into too much detail:

[ WITH <common_table_expression> [,...n] ]
MERGE
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
        USING <table_source>
        ON <merge_search_condition>
        [ WHEN MATCHED [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
            THEN <merge_not_matched> ]
        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ <output_clause> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]

Transactions

When implicit transactions is set to ON (SET IMPLICIT_TRANSACTIONS {ON|OFF}), every one of the following statements starts a transaction: ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, UPDATE.

Note on Rollbacks: "Although a ROLLBACK statement returns the data to its prior state, some functionalities, such as seed values for identity columns, are not reset."

With nested transactions, a ROLLBACK goes all the way to the outermost transaction (even if COMMITS were done for any of the inner ones).

The SAVE TRANSACTION savepoint_name statement allows partial rollbacks, if a savepoint_name is specified during rollback. However, it cannot be used with distributed transactions (damn it, CDR!).

When transactions deadlock, SQL Server chooses a victim based on the estimated cost of rolling back each one, and a 1205 error is issued.

Locking

"Locks can be assigned to resources such as rows, pages, indexes, tables and databases".

Locking modes:

Shared (S): placed on resources for read (SELECT) operations. They are compatible among them, but not with exclusive locks. If the isolation level is REPEATABLE READ or higher, the lock is kept throughout the transaction instead of just during the read.

Update (U): "placed on resources where a shared (S) lock is required, but the need to upgrade to an exclusive (X) lock is anticipated." ONLY ONE TRANSACTION CAN OBTAIN A "U" LOCK ON A RESOURCE AT A GIVEN TIME. This is to prevent locking deadlocks when two or more transactions have a "S" lock on a resource, that they want to upgrade to an "X" lock. But since each one has to wait for the other to release the "S" lock first, deadlock occurs.

Exclusive (X): for data modification. Not compatible with any other kind of lock, and can only be "overridden" by a NOLOCK hint or the READ UNCOMMITED isolation level.

Intent (IS, IX, SIX): from TechNet:

"The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

• To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.


• To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity."

Schema (Sch-M, Sch-S): they stand for Schema Modification, and Schema Stability. Obvious meaning for the first (e.g. adding a new column to a table), while the second ones "are placed on objects while queries are being compiled or executed". Need more info on this one...

Bulk Update (BU): used for bulk insert.

Key-range: again from TechNet:

"Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction."

???

Transaction Isolation Levels

Set with the SET TRANSACTION ISOLATION LEVEL statement.

READ UNCOMITTED: allows reading what other transaction has inserted but not committed.

READ COMMITED: prevents dirty reads (the previous case). This is the default.

REPEATABLE READ: no dirty reads, and ensures that shared locks are maintained until the transaction completes.

SNAPSHOT: (requires the ALLOW_SNAPSHOT_ISOLATION option). Literally takes a snapshot of the data that will be read, and consequently does not issue any locks. It's still not clear to me if data modification is done only "locally" on the snapshot or how is it handled...

SERIALIZABLE: total blocking. No one can read what the transaction has modified, and no one can modify what the transaction has read.

Other notes

Tables without a clustered index are stored in a heap.

"Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts." Cool!

Click here for the next part in this series of posts.