Identity column

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server, that uniquely identifies the row within the table. Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table.

Syntax:
Create table table_name
(column_name numeric(precision ,0) identity)
go

  • IDENTITY columns must have a datatype of numeric and scale of 0, or any integer type.
  • You can define the IDENTITY column with any desired precision, from 1 to 38 digits.
  • You define an IDENTITY column by specifying the keyword identity, instead of null or not null.
  • By default, Adaptive Server begins numbering rows with the value 1, and continues numbering rows consecutively as they are added. Some activities, such as manual insertions, deletions, or transaction rollbacks, and server shutdowns or failures, can create gaps in IDENTITY column values.

Example:
Create table sales_detail
(order_num numeric(16 ,0) identity,
item_name varchar(50) not null)
go

Insert into sales_detail (item_name)
values(‘COMPUTER’)
go

Above insert will insert numeric number automatically in order_num column.

order_num     item_name
1                      COMPUTER

Views that include IDENTITY columns: You can define a view that includes an IDENTITY column by listing the column name, or the syb_identity keyword, in the view’s select statement.

Example:
Create view sales_view
as select syb_identity, stor_id
from sales_daily
go

Including IDENTITY columns in nonunique indexes:
The identity in nonunique index database option automatically includes an IDENTITY column in a table’s index keys so that all indexes created on the table are unique. This option makes logically nonunique indexes internally unique and allows them to process updatable cursors and isolation level 0 reads.

Command to enable identity in nonunique indexes:
sp_dboption <db_name>, “identity in nonunique index”, true
go

Note: The table must already have an IDENTITY column for the identity in nonunique index database option to work.
Example:
Create table sales_detail
(order_num numeric(16 ,0) identity,
item_name varchar(50) not null)
go

Create index idx1 on sales_detail (item_name)
go

We created index on only column item_name. now if we run sp_helpindex sales_detail it will return below.
index_name           index_keys
idx1                         item_name, order_num

That means, it included column order_num as well (which is defined as IDENTITY column in table) automatically in idx1.

Create IDENTITY columns with user-defined datatypes:
Example:

sp_addtype ident, “numeric(5)”, “identity”
go

This example shows an IDENTITY column based on the ident datatype:
Create table sales_detail
(order_num ident, item_name varchar(50) NOT NULL)
go

Note: You cannot create an IDENTITY column from a userdefined datatype that allows null values.

Note: You can bind a default to an IDENTITY column or to a userdefined datatype with the IDENTITY property, but Adaptive Server ignores such defaults and assigns value that is greater than the last IDENTITY value assigned.

Referring to IDENTITY columns with syb_identity:
Once you have defined an IDENTITY column, you need not remember the actual column name. You can use the syb_identity keyword, qualified by the table name where necessary, in a select,insert, update, or delete statement on the table.
Example:
select * from sales_detail
where syb_identity = 1
go

Creating “hidden” IDENTITY columns automatically:
System Administrators can use the auto identity database option to automatically include a 10digit IDENTITY column in new tables. To turn this feature on in a database, use:

sp_dboption database_name,”auto identity”,”true”
go

Note: You must be connected to master database while running this command to change database option.

Example:
sp_dboption <database_name>, “auto identity”, “true”
go

Note: If you create it like above, The IDENTITY column is not visible when you use select * to retrieve all columns from the table. You must explicitly include the column name, SYB_IDENTITY_COL (all uppercase letters), in the select list.

Now create a table as below:
Create table identit_test (col1 char(1))
go

run sp_help identit_test and check column names. It created table with 2 columns named as below:

SYB_IDENTITY_COL
col1

Insert a value:
Insert into identit_test values (‘A’)
go

Run below select query:
Select SYB_IDENTITY_COL,* from identit_test
go

It will display following row:
SYB_IDENTITY_COL              col1
1                                               A

To set the precision of the automatic IDENTITY column, use the size of auto identity configuration parameter. For example, to set the precision of the IDENTITY column to 15 use:
sp_configure “size of auto identity”, 15
go
Explicitly inserting data into an IDENTITY column:
set identity_insert sales_daily on
go
insert sales_daily (syb_identity, stor_id) values (101, “1349”)
go

Note: set identity_insert on for only one table at a time in a database within a session.
Note: After you set identity_insert off, you can insert IDENTITY column values automatically, without specifying the IDENTITY column, as before.

Updating IDENTITY columns:
You can use the syb_identity keyword, qualified by the table name, where necessary, to update an IDENTITY column.
Example:
Update emp_transaction set emp_dept = “IT”
where syb_identity = 1
go

Deleting from IDENTITY columns:
You can use the syb_identity keyword in a delete statement on tables containing an IDENTITY column.
Example:
Delete sales_monthly
where syb_identity = 1
go

This entry was posted in Identity column, RDBMS concepts, Sybase ASE and tagged , , . Bookmark the permalink.

Leave a Reply