Is there any sense in using byte sized column lengths?

I would say that size of the column should represent the type of data you are storing. If there is any speed benefit to it, it will be negligible, and the headaches you have with improper column lengths will far outweigh any thing gained.

The other thing that you have to keep in mind is that there is a maximum record length in Sql 2000. I think its something around 8192 bytes. If you are allocating fields in powers of 2 you are potentially adding length from one column that will be needed elsewhere. If your columns are small and there aren't many per table, this won't be an issue, but tables with large varchar fields (say around the 5000 range which means you'd have to allocate 8192 to follow the power of 2 convention) you are going to have to allocate much more space for that field than is needed, and it will quite possibly prevent you from adding other fields which are needed in that table as well.

You can break your rule for this scenario, but that means that you have stopped following the rule you have set. That can be ok, but it also means that when someone goes back and looks at the overall schema later, the question will arise as to why certain columns follow one set of rules for allocating space and others do not.

True, but varchar columns lengths are very often chosen arbitrarily.

Joel Coehoorn 2009-05-14 14:42:37

I agree, but that doesn't mean that picking a length based on something not related to the data stored is any better.

Kevin 2009-05-14 14:44:20

Precisely. Is it possible that if the column length could be anywhere from 900 to 1100, that 1024 is the "better" choice? How does the column length affect paging?

Nick DeVore 2009-05-14 14:45:07

@Nick: Definining your varchar(1024) column doesn't mean that it will occupy 1024 bytes per row. That means it will occupy UP TO 1024, so it could only occupy, say, 50 bytes depending on the size of the data. If the size DID have an effect on paging and using a power of two was a benefit, then you'd only realize this if your data was actually a power of two in length (which means that the actual column length wouldn't be relevant, other than definining a MAXIMUM that was a power of two).

Adam Robinson 2009-05-14 14:51:47

With all of that said, I can see no reason why a length being a power of two would have any effect, certainly not one that could be noticed or reliably measured.

Adam Robinson 2009-05-14 14:52:27

Not true. A char field requires that amount of space no matter what. Varchars only use the required space.

Matt 2009-05-14 15:03:00

@Matt: The question is about varchar fields, not char fields.

Guffa 2009-05-14 15:21:13

ansaurus

tags:

views:

answers:

Is there any sense in using byte sized column lengths?

related questions