Database
sequences are objects that create sequential values, kind of like some types of
IDENTITY values. However, sequences are not strictly tied to just a single
database table. The default data type for a sequence is BIGINT. Sequences have
the ability to cycle when they go past the maximum value. If you plan on that
happening, make sure to set the MINVALUE property of the sequence. You have the
ability to cache a set of sequence values if you will be using a lot of them in
a batch operation.
The MERGE
statement is a combination of INSERT and UPDATE statements rolled into one. It
is good for OLTP systems. With a MERGE, you are dealing with SOURCE and TARGET
tables. You define rules to follow in scenarios of data between these tables.
For example, if there is not a match in the target table for source values, you
will normally perform an INSERT. And if there is not a match in the source, you
will normally do an UPDATE. Both of these can be taken into account and
performed by the MERGE statement.
OUTPUT allows you to return information related
to rows affected by DML. The format is similar to a SELECT statement. You use
the INSERTED and DELETED keywords to specify which information you are after.
This is a good technique to use when an IDENTITY column value is being inserted
into a table and you want to know what value got created.
