More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Advantages/Features of Using Identities in Sybase
There's an entire section devoted to Identity columns in the ASE Reference manual
Sybase System 10 introduced many changes over the 4.9.x architecture. One of these changes was the Identity feature. The identity column is a special column type that gets automatically updated by the server upon a new row insert. Its purpose is to guarantee a unique row identifier not based on the other data in the row. It was integrated with the server and made memory based for fast value retrieval and no locking (as was/is the case with homegrown sequential key generation schemes).
The Advantages and Features of Identities include:
* A non-SQL based solution to the problem of having an default unique value assigned to a row. ASE prefetches identity values into cache and adds them automatically to rows as they're inserted into tables that have a type Identity column. There's no concurrency issues, no deadlocking in high-insert situations, and no possibility of duplicate values.
* A high performance Unique identifier; ASE's optimizer is tuned to work well with Unique indexes based on the identity value.
* The flexibility to insert into the identity field a specific value in the case of a mistaken row deletion. (You can never update however). You accomplish this by:
1. set identity_insert [datababase]..[table] on
2. go
Note however that the System will not verify the uniqueness of the value you specifically insert (unless of course you have a unique index existing on the identity column).
* The flexibility during bcp to either retain existing identity values or to reset them upon bcping back in. To retain the specific identity values during a bcp out/in process, bcp your data out normally (no special options). Then create your bcp in target table with ddl specifying the identity column in the correct location. Upon bcp'ing back in, add the "-E" option at the end of the bcp line, like this (from O/S prompt):
% bcp [database]..[new_table] in [bcp datafile] -Usa -S[server] -f [fmt file] -E
For procedures on resetting identity values during a bcp, see the section regarding Identity gaps.
* Databasewide Identity options: 1) The ability to set Sybase to automatically create an Identity column on any table that isn't created with a primary key or a unique constraint specified. 2) Sybase can automatically include an Identity field in all indexes created, guaranteeing all will be unique. These two options guarantee increased index performance optimization and guarantees the use of updateable cursors and isolation level 0 reads.
These features are set via sp_dboption, like this:
1. sp_dboption [dbname], "auto identity", true
2. go
or
1. sp_dboption [dbname], "identity in nonunique index", true
2. go
To tune the size of the auto identity (it defaults to precision 10):
1. sp_configure "size of auto identity", [desired_precision]
2. go
(the identity in nonunique index db_option and the size of auto identity sp_configure value are new with System 11: the auto identity existed with the original Identity feature introduction in System 10)
Like other dboptions, you can set these features on the model database before creating new databases and all your future databases will be configured. Be warned of the pitfalls of large identity gaps however; see the question regarding Burn Factor Vulnerability in the Common Questions about Identities section.
* The existence of the @@identity global variable, which keeps track of the identity value assigned during the last insert executed by the server. This variable can be used programming SQL around tables that have identity values (in case you need to know what the last value inserted was). If the last value inserted in the server was to a non-identity table, this value will be "0."
(Continued on next question...)
