+5
Hi,
I created an index on 2 columns to get unique value.
However, I am running in below error in creation :
An unknown error occurred while executing 'create unique nonclustered index "unique_invoice_number"on "invoice"( [INDEX_COLUMN_DEF]) include ("company_id", "invoice_number" )'. Error: 'Column name 'INDEX_COLUMN_DEF' does not exist in the target table or view.'.
Please let me know what am I doing wrong?
Best answer by Mark Jongeling
View original
+23
Hi Rucha,
Indexes cannot be created for Views, could that be the issue here? Or else looking at the sql_indexes control procedure code, the "included” field is set to True which is not allowed.
Edit: I now see Included is true in your image. Now your Index does not have any regular columns. The index columns in this index should not be "included”.
Hope this helps!
It seems you only have included columns on the index. This is not allowed. There should be a validation to prevent this (I will create an idea for this). When creating an index you need at least 1 column where included is not ticket and you can add as many included columns as you like (but adding too many is not a good thing).
+5
Thanks for the quick reply Mark!
Its a table and now I updated the check box for “included” .
Still got below error in creation :
An unknown error occurred while executing ''. Error: 'Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'unique_invoice_number' has maximum length of 2004 bytes. For some combination of large values, the insert/update operation will fail.'.
+23
Hey Rucha,
SQL Server has a limit on unique indexes. The combined length of the columns should not exceed 1700 bytes. Best thing to do is to limit the size of the used datatypes of the columns.
+5
Thanks for the reply.
How can I limit the size of the data type?
col A → nvarchar_1000
col B → int_dom
+23
Nvarchar takes twice the amount of space as the number indicates, so 1000 means 2000 bytes. To prevent the error, attach a datatype that is smaller. For example a nvarchar(100) should be sufficient in most cases.
I haven't seen invoice numbers be bigger than 100 yet.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
