>>Yes, in SQL 2000 fixed lenght data types always take the same space regardless what value is stored in it.<
>
>Another good reason to use varchars!!! <g>
>
>Thanks, Sergey.
>
>~~Bonnie

Whoa up thar!

There is actually a really good reason for this, and it is very similar to why you want to be wary of varchar fields.

SQL fully allocates space for each row on the hard drive. If your record size is 100 characters, say, SQL will write approx. 80 records within a SQL page (which is limited to 8K). Regardless of row values, the space is fully allocated. If you change a value from null to something real, the record gets written back into the same spot.

The problem with varchars is that the space is not planned for. If your 100-character record has one varchar(25) field, SQL only allocates space for a percentage of that (I dismember what, I think it is ten characters). So, if the records exist and the columns are all null for the moment, you have a lot more rows on the page. Here's the kicker: if the field gets populated, the record width (we'll assume min 10char for the moment) will expand from 85 to 100 characters, and no longer fit in the space allocated for it. SQL has to phyiscally move the record to a new page.

Now - multiply that movement by thousands of updates over the course of a day - or worse, an hour. Your performance gets shot in the head. This is just physical record storage, mind you, I believe it has an equally adverse effect on indexes as well.

Be very careful using varchar fields. The MS default width in Enterprise Mangler is 50 characters when you define a varchar field in the table designer. I would propose that you would need a pretty good business reason for ANY varchar field. They do work, and can save space, but also have a pretty heavy cost in a very active database.