What is the IDENTITY property?
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.
What do you do when you want to specify your own values into an IDENTITY column?
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.
What are the functions that you can use to query the last identity value generated?
(1) SCOPE_IDENTITY, (2) @@IDENTITY, (3) IDENT_CURRENT
What is the SCOPE_IDENTITY function?
SCOPE_IDENTITY returns the last identity value generated in your session in the current scope.
What is the @@IDENTITY function?
@@IDENTITY returns the last identity value generated in your session regardless of scope.
What is the IDENT_CURRENT function?
IDENT_CURRENT accepts a table as input and returns the last identity value generated in the input table regardless of session.
What is the difference between SCOPE_IDENTITY and @@IDENTITY?
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.
Which statement DELETE or TRUNCATE affects the IDENTITY value?
DELETE doesn’t affect the IDENTITY value whereas TRUNCATE resets it to the initial seed.
How do you reseed the current identity value?
Use the DBCC CHECKIDENT command:
DBCC CHECKIDENT(‘Sales.MyOrders’, RESEED, 4);
Does the IDENTITY property guarantee uniqueness?
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.
Does the IDENTITY property guarantee that there will be no gaps between the values?
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.
Does the IDENTITY property have cycling support?
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.
When was the sequence object introduced?
SQL Server 2012
What are some of the benefits of using the sequence object?
(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.
How do you create a sequence?
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 .;
What data type will a sequence return?
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.
What are the options associated with the sequence object?
(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.
What happens when you set the MINVALUE to 1?
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.
How do you request a new value from the sequence?
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.
Can you change the properties of a sequence?
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;
When using a sequence in an INSERT SELECT statement, how can you control the order in which sequence values are assigned?
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)
NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid), custid, empid, orderdate
This is a T-SQL extension to the standard.
Can you use the NEXT VALUE FOR in a DEFAULT constraint?
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.
How does the sequence object’s caching option work?
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
Does the sequence object guarantee that there won’t be gaps?
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.
What does the sp_sequence_get_range stored procedure do?
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.
What system view can you use to query the properties of sequences?
How many columns with an IDENTITY property are supported in one table?
How do you obtain a new value from a sequence?
Use the NEXT VALUE FOR function.
What two features are available in SQL Server for generating surrogate keys?
The IDENTITY column property and the sequence object.
What is the MERGE statement?
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 ;
What does the MERGE INTO < target table> clause do?
This clause defines the target table for the operation. You can alias the table in this clause if you want.
What does the USING < source table > clause do?
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.
What does the ON < merge predicate > do?
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.
What does the WHEN MATCHED [AND < predicate > ] THEN < action > clause do?
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.
What does the WHEN NOT MATCHED [BY TARGET] [AND < predicate >] THEN < action > clause do?
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.
What does the WHEN NOT MATCHED BY SOURCE [AND < predicate >] THEN < action > clause do?
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.
How do you take a set of input parameters (variables) and turn them into a table or table expression?
You can use one of two options: a SELECT without a FROM clause or the VALUES table value constructor.
FROM (SELECT @orderid, @custid, @empid, @orderdate)
AS SRC(orderid, custid, empid, orderdate)
FROM (VALUES(@orderid, @custid, @empid, @orderdate))
AS SRC(orderid, custid, empid, orderdate)
What do the SERIALIZABLE/HOLDLOCK hints do in the context of a MERGE statement?
Helps to prevent failures/conflicts that occur from running the same MERGE statement simultaneously from different processes.
At a minimum, what clauses must be specified as part of a MERGE statement?
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.
Can a MERGE statement be used as a standard alternative to an UPDATE with JOINs which isn’t standard?
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.
How can you prevent unnecessary updates within a MERGE statement if none of the source values have changed?
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.
WHEN MATCHED AND (TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
What types of queries can be used in the USING clause?
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.
Is the WHEN NOT MATCHED BY SOURCE clause standard?
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
How many WHEN MATCHED clauses can a single MERGE statement have?
Two - one with an UPDATE action and one with a DELETE action.
What is the purpose of the ON clause in the MERGE statement?
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.
What is the OUTPUT statement?
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.
What options do you have for directing the output of the OUTPUT statement?
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.
How do you use the OUTPUT statement to return the newly generated keys after a multi-row insert?
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)
inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate
SELECT custid, empid, orderdate
If you need to store the result table instead:
INSER INTO Sales.MyOrders(custid, empid, orderdate)
inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate
INTO SomeTable(orderid, custid, empid, orderdate)
SELECT custid, empid, orderdate
How do you use the OUTPUT clause to return information from deleted rows?
DELETE FROM Sales.MyOrders
How do you use the OUTPUT clause with updated rows?
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”.
SET orderdate = DATEADD(day, 1, orderdate)
deleted.orderdate AS old_orderdate,
inserted.orderdate AS new_orderdate
How do you use the OUTPUT statement with MERGE?
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.
MERGE INTO... USING... WHEN MATCHED... WHEN NOT MATCHED... WHEN NOT MATCHED BY SOURCE... OUTPUT $action AS the_action
Can you refer to columns from both the target and source tables in a MERGE statement?
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.
What does DML stand for?
Data manipulation language. It basically represents INSERTs, UPDATEs, and DELETEs.
What is “Composable DML”?
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.
How can you use Composable DML?
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';
How many OUTPUT clauses can a single statement have?
Two - one with INTO and one without INTO.
How do you determine which action affected the OUTPUT row in a MERGE statement?
Use the $action function.