is there an advantage to varchar(500) over varchar(8000)?

views:

225

answers:

+5 Q:

is there an advantage to varchar(500) over varchar(8000)?

I've read up on this on MSDN forums and here and I'm still not clear. I think this is correct: Varchar(max) will be stored as a text datatype, so that has drawbacks. So lets say your field will reliably be under 8000 characters. Like a BusinessName field in my database table. In reality, a business name will probably always be under (pulling a number outta my hat) 500 characters. It seems like plenty of varchar fields that I run across fall well under the 8k character count.

So should I make that field a varchar(500) instead of varchar(8000)? From what I understand of SQL there's no difference between those two. So, to make life easy, I'd want to define all my varchar fields as varchar(8000). Does that have any drawbacks?

Related: http://stackoverflow.com/questions/177354/size-of-varchar-columns (I didn't feel like this one answered my question).

Ideally you'd want to go smaller than that, down to a reasonably sized length (500 isn't reasonably sized) and make sure the client validation catches when the data is going to be too large and send a useful error.

While the varchar isn't actually going to reserve space in the database for the unused space, I recall versions of SQL Server having a snit about database rows being wider than some number of bytes (do not recall the exact count) and actually throwing out whatever data didn't fit. A certain number of those bytes were reserved for things internal to SQL Server.

Otis 2010-01-05 22:53:01

true, this used to be a lot bigger concern as well. But nowadays, space is really cheap so I don't think it's that big a concern for consideration, at least from my point of view.

BBlake 2010-01-05 23:01:44

"(500 isn't reasonably sized)" for what? A name? A paragraph? A blog post? It's all very relative unless there are obvious limits, like a ZIP code or SSN.

jcollum 2010-01-06 00:20:53

@jcollum: In your example, 500 doesn't seem reasonably sized for a business name.

Otis 2010-01-06 03:34:38

@BBlake: Regardless of the cost of storage, if SQL Server still has row size constraints than it doesn't matter how much storage you have. You could store everything in textblobs but there are some SQL operations you can't do on a blob that you can do on a varchar.

Otis 2010-01-06 03:37:05

@Otis: my point is this: there's no actual constraint on the size of a business name. Unless there's a law somewhere. So in that case I'd make that field varchar(8000) and call it a day. My thinking goes like this: Real constraint? varchar(x). No real constraint? varchar(8000).

jcollum 2010-01-11 15:35:43

@jcollum: Fair enough.

Otis 2010-01-11 16:47:55

+2 A:

From a processing standpoint, it will not make a difference to use varchar(8000) vs varchar(500). It's more of a "good practice" kind of thing to define a maximum length that a field should hold and make your varchar that length. It's something that can be used to assist with data validation. For instance, making a state abbreviation be 2 characters or a postal/zip code as 5 or 9 characters. This used to be a more important distinction for when your data interacted with other systems or user interfaces where field length was critical (e.g. a mainframe flat file dataset), but nowadays I think it's more habit than anything else.

BBlake 2010-01-05 22:53:41

Makes sense... for things that naturally have a maximum length. But what do you do when the max length isn't obvious? E.g. a business name.

jcollum 2010-01-06 00:11:33

For something like that, if I don't foresee any way to forecast what the size could potentially be, then I usually will go with a varchar(8000) or varchar(max), depending on the type of data

BBlake 2010-01-06 15:15:51

+2 A:

Apart from best practices (BBlake's answer)

You get warnings about maximum row size (8060) bytes and index width (900 bytes) with DDL
DML will die if you exceed these limits
ANSI PADDING ON is the default so you could end up storing a wholeload of whitespace

gbn 2010-01-05 22:58:10

Just to clarify about ANSI PADDING ON: when using `nvarchar` and `varchar` types, this only means that trailing spaces are preserved upon insert--not that the values are padded with spaces to the size of the column, as in `char` and `nchar`.

Ben M 2010-01-05 23:15:08

ansaurus

tags:

views:

answers:

is there an advantage to varchar(500) over varchar(8000)?

related questions