VARCHAR(MAX) Performance in SQL Server 2008 R2

There has been some discussion around the relative performance of VARCHAR(MAX) versus VARCHAR(n) columns. I thought it would be useful to benchmark the performance of INSERT, UPDATE and BULK INSERT of these datatypes.

Essentially, I found that there is a performance cost to using VARCHAR(MAX) over VARCHAR(8000). This would suggest that you should only use VARCHAR(MAX) when you need to. (Ie you have data that can be longer than 8000 bytes.) I would add to this by saying that data types should always be as tight as practical. It would not be a good practice to make all varchar data types MAX. For example, if you application has a screen that only permits 30 characters, it would make sense to make the data type VARCHAR(30). Although, I could make exceptions if I thought this might be increased in future.

Notice; in the results of my test that INSERT and BULK INSERT are only penalised by cpu. UPDATE statements appear are penalised by reads and writes too. Consequently, inserts were only slower by a factor of 2, while updates were slower by a factor of 7. All tests were inserting or updating 110,879 rows taking 42MB. Compression was not used.