Chapter 11 Other Data Modification Aspects

Question 1

What is the IDENTITY property?

Answer 1

IDENTITY is a property of a column in a table. The property automatically assigns a value to the column upon insertion. You can define it for columns with any numeric type that has a scale of 0. This means all integer types, but also NUMERIC/DECIMAL with a scale of 0. When defining the property, you can optionally specify a seed and an increment. If you don’t, the defaults are 1 and 1. Only one column in a table can have an IDENTITY property.

Note that when you insert rows into the table, you don’t specify a value for the IDENTITY column because it gets values automatically.

Question 2

What do you do when you want to specify your own values into an IDENTITY column?

Answer 2

You need to set a session option called SET IDENTITY_INSERT to ON. Note that there’s no option that you can set to update an IDENTITY column.

Question 3

What are the functions that you can use to query the last identity value generated?

Answer 3

(1) SCOPE_IDENTITY, (2) @@IDENTITY, (3) IDENT_CURRENT

Question 4

What is the SCOPE_IDENTITY function?

Answer 4

SCOPE_IDENTITY returns the last identity value generated in your session in the current scope.

Question 5

What is the @@IDENTITY function?

Answer 5

@@IDENTITY returns the last identity value generated in your session regardless of scope.

Question 6

What is the IDENT_CURRENT function?

Answer 6

IDENT_CURRENT accepts a table as input and returns the last identity value generated in the input table regardless of session.

Question 7

What is the difference between SCOPE_IDENTITY and @@IDENTITY?

Answer 7

Suppose you have a stored procedure P1 that performs the following:

(1) An INSERT that generates a new identity value.
(2) A call to a stored procedure P2 that also has an INSERT statement that generates a new identity value.
(3) A query to the functions SCOPE_IDENTITY and @@IDENTITY.

SCOPE_IDENTITY will return the value generated by P1 which is the same scope. And @@IDENTITY will return the value generated by P2 irrespective of scope.

Question 8

Which statement DELETE or TRUNCATE affects the IDENTITY value?

Answer 8

DELETE doesn’t affect the IDENTITY value whereas TRUNCATE resets it to the initial seed.

Question 9

How do you reseed the current identity value?

Answer 9

Use the DBCC CHECKIDENT command:

DBCC CHECKIDENT(‘Sales.MyOrders’, RESEED, 4);

Question 10

Does the IDENTITY property guarantee uniqueness?

Answer 10

No. The IDENTITY property does not guarantee uniqueness. Remember that you can enter explicit values if you turn on IDENTITY_INSERT option. Also you can reseed the property value. To guarantee uniqueness, you need to use a constraint like a PK or UNIQUE constraint.

Question 11

Does the IDENTITY property guarantee that there will be no gaps between the values?

Answer 11

No. The IDENTITY property does not guarantee that there will be no gaps between the values. If an INSERT statement fails, the current identity value is not changed back to the original one so the unused value is lost. The next insertion will have a value after the one that wasn’t used.

Question 12

Does the IDENTITY property have cycling support?

Answer 12

No. The IDENTITY property does not have cycling support. This means that after you reach the maximum value in the type, the next insertion will fail due to an overflow error. To get around this, you need to reseed the current identity value before such an attempt is made.

Question 13

When was the sequence object introduced?

Answer 13

SQL Server 2012

Question 14

What are some of the benefits of using the sequence object?

Answer 14

(1) A sequence is not tied to a particular column in a particular table. However, you can use a DEFAULT constraint to assign a new value, (2) Because the sequence is an independent object in the database, you can use the same sequence to generate keys that are used in different tables. This way the keys won’t conflict across tables, (3) You can generate a sequence value before using it by storing the result of the NEXT VALUE FOR function in a variable, (4) You can update columns with the result of the NEXT VALUE FOR function, (5) A sequence supports cycling, (6) A TRUNCATE statement doesn’t reset the current value of a sequence object because the sequence is independent of the tables that use it.

Question 15

How do you create a sequence?

Answer 15

You can use the CREATE SEQUENCE command to create a sequence. At a minimum, you just need to specify a name for the object as follows:

CREATE SEQUENCE .;

Question 16

What data type will a sequence return?

Answer 16

Like IDENTITY, all numeric types with a scale of 0 are supported. But if you don’t indicate a type explicitly, SQL Server will assume BIGINT by default. If you need a different type, you need to ask for it explicitly by adding AS after the sequence name.

Question 17

What are the options associated with the sequence object?

Answer 17

(1) INCREMENT BY - Increments value. The default is 1, (2) MINVALUE - The minimum value to support. The default is the minimum value of the type, (3) MAXVALUE - The maximum value to support. The default is the maximum value in the type, (4) CYCLE | NO CYCLE - Defines whether to allow the sequence to cycle or not. The default is NO CYCLE, (5) START WITH - The sequence start value. The default is MINVALUE for an ascending (positive increment) and MAXVALUE for a descending one.

Question 18

What happens when you set the MINVALUE to 1?

Answer 18

Not only is the minimum value set to 1, but also the start value. If you just set the start value to 1 using the START WITH property this won’t change the minimum value from -21147483648. This will turn out to be a problem if the sequence allows cycling. After you hit the last value in the type, the next value generated won’t be 1; instead it will be -2147483648. Therefore, the smarter thing to do if you need your sequence to generate only positive values is to set the MINVALUE to 1. This will implicitly set the START WITH value to 1 as well.

Question 19

How do you request a new value from the sequence?

Answer 19

Use the NEXT VALUE FOR function, e.g. SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

This function can be called in INSERT VALUES and INSERT SELECT statements, a SET clause of an UPDATE statement, an assignment to a variable, a DEFAULT constraint expression, and other places.

Question 20

Can you change the properties of a sequence?

Answer 20

You cannot change the data type of an existing sequence, but you can change all of its properties by using the ALTER SEQUENCE command, e.g. if you want to change the current value:

ALTER SEQUENCE Sales.SeqOrderIDs
RESTART WITH 1;

Question 21

When using a sequence in an INSERT SELECT statement, how can you control the order in which sequence values are assigned?

Answer 21

You can optionally add an OVER clause with an ORDER BY list to control the order in which the sequence values are assigned to the result rows:

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT
NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid), custid, empid, orderdate
FROM Sales.Orders
WHERE custid=1

This is a T-SQL extension to the standard.

Question 22

Can you use the NEXT VALUE FOR in a DEFAULT constraint?

Answer 22

Yes. You can use the NEXT VALUE FOR function in a DEFAULT constraint and this is how you would let the constraint generate the values automatically when you insert rows.

ALTER TABLE Sales.MyOrders
ADD CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;

This option is a more flexible alternative than IDENTITY because it only assigns a default value if one wasn’t specified explicitly in the INSERT statement.

Question 23

How does the sequence object’s caching option work?

Answer 23

The sequence object supports a caching object that controls how often the current sequence value is written to disk versus written to memory.

Using the cache option increases performance by minimizing the number of disk IOs that are required to generate sequence numbers.

For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

When created with the CACHE option, an unexpected shutdown (such as a power failure) may result in the loss of sequence numbers remaining in the cache.

There is a very big performance difference between using NO CACHE vs CACHE . With NO CACHE, SQL Server has to write to disk for every request of a new sequence value. With some caching, the performance is much better. The default cache value is 50.

Here’s an example of changing the cache value:

ALTER SEQUENCE Sales.SeqOrderIDs
CACHE 100;

Question 24

Does the sequence object guarantee that there won’t be gaps?

Answer 24

Similar to IDENTITY, the sequence object doesn’t guarantee that there won’t be gaps. If SQL Server creates a new sequence value in a transaction and the transaction fails, the change to the sequence value is not undone.

Question 25

What does the sp_sequence_get_range stored procedure do?

Answer 25

Allocates an entire range of sequence values of a requested size. You provide the requested range of size by using the input parameter @range_size and collect the first value in the allocated range by using the output parameter @range_first_value. Then you can assign the values in the allocated range as you want. The sequence itself gets modified only once by advancing it from its current value to the current value plus @range_size.

Question 26

What system view can you use to query the properties of sequences?

Answer 26

sys.sequences

Question 27

How many columns with an IDENTITY property are supported in one table?

Answer 27

One.

Question 28

How do you obtain a new value from a sequence?

Answer 28

Use the NEXT VALUE FOR function.

Question 29

What two features are available in SQL Server for generating surrogate keys?

Answer 29

The IDENTITY column property and the sequence object.

Question 30

What is the MERGE statement?

Answer 30

Using the MERGE statement, you can merge data from a source table or table expression into a target table. The general form of the MERGE statement is as follows:

MERGE INTO < target table > AS TGT
USING < source table > AS SRC
   ON < merge predicate >
WHEN MATCHED [AND < predicate > ]
   THEN < action >
WHEN NOT MATCHED [BY TARGET] [AND < predicate >]
   THEN INSERT...
WHEN NOT MATCHED BY SOURCE [AND < predicate >]
   THEN ;

Question 31

What does the MERGE INTO < target table> clause do?

Answer 31

This clause defines the target table for the operation. You can alias the table in this clause if you want.

Question 32

What does the USING < source table > clause do?

Answer 32

This clause defines the source table for the operation. You can alias the table in this clause if you want. Note that the USING clause is designed similar to a FROM clause in a SELECT query, meaning that in this clause you can define table operators like joins, refer to table expressions (derived tables, CTEs), or even refer to a table function like OPENROWSET. The outcome of the USING clause is ultimately a table result, and that table will be considered the source of the merge operation.

Question 33

What does the ON < merge predicate > do?

Answer 33

In this clause, you specify a predicate that matches rows between the source and the target and defines whether a source row is or isn’t matched by a target row. Note that this clause isn’t a filter like the ON clause in a join.

Question 34

What does the WHEN MATCHED [AND < predicate > ] THEN < action > clause do?

Answer 34

This clause defines an action to take when a source row is matched by a target row. Because a target row exists, an INSERT action isn’t allowed. The two actions that are allowed are UPDATE and DELETE. If you want to apply different actions in different conditions, you can specify two WHEN MATCHED clauses, each with a different additional predicate to determine when to apply an UPDATE and when to apply a DELETE.

Question 35

What does the WHEN NOT MATCHED [BY TARGET] [AND < predicate >] THEN < action > clause do?

Answer 35

The clause defines what action to take when a source row is not matched by a target row. Because a target row does not exist, the only action allowed in this clause (if you choose to include it in the statement) is INSERT. Using UPDATE or DELETE holds no meaning when a target row doesn’t exist. You can still add an additional predicate that must be true in order to perform the action.

Question 36

What does the WHEN NOT MATCHED BY SOURCE [AND < predicate >] THEN < action > clause do?

Answer 36

This clause defines an action to take when a target row exists, but it is not matched by a source row. Because a target row exists, you can apply either an UPDATE or a DELETE but not an INSERT. If you want you can have two such clauses with different additional predicates that define when to use an UPDATE and when to use a DELETE.

Question 37

How do you take a set of input parameters (variables) and turn them into a table or table expression?

Answer 37

You can use one of two options: a SELECT without a FROM clause or the VALUES table value constructor.

SELECT *
FROM (SELECT @orderid, @custid, @empid, @orderdate)
AS SRC(orderid, custid, empid, orderdate)

or

SELECT *
FROM (VALUES(@orderid, @custid, @empid, @orderdate))
AS SRC(orderid, custid, empid, orderdate)

Question 38

What do the SERIALIZABLE/HOLDLOCK hints do in the context of a MERGE statement?

Answer 38

Helps to prevent failures/conflicts that occur from running the same MERGE statement simultaneously from different processes.

Question 39

At a minimum, what clauses must be specified as part of a MERGE statement?

Answer 39

The MERGE statement does not require you to always specify the WHEN MATCHED/WHEN NOT MATCHED clauses. At a minimum, you are required to specify only one clause and it could be any of the three WHEN clauses.

Question 40

Can a MERGE statement be used as a standard alternative to an UPDATE with JOINs which isn’t standard?

Answer 40

Yes. A MERGE statement that specifies only then WHEN MATCHED clause is a standard alternative to an UPDATE statement based on a join which isn’t standard.

Question 41

How can you prevent unnecessary updates within a MERGE statement if none of the source values have changed?

Answer 41

Remember that each WHEN clause in the MERGE statement allows an additional predicate that must be true in order for the respective action to be applied. You can add a predicate that says at least one of the non key column values in the source/target must be different.

MERGE…
USING…
ON…
WHEN MATCHED AND (TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET…

Question 42

What types of queries can be used in the USING clause?

Answer 42

The USING clause is designed like the FROM clause in a SELECT statement. This means you an define table operators like JOIN, APPLY, PIVOT, and UNPIVOT; and use table expressions like derived tables, CTE’s, views, inline table functions, and even table functions like OPENROWSET and OPENXML. You can refer to real tables, temp tables, or table variables as the source.

Question 43

Is the WHEN NOT MATCHED BY SOURCE clause standard?

Answer 43

No. T-SQL extends the standard SQL by supporting WHEN NOT MATCHED BY SOURCE. With this clause, you can define an action to take against the target row when the target row exists but is not matched by a source row. The allowed actions are UPDATE and DELETE.

e.g.

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Question 44

How many WHEN MATCHED clauses can a single MERGE statement have?

Answer 44

Two - one with an UPDATE action and one with a DELETE action.

Question 45

What is the purpose of the ON clause in the MERGE statement?

Answer 45

The ON clause determines whether a source row is matched by a target row and whether a target row is matched by a source row. Based on the result of the predicate, the MERGE statement knows which WHEN clause to activate and a result which action to take against the target.

Question 46

What is the OUTPUT statement?

Answer 46

T-SQL supports an OUTPUT clause for modification statements which you can use to return information from modified rows. You can use the output function for purposes like auditing or archiving.

The design of output is similar to that of the SELECT clause in that you can specify expressions and assign them with result column aliases. When you refer to columns from the modified rows, you need to prefix the column names with the keywords, “inserted” or “deleted”. Use “inserted” when rows are inserted rows and deleted when they are “deleted” rows. In an UPDATE statement, “inserted” represents the state of the rows after the update and “deleted” represents the state before the update.

Question 47

What options do you have for directing the output of the OUTPUT statement?

Answer 47

You can have the OUTPUT clause return a result set back to the caller much like a SELECT. Or you can add an INTO clause to direct the output rows into a target table. In fact, you can have two OUTPUT clauses - the first with INTO directing the rows into a table and the second without the INTO, returning the result set from the query.

Note that if you do use the INTO clause, the target table cannot participate in either side of a foreign key relationship and cannot have triggers defined on it.

Question 48

How do you use the OUTPUT statement to return the newly generated keys after a multi-row insert?

Answer 48

An example of a very practical use case is when you have a multi-row INSERT statement that generates new keys by using the IDENTITY property or a sequence and you need to know which keys were generated.

INSERT INTO Sales.MyOrders(custid, empid, orderdate)
OUTPUT
inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate
SELECT custid, empid, orderdate
FROM Sales.Orders

If you need to store the result table instead:

INSER INTO Sales.MyOrders(custid, empid, orderdate)
OUTPUT
inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate
INTO SomeTable(orderid, custid, empid, orderdate)
SELECT custid, empid, orderdate
FROM Sales.Orders

Question 49

How do you use the OUTPUT clause to return information from deleted rows?

Answer 49

DELETE FROM Sales.MyOrders
OUTPUT deleted.orderid
WHERE empid=1;

Question 50

How do you use the OUTPUT clause with updated rows?

Answer 50

With updated rows, you have access to both the old and the new images of the modified rows. To refer to columns from the original state of the row before the update, prefix the column names with the “deleted” keyword. To refer to columns from the new state of the row after the update, prefix the column names with the keyword “inserted”.

UPDATE Sales.MyOrders
SET orderdate = DATEADD(day, 1, orderdate)
OUTPUT
inserted.orderid,
deleted.orderdate AS old_orderdate,
inserted.orderdate AS new_orderdate
WHERE empid=7

Question 51

How do you use the OUTPUT statement with MERGE?

Answer 51

You can use the OUTPUT clause with the MERGE statement, but there are special considerations with this statement. Remember that one MERGE statement can apply different actions against the target table. And suppose that when returning output rows, you need to know which action (INSERT, UPDATE, DELETE) affected the output row. SQL Server provides the $action function for this purpose. This function returns a string (‘INSERT’, ‘UPDATE’, ‘DELETE’) indicating the action.

e.g.

MERGE INTO...
USING...
WHEN MATCHED...
WHEN NOT MATCHED...
WHEN NOT MATCHED BY SOURCE...
OUTPUT
   $action AS the_action

Question 52

Can you refer to columns from both the target and source tables in a MERGE statement?

Answer 52

Yes. In a MERGE statement, you can refer to columns from both the target and source. This is different from normal INSERTs, UPDATEs, and DELETEs where you can only refer to columns from the target table in the OUTPUT clause.

Question 53

What does DML stand for?

Answer 53

Data manipulation language. It basically represents INSERTs, UPDATEs, and DELETEs.

Question 54

What is “Composable DML”?

Answer 54

Composable DML is a feature in T-SQL that allows you to use the data provided by the OUTPUT clause of a DML statement (i.e. INSERT, UPDATE, DELETE) as a derived table and thus insert it elsewhere.

Question 55

How can you use Composable DML?

Answer 55

Suppose you need to capture output rows from a modification statement, but you are only interested in a subset of the output rows and not all of them.

Composable DML allows you to create a derived table based on modification with an OUTPUT clause. THEN you can have an INSERT SELECT statement against a target table with the source being the derived table. The outer INSERT SELECT can have WHERE clause that filters the output rows from the derived table. The outer INSERT SELECT can not have other elements besides WHERE such as GROUP BY, HAVING.

INSERT INTO @InsertedOrders(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM  (MERGE INTO ...
            USING ....
            WHEN MATCHED....
            ... 
            OUTPUT
            $action AS the_action, inserted.*) AS D
WHERE the_action = 'INSERT';

Question 56

How many OUTPUT clauses can a single statement have?

Answer 56

Two - one with INTO and one without INTO.

Question 57

How do you determine which action affected the OUTPUT row in a MERGE statement?

Answer 57

Use the $action function.