C# web using linq

Question

In a C# 2010 web application that uses linq to sql datacontext object that connects to a sql server 2008 r2 database, I need to do a maintenance change to modify 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 when change the column size using sql server management studio 2008 r2.

I am assuming that the datacontext object added this notes field to the index? If I am correct or not, can you tell me why a data field of this size would be used as a foreign key by the data context object.

I am trying to determine what my options are to fix this problem. The following is what I noted so far:

1. I saw 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).

2. Could I change the field to a text size? This way the field can be the size the user may need. Can you tell me if this is a good option or not any why?

3. Is there a way I could change the data context object so this field is not a foreign key to another table? If so, how would I accomplish this goal?

4. Are there any other options I would have?

Akkiraju Ivaturi · Answer

SQL server computes the size of all the key columns which have index (indexed keys) and if the total size is more than 900 bytes it gives you gives you an error. So in a table if we want to add columns under index where it already reached 900 bytes limit there is a provision provided by SQL Server 2008 i.e. Include columns. You can include non key columns of 2GB size limit without any problem. Please find the steps to include non key columns in the index: In Object Explorer click the plus sign to expand the database that contains the table on which you want to create an index with nonkey columns. Click the plus sign to expand the Tables folder. Click the plus sign to expand the table on which you want to create an index with nonkey columns. Right-click the Indexes folder point to New Index  and select Non-Clustered Index… . In the New Index dialog box on the General page enter the name of the new index in the Index name box. Under the Index key columns tab click Add… . In the Select Columns from table_name dialog box select the check box or check boxes of the table column or columns to be added to the index. Click OK . Under the Included columns tab click Add… . In the Select Columns from table_name dialog box select the check box or check boxes of the table column or columns to be added to the index as nonkey columns. Click OK . In the New Index dialog box click OK . Or if you use T-SQL USE AdventureWorks2012; GO -- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns. -- index key column is PostalCode and the nonkey columns are -- AddressLine1 AddressLine2 City and StateProvinceID. CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1 AddressLine2 City StateProvinceID); GO Please visit the following URLs which would be more useful and might help you: http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms190806.aspx Please feel free to ask if you have still questions on this.

dc · Answer

Why does the 'include' statement do for me and can you show me an example of how to use the include in some sql?

Akkiraju Ivaturi · Answer

An index can only hold 900 bytes per record. It seems your column Notes is part of some index and it's ok until you acutally store more than 900 bytes in field 2 column. Rewrite the indexes holding Notes column and use the INCLUDE keyword. Do not try to index the column directly.