Identity Property
It is common to provide simple counter-type values for tables that don't have a natural or efficient primary key. Columns such as customer _ number are usually simple counter fields. SQL Server provides the Identity property that makes generating unique numeric values easy. Identity isn't a datatype; it's a column property that you can declare on a whole-number datatype such as tinyint , smallint , int , and numeric/decimal (having a scale of zero). Each table can have only one column with the Identity property. The table's creator can specify the starting number (seed) and the amount that value increments or decrements. If not otherwise specified, the seed value starts at 1 and increments by 1, as shown in this example:
CREATE TABLE customer ( cust_id smallint IDENTITY NOT NULL, cust_name varchar(50) NOT NULL, ) |
To find out which seed and increment values were defined for a table, you can use the IDENT_SEED( tablename ) and IDENT_INCR( tablename ) functions. The statement
SELECT IDENT_SEED('CUSTOMER'), IDENT_INCR('CUSTOMER') |
produces
1 1 |
for the customer table because values weren't explicitly declared and the default values were used.
This next example explicitly starts the numbering at 100 (seed) and increments the value by 20:
CREATE TABLE customer ( cust_id smallint IDENTITY(100,20) NULL, cust_name varchar(50) NOT NULL, ) |
The value automatically produced with the Identity property will normally be unique, but it isn't guaranteed by the Identity property itself, nor is it guaranteed to be consecutive. For efficiency, a value is considered used as soon as it is presented to a client doing an INSERT operation. If that client doesn't ultimately commit the INSERT, the value will never appear, so a break will occur in the consecutive numbers . An unacceptable level of serialization would exist if the next number couldn't be parceled out until the previous one was actually committed or rolled back. (And even then, as soon as a row was deleted, the values would no longer be consecutive. Gaps are inevitable.)
NOTE
If you need exact sequential values without gaps, Identity isn't the appropriate feature to use. Instead, you should implement a next_number -type table in which you can make the operation of bumping the number contained there part of the larger transaction (and incur the serialization of queuing for this value).
To temporarily disable the automatic generation of values in an identity column, use the SET IDENTITY_INSERT tablename ON option. In addition to filling in gaps in the identity sequence, this option is useful for tasks such as bulk loading data in which the previous values already exist. For example, perhaps you're loading a new database with customer data from your previous system. You might want to preserve the previous customer numbers but have new ones automatically assigned using Identity. The SET option was created exactly for cases like this.
Because of the SET option's ability to override values, the Identity property alone doesn't enforce uniqueness of a value within the table. Although Identity will generate a unique number, it can be overridden with the SET option. To enforce uniqueness (which you'll almost always want to do when using Identity), you should also declare a UNIQUE or PRIMARY KEY constraint on the column. If you insert your own values for an identity column (using SET IDENTITY_INSERT), when automatic generation resumes, the next value will be the next incremented value (or decremented value) of the highest value that exists in the table, whether it was generated previously or explicitly inserted.
TIP
If you're using the bcp utility for bulk loading data, be aware of the /E (uppercae) parameter if your data already has assigned values that you want to keep for a column having the Identity property. You can also use the Transact -SQL BULK INSERT command with the KEEPIDENTITY option. For more information, see the SQL Server documentation for bcp and BULK INSERT.
The keyword IDENTITYCOL automatically refers to the specific column in a table, whatever its name , that has the Identity property. If cust_id is that column, you can refer to the column as IDENTITYCOL without knowing or using the column name, or you can refer to it explicitly as cust_id . For example, the following two statements work identically and return the same data:
SELECT IDENTITYCOL FROM customer SELECT cust_id FROM customer |
The column name returned to the caller is cust_id , not IDENTITYCOL, in both of these cases.
When inserting rows, you must omit an identity column from the column list and VALUES section. (The only exception is when the IDENTITY_INSERT option is on.) If you do supply a column list, you must omit the column for which the value will be automatically supplied. Here are two valid INSERT statements for the customer table shown earlier:
INSERT customer VALUES ('ACME Widgets') INSERT customer (cust_name) VALUES ('AAA Gadgets') |
Selecting these two rows produces this output:
cust_id cust_name ------- --------- 1 ACME Widgets 2 AAA Gadgets (2 row(s) affected) |
Sometimes in applications, it's desirable to immediately know the value produced by Identity for subsequent use. For example, a transaction might first add a new customer and then add an order for that customer. To add the order, you probably need to use the cust_id . Rather than select the value from the customer table, you can simply select the special system function @@IDENTITY, which contains the last identity value used by that connection. It doesn't necessarily provide the last value inserted into the table, however, because another user might have subsequently inserted data. If multiple INSERT statements are carried out in a batch to the same or different tables, the variable has the value for the last statement only.
You can't define the Identity property as part of a UDDT, but you can declare the Identity property on a column that uses a UDDT. A column having the Identity property must always be declared NOT NULL (either explicitly or implicitly), or error message number 8147 will result from the CREATE TABLE statement, and CREATE won't succeed. Likewise, you can't declare the Identity property and a DEFAULT on the same column. To check that the current identity value is valid based on the current maximum values in the table, and to reset it if an invalid value is found (which should never be the case), use the DBCC CHECKIDENT ( tablename ) statement.
Identity values are fully recoverable. If a system outage occurs while insert activity is taking place with tables that have identity columns, the correct value will be recovered when SQL Server is restarted. This is accomplished during the SQL Server checkpoint processing by flushing the current identity value for all tables. For activity beyond the last checkpoint, subsequent values are reconstructed from the transaction log during the standard database recovery process. Any inserts into a table having the Identity property are known to have changed the value, and the current value is retrieved from the last INSERT statement (post-checkpoint) for each table in the transaction log. The net result is that when the database is recovered, the correct current identity value is also recovered.
SQL Server, unlike some other products, doesn't require that you maintain a large safety buffer or burning set. After a system failure, products that don't recover their autosequencing values sometimes add a large number to the last known value on recovery to ensure that a number isn't reused. This can result in odd and probably undesirable values. For example, values might be progressing nicely as 101, 102, 103, 104, 105, and so on. Then a system outage occurs. Because the next value isn't recovered, these products don't detect exactly where to resume (104? 105? 106? 107?). To avoid reusing a number, these products simply add a safety buffer; the number after 105 might be 1106, with a safety buffer of 1000. This can result in some odd patterns for what are loosely thought of as sequential numbers (for example, 102, 103, 104, 105, 1106, 1107, 2108, 2109, 3110). Because SQL Server recovers the exact value, large gaps like this never occur.
In rare cases, the identity value can get out of sync. If this happens, you can use the DBCC CHECKINDENT command to reset the identity value to the appropriate number. In addition, the RESEED option to this command allows you to set a new starting value for the identity sequence. Take a look in the online documentation for complete details.
EAN: 2147483647
Pages: 144