Menu
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 single 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.
413
CHAR, VARCHAR, and VARCHAR(MAX) String values that vary significantly in length and are no longer than 8,000 bytes should be stored in a VARCHAR column. If you have huge strings (over 8,000 bytes), then VARCHAR(MAX) should be used. In order to store VARCHAR columns, the length information along with the data is stored.
We can use the varchar(max) column as an included column in the index, but you cannot perform the index seek on this column. It will also require additional storage. Therefore, you should avoid creating an index with the varchar(max) data type.
Even using varchar(max) shouldn't have any impact on storage. Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible.
Today's development platforms or their operating systems support the Unicode character set. Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.
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?
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
