SQL - 09 Modifying Data

SELECT... INTO

Creates a new table from the results of a query

Explain how to generate identifiers using sequences.

Objects that generate sequential numbers; they exist independently of tables, so offer greater flexibility than Identity.

What data does an UPDATE statement modify?

Only columns specified in the SET clause.

What happens when you use DELETE with a WHERE clause?

Specific rows are deleted.

What happens when you use DELETE without a WHERE clause?

All rows are deleted.

When is the only time that TRUNCATE TABLE can be rolled back?

If issued within a transaction.

When must you explicitly list a column name in an INSERT statement?

If it 1) not an identity column, 2) doesn't accept NULLs. or 3) has a default.

INSERT...VALUES

Inserts explicit values.

How does the MERGE statement work?

It modifies data based on a condition, such as 1) when the source matches the target, 2) when the source has no match in the target, and/or 3) when the target has no match in the source.

How does the TRUNCATE TABLE statement work?

It quickly and efficiently clears the entire table, physically deallocating storage rather than individually removing rows.

How does the UPDATE statement work?

It updates all rows in a table or view. The set of rows can be filtered with a WHERE clause and defined with a FROM clause.

Explain how to generate identifiers using identity columns.

The IDENTITY property of a column generates sequential numbers automatically for insertion into a table. You can specify optional seed and incremental values.

Define SCOPE_IDENTITY().

The last identity generated in the current scope.

Define @@IDENTITY.

The last identity generated in the current session.

Define IDENT_CURRENT('table_name>')

The last identity inserted into a table (still across all sessions).