Ch. 11 Sequence/Identity and Merge

Chapter 11 lessons one and two deal with sequence/identity and merge statements respectfully.

I've learned about these in the past, but it was great to review these ideas again learn a few new things about them.  For example, the indentity property has been around in SQL for sometime now, but in 2012 Sequence was introduced.  There are a few key differences between sequence and identity which I will get to in a minute, but first it's important to understand what these properties are used for.

Identity and Sequence both automatically assign a value to the column in a table once the row is inserted.  Only one column in a table can have an identity property.

As of now the most common way that I see the identity property used has been in practice exercises such as my example of finding the sum of all rows in the table back in my chapter 5 homework (I remember this because I just did the homework Tuesday :)).. In that example you first had to create a table, then insert values; here's how that was done:

USE TestDB
IF OBJECT_ID('sumquestion') IS NOT NULL DROP TABLE sumquestion;
CREATE TABLE sumquestion (
ID INT IDENTITY (1,1)
Name VARCHAR(10)
);
INSERT INTO sumquestion (Name)
VALUES ('Alpha'), ('Beta'), ('Beta'), ('Charlie'), ('Charlie'), ('Charlie');
SELECT * FROM sumquestion

Notice that when I inserted values into table sumquestion that I didn't have to insert for ID, just name, because the IDENTITY property on the ID column automatically assigns a value for the row.  Placing (1,1) next to INT tells the operator to start at number 1, and increase each value by 1 (this is the default).

Important items that are pointed out in this section are:

• There are differences when using delete and truncate and the identity property.  Using delete in the where clause does not affect the value of identity, however truncate does.  
• Identity does not guarantee the uniqueness of values.. if you try to insert a value into a table and the current seed of identity is 5, and the insert fails, this insert would have taken the value 6.. but because it failed, 6 will be skipped.  Try to make another insert and it will insert 7, your identity column will now show 4, 5, 7, 8, etc...
• The following functions help you with identity: SCOPE_IDENTITY returns the last identity value in the session in the current scope.  @@IDENTITY returns the last identity value generated in your session regardless of scope.  IDENT_CURRENT accepts a table as input and returns the last identity value generate in the input table regardless of session

As far as SEQUENCE goes, it's important to remember that sequence is actually an object in the DB, not just a function.  Here are the key differences between IDENTITY and SEQUENCE according to the book:

• Identity is tied to a particular column in your table so you can't remove the property and add it to something else
• Identity is table specific, sequence is not (because it's a file object)
• With identity you need to create a row before the value is assigned, this is not true with sequence
• You can't update the identity column
• Identity can't support cycling (meaning once it runs out of values it can't go back to the start)
• Truncate resets the identity value

The way that I remember the differences between the two is that first off SEQUENCE is a newer to SQL so so it comes with more customization.  For example with sequence you're able to tell it to start at a minimum value, how much to increment by, a max value, whether it's allowed to cycle or not cycle, and a starting value.  An example of the syntax of SEQUENCE is something like this:

CREATE SEQUENCE Sales.SequOrderIDs AS INT

   MINVALUE 1

   CYCLE;

--

So, as far as merge statement, it's just as it seems in the spoken word… it deals with moving data from one table to another based on certain clauses.  Here is how tech net describes merge which I believe is very concise and simple:

The MERGE syntax consists of five primary clauses:

• The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
• The USING clause specifies the data source being joined with the target.
• The ON clause specifies the join conditions that determine where the target and source match.
• The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
• The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

Credit: Querying Microsoft SQL 2012; Itzik Ben-Gan, Dejan Sarka, Ron Talmage; http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx