what is the diff between TEXT and VARCHAR datafields?

Question

I have following confusions -
1. Which datatype is used for bigger amount of data store
2. When we specify varcha(100) so this 100 represent 100 characters or 100 bits or bytes

Answer 1

On SQL Server

varchar(100) = 100 ascii characters and 102 bytes

nvarchar(100) = 100 unicode/ascii characters and 202 bytes of storage

text and ntext go up to 2GB but are deprecated (from sql server 2005), you should use varchar(max) and nvarchar(max) which both also go up to 2 GB instead

Answer 2

TEXT will store up to 64K - MySQL (for example) provides variants as well: TINYTEXT (256 bytes), MEDIUMTEXT (16MB) and LONGTEXT (4GB).

A VARCHAR(100) column will hold up to 100 characters of text. You can use a VARBINARY for binary data, in which case a VARBINARY(100) field will hold up to 100 bytes.