Good morning.

Using SQL Server 2000. It appears that SQL Server char fields are padded with spaces when they are saved in entities. For example, FirstName char(20) = "Jeff" is saved as "Jeff ". This does not occur with varchar types.

Is this a problem with LLBLGenPro? Should LLGLGenPro TRIM string fields before saving? It's not really a big problem because I could easily use only varchar types, but it may be a bug that you want to know about, Frans.

Thanks.

Jeff

Oops! Tiny forum doesn't like padded strings itself! That should read "saved as "Jeff" with 16 spaces following the second "f".

Jeff

That's not a bug. That's the way that the char datatype works.

You define a column as char(20) and whatever what you put in that column will be 20 characters in length. The datatype varchar is variable character and will be trimmed.

Thanks for the answer, Chris. Indeed, that's how CHAR fields work: they are padded with spaces when they're read from the DB. I leave these spaces in the fields because you might want them there.

In the past I've lost several days of debugging time when fighting this behavior, in the time when I didn't know that much about sqlserver and the difference between char and varchar

So which is better to use char or varchar? I too have had this issue since I choose to use char but I now trim everything. Any advantages or disadvantages to varchar?

Database geeks will say "CHAR!" as varchar requires dynamic allocation, but in the long run, varchars are better, as you don't have to write tedious client-side code which indeed trims the crap out of every field used and worse: if you forget a field, you insert bugs in the code as "FOO" is not the same as "FOO "... (been there... )

I kind of consider myself to be a db geek (or just a geek in general) but I say it depends.

If the average # of bytes used is > 85% of the field length then make it char to help performance, and youre going to use the disk anyway.

But on the other hand, it makes no sense to allocate X bytes of data for each record where the average # bytes used is only 10% of the max. Thats just a pure waste of disk space and performance is not helped.

Every nanaosecond counts (yeah im a geek)

Yes, It I agree, it depends is the right answer.

As I understand, the problem with varchar fields is that updates to a varchar field that increase the number of characters force the database to reorganize and reindex the whole table. Varchar will only store as many bytes as needed, while char stores all the bytes declared in the field. That's why the string gets padded: the db had to insert whitespace to keep up with the char field length.

Example: Insert "lala" in a varchar(256) field. The db will actually use 4 bytes for this, not 256. Then update this field with "lala2". Now the db has to resize and reindex. If the update had fewer characters than the original (i.e. "la"), no resizing is done.

This can be a big big problem if it happens in a table with a lot of data. That is why we tend to use char (well, nchar actually) instead of varchar for big tables. But, the trimming headache is there, no question about that.

I think it would be neat to have an option in LLBLGenPro to automatically trim certain fields. You could declare in the designer that you want LLBLGenPro to trim some field when you read it which would save us the headache. How's that?

cheers alvaro.-

I didn't know it re-indexes everything, which seems a bit odd, as indexes work on values, not bytes. (but this might differ among different database flavors). I also wouldn't be surprised if sqlserver sizes on row, not table, as it is a varchar. This means that each physical record has at the beginning a set of offsets where the various fields begin in the byte array which forms the record (max 2 mempages). So if you insert a new value, which is longer, it has to resize the record, but not the table. This can however cause disk trashing as a big difference in value size can require that physical data has to be moved internally because basic overflow buffers are maxed out and a clustered index on a PK (which is the default for sqlserver) requires a physical order on disk, which means you can't simply move the now much bigger record to the back of the table 'file'.

Do you have links which go more into details about this? (As I don't have any info on this besides mr. Date's book ) so I'm curious to know how it is solved in sqlserver.