16
WARNING: A non-unique clustered index has been forced on an isolation level 0 scan on table '%.*s'. If the scan must restart, the scan will be repositioned at the beginning of the duplicate key group. Thus, it is possible that this can infinitely loop.
Isolation level 0 (zero) allows transactions to read uncommitted data (dirty reads). Since level 0 scans do not acquire locks, modifications by other processes can cause rows being scanned to move. When this occurs, Adaptive Server restarts its scan. To properly restart, the scan must use the same key used to find the modified row and start again from the next key in the index. Thus, the scan must be on a unique index.
Error 314 occurs when a unique index does not exist during a dirty read scan and you have used forceindex to force Adaptive Server to use a nonunique clustered index for your table scan. In this case, Adaptive Server uses key values to restart the scan at the first row that has key values. However, the restart will either be approximate or fail altogether if a row becomes invalidated. If the restart fails, your query will abort. In addition, it is possible that the scan can go into an infinite loop.
This is a warning message and no action is required. However, since the consequences are severe, consider using the following options instead of forceindex.
Create a unique index using one of the following methods:
Drop your existing index. Create an index with the unique option or use alter table add constraint with the unique option.
Create a new index using create index with the unique option or use alter table add constraint with the unique option.
When you create an index with the unique option,
if there is a duplicate key value or if more than one row contains
a null value, the command is aborted and error 1505 is raised, showing
the duplicate value. Refer to “Error 1505” if that happens.
Add an IDENTITY column using alter table and the identity option.
If your table has one or more IDENTITY columns, you can, for future indexes you create for this table, use the database option identity in nonunique index. When this option is set, any nonunique index created on a table with an IDENTITY column will have the IDENTITY column automatically included as the last key field of the index. This allows an otherwise nonunique index to be used for a cursor declared for update.
However, all indexes would then be considered unique by the optimizer since every index that is created would be unique and this could result in poor performance for some queries. The identity in nonunique index option does not affect existing indexes, only future indexes you create.
WARNING! Do not use the identity in nonunique index option unless you plan to test your queries.
To use the identity in nonunique index option, the table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.
The commands to set this option are:
1> use master 2> go 1> sp_dboption <database_name>, 2> "identity in nonunique index", true 3> go 1> use <database_name> 2> go 1> checkpoint 2> go
Refer to the Reference Manual: Commands for information about create index and alter table, and Reference Manual: Procedures for information about sp_dboption.
All versions
