When creating database columns, is varchar(n) better or worse than varchar(max)
Tags: General, Coldfusion
Well I come from the old school where you define the length of the data that needs to go into that column, and yet I see that people don't adhere to this way of thinking that often. I am also more used to MS SQL and mySQL than any of the others, and I know the tools for these fairly well. And the one thing that annoys me the most when I see varchar(max) is that I don't know what you are asking from it.
In other words if you are looking at the schema, or the table in and IDE then you see straight away that the username is going to be at max 50 chars in length.
So after doing some reading on this I thought I would ask the question, which is better and why?
As I said I come from the old school of DB design, and I look at that if I open the design of the table up I can see at a snapshot what everything is. With varchar(max) I don't see this, and it leaves it open for problems down the track.
For example lets look at a simple login for example, with two fields username and password in a user table. Now if I set the varchar(max) to the username, and write a little editor to edit/create this user I am setting there that the max input can be 50 chars. Sounds good so far, and you might think that by doing this you are also allowing for future changes to be simple.
You could not be more wrong.
Let me explain further. Lets say the above scenario in this case and another use has to write a maintenance program to make changes to the information somewhere else, usually they would look at the database and see that we expect it to be a max of 50. However this user didn't place any constraints onto there small maintenance section, as they might not have designed it in the first place or just didn't know.
Some months later the original maintenance program has been used and seemed to have caused no problems. Until that support call comes in to say that they can no longer login, and that they have tried to send their details but the system says it can't find them. When the developer goes looking he finds that the information was entered by the new program, and then later by the original and without realising it the saving of the data ended up truncating the information because the input in the original field would only allow 50 chars.
This is a serious problem to face and one that I personally hate seeing, especially when the developers use the excuse about being lazy that they end up using varchar(max) as it cause more issue and headaches down the track, something that is not thought about at the beginning.
Now I can already hear people saying that you can use constraint checks on the database table schema, and that is very true but it is not the sort of thing that I would say one should rely on either. In other words in an ideal world it would be best to do both, because most people are not used to using check constraints and will not go looking for them either.
I did a bit of research into this and the most common scenario was that those who use the varchar(max), did it because they were lazy and thought that it would be fine to do so.
Now this is my opinion after developing database applications for 20 years, and I think that the rule of 3 levels of validation come into play here as well, which a lot of people are not used to doing either.
When creating database columns, is varchar(n) better or worse than varchar(max) - http://cfbloggers.org/?c=45569
Nov 5, 2010
-
I never use (max) and doing so has actually caused SQL errors in stored procedures! A DBA used (max) in his SPs when creating temp tables. It took me hours to debug that and I still don't know why (max) failed but (n) worked fine.
It's also good to enforce some kind of limit. The GUI should always have a limit and it needs to be backed up by application code and in the database. IMHO.# Posted By Gary Fenton | 11/5/10 6:08 AM -
Andrew,
I belive varchar(max) and nvarchar(max) are intended by Microsoft to replace the text and ntext datatypes, which will be deprecated. These new datatypes have their place, mostly when moving away from text and ntext. You are right, though, that they shouldn't be used without much care and careful consideration of the implications for all systems that access the data.
Carl# Posted By Carl Von Stetten | 11/5/10 10:13 AM -
@gary I totally agree, and why I posted this as well. but am interested on every ones opinion.
# Posted By Andrew Scott | 11/6/10 1:20 AM -
@Carl, hmm not sure I have read that to be the case as text and ntext are also supported by many other databases as well.
But knowing M$ anything is possible.# Posted By Andrew Scott | 11/6/10 1:22 AM -
@Paul, that is still being lazy. I think that varchar(max) should only be used when you are going to be including large bodies of text. I can name some example where varchar(n) can be used but can still be limited to 512 chars. Rare as it is.
Lets say you are entering an input field, by using varchar(max), you are basically declaring that you are going to allow any length of text/chars in that field.
But the title of my blog posts could be as much as 256-300 chars in length but I would not vision them ever reaching past 512 chars in length either.
My summary for my blog posts are limited to 4000 chars, so why would I want to limit my summary fields out to the maximum allowed. varchar(4000) is very clear in saying that this is like a summary of text, and that it can't or shouldn't have any more than 4000 characters.# Posted By Andrew Scott | 11/7/10 10:38 PM
My thoughts are. If the text is greater then 255 then use varchar(max) if its less then 255 then specify the size. Also you cannot put an index on varchar(max) another reason to set a varchar() length.
TweetBacks