Gotta love Mike's reply
Try the following and look at the results :
Code:
use tempdb
go
create table #abc ( a int , vc varchar(10), uvc univarchar(10), ch char(10), uch unichar(10) )
go
insert into #abc values ( 1, 'abc', 'abc', 'abc', 'abc')
go
insert into #abc values ( 2, 'abc ', 'abc ', 'abc ', 'abc ')
go
insert into #abc values ( 3, 'abcdefghijkl', 'abcdefghijkl', 'abcdefghijkl', 'abcdefghijkl')
go
select a,vc,uvc,ch,uch
from #abc
select a, 'chlen_vc'=char_length(vc), 'dtlen_vc'=datalength(vc), 'chlen_uvc'=char_length(uvc), 'dtlen_uvc'=datalength(uvc), 'chlen_ch'=char_length(ch), 'dtlen_ch'=datalength(ch), 'chlen_uch'=char_length(uch), 'dtlen_uch'=datalength(uch)
from #abc
go
-- exec sp_help #abc
-- go
select c.colid, c.type, c.length, c.usertype, 'colname'=convert(varchar(10),c.name), 'datatype'=convert(varchar(10),t.name)
from syscolumns c, systypes t
where id = object_id('#abc')
and c.usertype = t.usertype
go
drop table #abc
go
Results here :
Code:
a vc uvc ch uch
----------- -- --- -- ---
1 abc abc abc abc
2 abc abc abc abc
3 abcdefghij abcdefghij abcdefghij abcdefghij
a chlen_vc dtlen_vc chlen_uvc dtlen_uvc chlen_ch dtlen_ch chlen_uch dtlen_uch
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 3 3 6 10 10 10 20
2 3 3 3 6 10 10 10 20
3 10 10 10 20 10 10 10 20
colid type length usertype colname datatype
----------- ----------- ----------- ----------- ------- --------
1 56 4 7 a int
2 39 10 2 vc varchar
3 155 20 35 uvc univarchar
4 47 10 1 ch char
5 135 20 34 uch unichar
The vc is a varchar() column, the uvc is a univarchar() column. Both of these are "variable length" columns where the portion inside the parenthesis indicate the maximum character length. ( So varchar(255) indicates a max of 255 characters that can be contained. )
The same goes for the maximum character length of char() and unichar() columns, but they are deemed fixed-length datatypes.
For the *varchar datatypes, blanks or spaces are trimmed off the trailing end, and is also stored without the trailing spaces inside the database.
For the *char datatype, blanks are padded onto the end if the string is less than the actual maximum string size, and it's also stored in the database with the padded spaces on the end.
The unichar and univarchar types are stored as "double-byte" characters, as is indicated by the datalength() function.
If you look at the results, you'll see that char_length returns the actual length of the string that was stored. For varchar and univarchar, this means that 'abc' and 'abc ' is both stored as 'abc' in this case.
If you look at the datalength() of the two types, you will see that the varchar() uses half the storage of the univarchar(), so the univarchar is stored as double-byte characters.
The interesting thing to note is the char() & unichar() vs the varchar() & univarchar() - their character length and data length always indicate the padded spaces as well : character length and data length of 10 for char(), and character length of 10 and data length of 20 (as per the example).
Note that for row 3, the 'k' & 'l' characters were trimmed from 'abcdefghijkl' as the maximim column length for all columns was defined as 10.
dBforums
>
What are the 2 byte characters?
06-15-09, 10:37
Linear Mode
