Flashcards in Chapter 11 Other Data Modification Aspects Deck (57)
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.