MDSE char/varchar question

[bri189a]

As a few of you know I've always done Access prior to my recent arrival into the MSDE world. When you use char, or memo with access, and then put that a record into that field and later pull it back out you just get the text, not the trailing spaces that would be caused by say your field was a lenght of 50, but your string you put in was only 10. I've noticed with MSDE, there is no memo field, but you can use char or varchar and set the length to a rather large amount. However the downside seems to be that if you set that the column size is 1024, when you pull out your 100 character item, your going to get 924 blank spaces behind it... I'm learning DataList now to... so this of coarse distorts how that looks. Obviously I'm doing something wrong... I can manually trim myself to elivate the problem, but I know there's something I'm not doing right with MSDE, and after filling a dataset, going back and triming each row would be a horrible waste. Thanks for the help!

 

Brian

[Robby]

There are Fixed-Length and Variable-Length, have a look at this chart http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp

 

You can use ntext in place of the Access Memo

[Nerseus]

The short version:

 

char ALWAYS has spaces at the end, to pad the full length. So a char(10) with the text "hi" will actually be "hi " when you get it out. You would HAVE to trim in the DataSet to remove the trailing spaces.

 

varchar has whatever you put in it. If you define it as varchar(10) and put in "hi", you'll get out "hi". If you put in "hi " you'll get out "hi ".

 

text holds a HUGE amount of data but has searching/filtering limitations. You can't do a lot of common string functions on text fields.

 

SQL Server/MSDE also has other options you can set. One of them will always trim trailing spaces. So if you tried putting "hi " into a column it would trim to "hi". I can't remember if that's at the column or database level though - you'll have to check the help.

 

-nerseus

[EricDotNet]

As I know,

 

For Char(10) - fix length

when u put "Hi" in database, the physical stored in database is "Hi________". (8 spaces in here)

It use 10 bytes.

 

For varchar(10) - variable length

when u put "Hi" in database, the physical stored in database is "Hi" with end char.

It use 2 + 1 bytes.

[Nerseus]

Actually, I think varchar stores the string "hi" as 2 + 4 bytes, not 2 + 1. It uses 4 bytes for the "pointer" as I recall. Not that big usually. Unless I have a "state code" (for the US) that I KNOW is only 2 chars, I rarely use char for much. The savings just isn't worth it in most cases.

 

Even a Vehicle's VIN, which is normally 17, is subject to change based on vehicle type (at least for the one's I've seen). So even something as "standard" as that can have enough variation that using varchar has it's programming/maintenance advantages.

 

Personally, I think char made more sense when disk space cost more. Unless you're storing hundreds of gig of data or approaching terabyte sizes, I don't think there's much use for char for *most* applications/tables.

 

-ner

[PlausiblyDamp]

char can be quicker on heavily updated tales though - SQL will always have the correct ammount of space allocated. Using varchar it is possible that if you update a field to a longer string then it will have to turn the update into a delete/insert behind the scenes.

Although not a major issue this can result in unwanted page splits in indexes etc.