char varchar
-
my question is what is the difference between char 6 and varchar(6) ? any example ?
-
Char is fixed length and will always take up 6 characters.
VarChar is variable length and will take a maximum of 6 characters.
CREATE TABLE #CharVarChar(CharField CHAR(6),VarCharField VARCHAR(6))
INSERT #CharVarChar (CharField,VarCharField) VALUES('A','A')
--Now try
SELECT '|'+charfield+'|'+varcharfield+'|' FROM #CharVarChar
You will see that the effect
-
CHAR(6) will give you 'ABC___' where _ equals a space
VARCHAR(6) will give you 'ABC' without spaces
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Something else to realise is that if you have a char(20) column and do not use all twenty characters, you are wasting disk space.
Varchar will only only use up the disk space that it requires. I only use char where I know the length of the text.
------------------------------
The Users are always right - when I'm not wrong! -
varchar and char both stores the characters(Alphanumeric) values. The difference is that if you had defined a column with char(6) then whatever you have entered will consume the 6 Byte of spaces(any how you can't enter more than 6 chars). lets suppose you had entered 'AM' which counts to 3 characters but if it is stored into char data types then it will consume 6 bytes of space. In other case if you are storing the same value in varchar(6) then the remaining 3 bytes will be realesed. thus making it optimiosed for disk space.
-
I'm not sure if you gain anything with small VARCHAR fields because the nature of a VARCHAR is that it needs to store its start and end point.
A VARCHAR(3) could use more space as a CHAR(3) because of this.
I think there is another element to storage space connected to whether or not the field is allowed NULL values.
-
Problem with CHAR is not ony disk space.
You cannot add anything to the right of CHAR value, you must RTRIM it first.
Try to do this:
declare @T TABLE ( A char (5), B varchar(5)  
insert into @T select 'A', 'B'
update @T SET A = '_'+A
update @T SET A = A + '_'
select * from @T
update @T SET B = '_'+B
update @T SET B = B + '_'
select * from @T
Second update to column A does not work:
Server: Msg 8152, Level 16, State 9, Line 8
String or binary data would be truncated.
The statement has been terminated.
You have to use update @T SET A = rtrim(A) + '_' to make it work.
Are you sure your developers always remember about this?
But Varchar has inbuilt overheads and it's reasonable to use CHAR for short fields. Of course there is no any reason to use VARCHAR(1), and you must to think if it's OK to use VARCHAR(2) or VARCHAR(3) in every particular case.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply