Menu
I'm a bit old school when it comes to database design, so I'm totally for using the correct data sizes in columns. However, when reviewing a database for a friend, I noticed he used varchar(max) a lot. Now, my immediate thought was to throw it back to him and tell him to change it. But then I thought about it and couldn't come up with a good reason for him not to use it (he'd used a case type tool to generate the db, if you're wondering).
I've been researching the topic of varchar(max) usage and I can't really come up with any good reason for him not to use it.
He doesn't use the columns for indexes, the application that sits on the db has limitations on the input, so it won't allow massive entries in the fields.
Any help would be appreciated to help me make him see the light :).
590
If you use char or varchar, we recommend to: Use char when the sizes of the column data entries are consistent. Use varchar when the sizes of the column data entries vary considerably. Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.
The answers is: there is no different between nvarchar(7) and nvarchar(4000) in term of performance & storage size. There is an interesting thing is that: if you change nvarchar(7) or nvarchar(4000) to nvarchar(max). There is a difference in term of performance & storage size.
varchar (n) n – is the number of bytes. The maximum storage capacity is upto 8000 bytes. varchar(max) : It stores character string data of maximum storage size 2³¹-1 bytes.
Microsoft SQL Server 2008 (and above) can store up to 8000 characters as the maximum length of the string using varchar data type. SQL varchar usually holds 1 byte per character and 2 more bytes for the length information.
My answer to this, isn't about the usage of Max, as much as it is about the reason for VARCHAR(max) vs TEXT.
In my book; first of all, Unless you can be absolutely certain that you'll never encode anything but english text and people won't refer to names of foreign locations, then you should use NVARCHAR or NTEXT.
Secondly, it's what the fields allow you to do.
TEXT is hard to update in comparison to VARCHAR, but you get the advantage of Full Text Indexing and lots of clever things.
On the other hand, VARCHAR(MAX) has some ambiguity, if the size of the cell is < 8000 chars, it will be treated as Row data. If it's greater, it will be treated as a LOB for storage purposes. Because you can't know this without querying RBAR, this may have optimization strategies for places where you need to be sure about your data and how many reads it costs.
Otherwise, if your usage is relatively mundane and you don't expect to have problems with the size of data (IE you're using .Net and therefore don't have to be concerned about the size of your string/char* objects) then using VARCHAR(max) is fine.
193
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
