SQL Server: Better to use varchar(MAX) or keep a separate notes table and INNER JOIN it?

We have a primary record that allows users to enter notes. There are 18 individual "notes" fields for each primary record.

Currently we have normalized it to another table called notes, with an ID foreign key column and a sing开发者_开发知识库le varchar(8000) column, then we just INNER JOIN them together as necessary. I believe this was the recommended approach in SQL Server 2000.

We recently migrated to SQL Server 2008 which has varchar(MAX) and we're wondering if it is better or equal for performance if we get rid of our separate notes table and use varchar(MAX) instead. It sure would be more convenient.

I'm not entirely clear on your setup...if you're saying that the user can enter 18 individual notes (presumably of different "types"), then you should keep the secondary notes table. In this case, yes, switching from varchar(8000) to varchar(MAX) will allow the user to store more than 8000 characters in the notes.

Just to be clear, if the user is entering individual notes, then you should leave the tables normalized as you have them now. Whether or not you should switch from varchar(8000) to varchar(max) is a question of whether or not you want to allow users to entier more than 8000 characters. Note that, if they do, the content will be stored off-row, as if you'd been using the TEXT type in pre-2005 SQL Server.

If you're saying (as somewhat sounds like you are) that the user can enter one large note and you dynamically split it up into multiple chunks of a maximum of 8000 characters each, then you should remove the second table and put a single varchar(MAX) column on the parent record.

Is this what you're asking?

Having a varchar(max) field in your "primary" table is a bad idea, especially if you already have it somewhat normalized.

This will also lead to page splits, fragmentation, and really poor performance on your main table.

When someone adds a note, if the field is not in that notes table, it may fill up the data page and cause it to split to another page, which is fragmentation, which is BAD.

The better design would be to keep the Notes table (you can still increase the length of the Note by using VARCHAR(MAX) which would introduce some slow-down).

This will allow you to treat each note independently of the others as separate entities in the database.

I would set up a single Note table with a primary key consisting of the foreign key to its parent entity and a note number, and a single [n]varchar(max) column. This allows you to have multiple notes per parent entity.

varchar(max) will store up to 8000 octets in a single, conventional column. if the size exceeds that (up to its 2.1gb max), the data spills over to overflow pages, much like the [now-deprecated] text/ntext/image did. Dealing with text/blob data was a huge hassle: SQL now handles it sotto voce, as it were. All you get back is a string.