Chapter 11 Other Data Modification Aspects

Question 1

What is the IDENTITY property?

Answer 1

The 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 ON;.

Note that there’s no option that can be set to update an IDENTITY column.

Question 3

What are the functions that can be used to query the last identity value generated?

Answer 3

1. SCOPE_IDENTITY,
2. @@IDENTITY,
3. IDENT_CURRENT (Schema.Table)

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.

@@IDENTITY will return the value generated by P2 without consideration to scope.

Question 8

Which statement DELETE or TRUNCATE affects the IDENTITY value?

Answer 8

TRUNCATE resets it to the initial seed.

DELETE doesn’t affect the IDENTITY value

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.

Explicit values can be entered if IDENTITY_INSERT option is turned on.

Also, the values can be reseeded.

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 in 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 .;

CREATE SEQUENCE Test.DecSeq

AS decimal(3,0)

START WITH 125

INCREMENT BY 25

MINVALUE 100

MAXVALUE 200

CYCLE

CACHE 3;

Question 16

What data type will a sequence return?

Answer 16

Like IDENTITY, all numeric types with a scale of 0 are supported.

If no type is explicitly indicated, SQL Server will assume BIGINT by default.

If a different type is needed, explicitly state it by adding AS after the sequence name.

Question 17

What are the options associated with the sequence object?

Answer 17

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

Question 18

What happens when you set the MINVALUE to 1?

Answer 18

Both the Minimum Value and Start Value is set to 1.

Not only is the minimum value set to 1, but also the start value.

If the start value is set to 1 this does not change the minimum value from -21147483648.

This will be a problem if the sequence allows cycling.

After the last value in the type, the next value generated will not be 1.

Instead it will be -2147483648.

Therefore, to generate only positive values 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.

SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

This function can be called in

1. INSERT VALUES
2. NSERT SELECT Statements
3. SET clause of an UPDATE statement
4. An assignment to a variable
5. A DEFAULT constraint expression
6. And the list goes on…

Question 20

Can you change the properties of a sequence?

Answer 20

Mostly Yes, One No.

The data type of an existing sequence cannot be changed. ,

All of the other properties can be changed using the ALTER SEQUENCE command.

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

An optional OVER clause with an ORDER BY list can be used 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.

Use the NEXT VALUE FOR function in a DEFAULT constraint. This will generate the constraint 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 Caching object controls how often the 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 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.

The default cache value is 50.

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 value is not undone.

Question 25

What does the sp_sequence_get_range stored procedure do?

Answer 25

SP_SEQUENCE_GET_RANGE Allocates an entire range of sequence values of a requested size.

Provide the requested range by using the input parameter @range_size and collect the first value in the allocated range by using the output parameter @range_first_value.

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.

SELECT NEXT VALUE FOR Schema.Sequence

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, data from a source table or table expression can be merged 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.

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. An additional predicate can be added to further restrict this action.

Using UPDATE or DELETE holds no meaning when a target row doesn’t exist.

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

1. SELECT without a FROM clause
2. 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 the WHEN MATCHED clause is a standard alternative to an UPDATE statement based on a join which is not standard.

Question 41

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

Answer 41

Each WHEN clause in the MERGE statement allows an additional predicate that must be true in order for the respective action to be applied.

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…

OR

MERGE… USING… ON… WHEN MATCHED

AND EXISTS

(

SELECT SRC.custid, SRC.empid, SRC.orderdate

EXCEPT

SELECT TGT.custid, TGT.empid, TGT.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.

WHEN NOT MATCHED BY SOURCE THEN DELETE; –> Deletes Target Row

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

The OUTPUT clause can return a result set back to the caller, like a SELECT.

Or, an INTO clause can direct the output rows into a target table.

There can be 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 the INTO clause is used, 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

A multi-row INSERT statement generates new keys by using the IDENTITY property or a sequence. This is how 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

To store the result table instead:

INSERT 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

For updated rows, there is access to both the old and the new images of the modified rows.

The original state of the row is accessed with the prefix “deleted”.

The new state of the row is accessed with the prefix “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

There are special considerations with this statement. Remember that one MERGE statement can apply different actions against the target table.

And, when returning output rows, the action (INSERT, UPDATE, DELETE) affects the output row.

SQL Server provides the $action function for this purpose.

This function returns a string (‘INSERT’, ‘UPDATE’, ‘DELETE’) indicating the action.

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’s OUTPUT clause?

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

To capture output rows from a modification statement where the interested is in a subset of the output rows.

Composable DML allows you to create a derived table based on a 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 a WHERE clause that filters the output rows from the derived table. The outer INSERT SELECT, with the exception of WHERE, cannot have other elements such as GROUP BY or 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.