In SQL2000, you were limited to 8060 bytes per row. Things like Text and nText stored 16 bytes in the row, which was just a pointer to another location where the actual data was stored.
Also with SQL2000, you could define columns in a table that exceeded the 8060, but you would get an error if the data exceeded 8060 bytes.
With SQL2005 and up, things changed a little. You are still limited to 8060 bytes for the "in row data". If you add data to the row that causes it to exceed the 8060 bytes, SQL will move the data out of the row for you automatically. There is a performance penalty for doing this.
When you have columns that can hold large amounts of text, you can create another table that has a foreign key back to the original table and use it to store the large text. This approach is preferred because it keeps the main table narrow and narrow tables have significantly better performance.
The reason there is an 8060 byte limit is because SQL Server stored data to disk in 8K chunks. Each row has some overhead, so you are reduced to 8060 bytes. When SQL reads data from disk, it always loads the entire 8K chunk. Always. If this 8k of data contains just one row and you have 1,000,000 rows in the table, SQL would need to go to the disk 1,000,000 times to read the whole table. If your in-row data fits in 80 bytes, that's approximately 100 rows per 8K data, so SQL would only need to go to the disk 10,000 to read the entire table. That's 100x less work, therefore better performance.
Often times, large text columns are only used for individual reports (and edit screens), but there are usually a ton of reports that don't use this data.
For example, suppose you were tracking job applicants. You would want to store, name, phone, address, etc... You may also want to store the entire resume in a text (varchar max) column. You probably wouldn't run reports where you show the Resume column for 1,000's of applicants. You would run reports that show highest level of education, the city where they live, etc... If you wanted to print the details for certain applicants, you would probably want to include the resume.
The reports that don't include the large text columns will be much faster if the text column is in another table.
That being said, you are likely to see a difference only if your table is very large. For small-ish tables, there's not likely to be any (noticeable) difference in performance.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
