The assign as key statement dynamically creates a new key for a table. The parameters for the new key are used to specify the sorting order as the table’s data is returned from SQL Server™.
assign key_variable as key for table table_name {with key_options} using table_field {with segment_options} {, table_field {with segment_options}}...
• key_variable – An integer variable to which the new key will be assigned. This variable will be used when the new key is applied.
• table table_name – The table for which the new key is being created.
• with key_options – An optional clause that specifies options for the key. The following table lists the options that are available.
![[spacer]](1p.gif){kind=small}
|
Key option |
Description |
|---|---|
|
KEY_OPTION_ALLOW_DUPLICATES |
Indicates that duplicate key values are expected based on the key definition. |
|
KEY_OPTION_SQL_UNIQUE |
Indicates that key values are expected to be unique based on the key definition. This is the default if no key option is specified. |
|
It’s important to correctly specify whether the new key should allow duplicates. If the new key specifies that the key values will be unique, but encounters duplicate key values, the order of the rows returned will be inconsistent. |
• using table_field – A reference to the first field in the table to use for the new key.
• with segment_options – An optional clause that specifies the options to use for the segment created from the table_field. The following table lists the options that are available.
|
Key segment option |
Description |
|---|---|
|
KEY_SEGMENT_OPTION_DESCENDING |
Segment is sorted in descending order. If this option is not specifed, the segment is sorted in ascending order. |
• table_field with segment_options – Additional optional clauses that define additional segments for the key. Each additional clause should be separated by a comma.
The assign as key statement is typically used when retrieving data from a SQL table, but there isn’t a key available that will sort the data in the desired order.