8

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

nectar
  • 9,525
  • 36
  • 78
  • 100

5 Answers5

4

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • The storage requirements are not 100 bytes for the `VARCHAR(100)`, but 102 bytes: http://msdn.microsoft.com/en-us/library/ms176089.aspx. Same goes for `NVARCHAR`. – the_void Aug 17 '10 at 11:48
  • Yes I know 2 bytes are used to denote start and end position, this is also why you use char(2) not varchar(2). Thanks, updated the answer – SQLMenace Aug 17 '10 at 12:53
  • Actually no, the 2 bytes are used to store the size of the `VARCHAR` array and not as a start-end position. And you don't use `CHAR` rather than `VARCHAR` if the columns differ significantly in size, as this will be a waste of space compared to `VARCHAR`. – the_void Aug 22 '10 at 06:02
  • If you have more than 1 varchar column then there is also a 2-byte overhead per row to maintain the count of variable columns. I gave a char(2) vs varchar(2) example not char(50) vs varchar(50) because of course in that case if you only use 25 bytes you would want to use varchar – SQLMenace Aug 22 '10 at 10:15
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.

David Knell
  • 745
  • 4
  • 6
2

If using MySQL the difference is mainly related to the storage requirements:

  • VARCHAR: L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes

  • TEXT: L + 2 bytes, where L < 2^16

where L is the actual size of the entry

the_void
  • 5,512
  • 2
  • 28
  • 34
1

Assuming you are talking about MS SQL Server:

1) varchar(max): varchar(max) is a replacement for TEXT

2) varchar(100) = 100 characters (single byte ascii chars)

(whereas nvarchar(100) = 100 characters (double byte unicode chars))

Ref: char and varchar (Transact-SQL)

[Just found a possible duplicate: ms sql server; varchar(MAX) vs TEXT ]

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
0

The datatype TEXT is limited to 4000 characters where in the new data type Varchar(max) is meant to store Maximum Length of characters or the length that you specified

For more information check this

http://databases.about.com/od/sqlserver/a/char_string.htm

Developer
  • 8,390
  • 41
  • 129
  • 238