Columns of the type text are stored in a different way than varchar columns. Varchar columns have a much lesser effect on performance, and the amount of characters is more than enough to store paths from new york to tokio.
Text columns are stored with pointers in another place, because it is a variable length column. They aren't stored along with the row, but the row only contains a pointer to the place where the text column is stored, and this slows a table down. SQL Server features functionality to combine the performance power of varchar with the flexibility power of text by using text in row. This allows you to say to SQL Server "store the first XXX characters with the row, and the rest in another spot and create pointer to it". The will dramatically increase performance, and I could recommend everyone using text columns to use this feature. It is truly awesome. I did some extensive testing and noticed improvements over 1000% speed on large tables. The feature is called text in row. You first must enable the feature and then update each row to follow the new rules of storage. EXEC sp_tableoption 'Content', 'text in row', 'ON' GO UPDATE Content SET Data = C.Data, FROM Content C Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 AL Amersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188631 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
