INF: Create Clustered Index with SORTED

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.

INF: Create Clustered Index with SORTED_DATA Option (99813)

SUMMARY

MORE INFORMATION