More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Recovery from Large Identity value gaps in Sybase
This section will discuss how to re-order the identity values for a table following a crash/abnormal shutdown that has resulted in huge gaps in the values. The same procedure is used in cases where the identity field has "filled up" and does not allow inserts anymore. Some applications that use Identities are not truly candidates for this process (i.e., applications that depend on the identity field for business purposes as opposed to simple unique row identifiers). Applications like this that wish to rid their dependence on identities will have to re-evaluate their database design.
* Method 1:bcp out and in:
- First, (from O/S command line):
% bcp database..table out [data_file] -Usa -S[server] -N
This will create a binary bcp datafile and will force the user to create a .fmt file. The -N option tells the server to skip the identity field while bcp'ing out.
- drop and recreate the table in question from ddl (make sure your table ddl specifies the identity field).
- Now bcp back in:
% bcp database.table in [data_file -Usa -S[server] -f[fmt file] -N
The -N option during bcp in tells the server to ignore the data file's placeholder column for the defined identity column.
Coincidentally, if you bcp out w/o the -N option, drop the table, recreate from ddl specifying the identity field, and bcp back in w/o the -N option, the same effect as above occurs.
(note: if you bcp out a table w/ identity values and then want to preserve the identity values during the bcp back in, use the "-E" option.)
* Method 2: select into a new table, adding the identity column as you go : Follow this process:
1. select [all columns except identity column]
2. [identity column name ] = identity(desired_precision)
3. into [new_table]
4. from [old table]
5. go
* There are alternate methods that perform the above in multi steps, and might be more appropriate in some situations.
o You can bcp out all the fields of a table except the identity column (create the bcp format file from the original table, edit out the identity column, and re-bcp). At this point you can create a new table with or without the identity column; if you create it with, as you bcp back in the Server will assign new identity values. If you create it without, you can bcp back in normally and then alter the table and add the identity later.
o You can select all columns but the identity into a new table, then alter that table and add an identity later on.
(Continued on next question...)
