August 3, 2012 at 2:13 pm
I want to change the size of a notes field from varchar(500) to varchar(900). I am getting the error message, "changing the data type of column 'field2' on table 'test1' from 'varchar(500)' to 'varchar(900)' causes the following indexes to exceed the maximum index size of 900 bytes: index2.
I am trying to determine what my options are to fix this problem. I saw online that if I use a nonclustered index on a nonkey field, the 900 byte limit does not apply. However the table I am referring to is a clustered index and I do not want to change the index for this reason.
Note there are several foreign keys in the table I am referring to, however the primary key to this table is an 'int'.
The only option I can see is to increase the size of the field to varchar(886) instead of varchar(900). Are there any other options I would have?
August 3, 2012 at 2:33 pm
The real question would be, "do you really need an index on the notes column?". Do you search on this often? Do you search on it without some other key attribues being known?
August 3, 2012 at 2:36 pm
wendy elizabeth (8/3/2012)
The only option I can see is to increase the size of the field to varchar(886) instead of varchar(900). Are there any other options I would have?
Drop index2
Why do you need such a big index anyway? It's a complete waste of space and may lead to performance problems.
August 3, 2012 at 3:16 pm
I agree with everyone re: "Why the big ol' index?"
Do you have any idea how many reads this table gets vs writes?
If the index is not used often but there are many inserts, then that index is not just sucking up space, it's becoming a drag on your inserts/updates/deletes.
If it must be there then why not let it be 886? Do we really need those extra 14 characters?
http://xmlsqlninja.blogspot.com/2012/07/removing-unused-indexes.html
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply