Varchar(fixedlength) has maximum capacity of 8000. i.e you can define varchar(8000). If you want to store more than this in a varchar column you need to use varchar(max). The maximum capacity of varchar(max) is 2gb.Use varchar(max) only when its required.

If you are sure that the length of the parameter @deliverycenter will be less than 8000 characters then use varchar(500). Whilst varchar(MAX) will not affect the performance much, since they are stored in the database memory space rather than a pointer to data concept as in Sql Server 2000 (TEXT/NTEXT/ IMAGE datatypes). So the process time with varchar(MAX) is proportional to the size of the data you are processing . (Will not be of much difference).So the answer to your question is "The performance will not be affected much".

declareselect * from itemdetails i where i.deliverycentercd in (@deliverycenter)

I don't understand why you using the above syntax, instead you can use a direct comparison to compare two strings.(here you are trying to perform a case sensitive compare between @deliverycenter and i.deliverycentercd).

Rowsize > 8060 bytes ?-table size is still limited by fixed-length types, as well as internal headers and row pointers. -You cannot create a table with a row size of greater than 8060 bytes if that table is comprised of e.g. all numeric columns.row overflow:-If a row exceed 8060 bytes, the data for varying columns (VARCHAR, NVARCHAR, or VARBINARY) will be automatically moved off-row into the large object area. In addition, each column that is moved off-row will occupy a 24-byte on-row pointer. Make sure when creating large tables that the pointers will not cause rows to overflow the 8060-byte limit, or data modifications may be terminated as a result of too much data in row.

If you have off-row columns, you may end up with vast IO overhead.(One to read the pointer, the second to read the off-row column)

Johan

Don't drive faster than your guardian angel can fly ...but keeping both feet on the ground won't get you anywhere

Ya you are right, that above for 8000 bytes we need a off row data, we should also turn on the option "large value types out of row". But i wonder what is the big difference between the TEXT and VARCHAR(max) apart from the fact that VARCHAR(MAX) can be used as local variables whereas TEXT cannot be so.

TEXT is an older datatype, is deprecated in SQL 2005 and will be removed in a future version. Many of the string functions (left, right, substring, replace, etc) don't work on Text, while the do on varchar(max)