INF: Create Clustered Index with SORTED_DATA Option

ID: Q99813

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SUMMARY

If there are duplicate rows in the table, the query


   CREATE CLUSTERED INDEX index_name
   ON table_name
   WITH SORTED_DATA

will fail and the following error message will appear:

Create index aborted on duplicate rows.
(Msg 1508, Level 16, State 1)

Some users may want to use allow_dup_row combined with sorted_data options to workaround this problem. However, it will still fail with the same error message.

To work around this problem, use the following query:


   CREATE CLUSTERED INDEX index_name
   ON table_name
   WITH ALLOW_DUP_ROW

MORE INFORMATION

The description for SORTED_DATA in the "Language Reference" manual includes the following:


   It verifies that the data has indeed been sorted by checking each
   index value to see if it is higher than the previous one.

Additional query words: create clustered index

Keywords : kbprg SSrvServer
Version : 4.2
Platform : OS/2
Issue type :