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
views:
53answers:
5
+1
A:
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 ]
Mitch Wheat
2010-08-17 11:28:55
+1
A:
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
2010-08-17 11:29:29
+2
A:
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
2010-08-17 11:30:59
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
2010-08-17 11:48:56
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
2010-08-17 12:53:25
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
2010-08-22 06:02:56
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
2010-08-22 10:15:43
A:
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
Dorababu
2010-08-17 11:32:53
+1
A:
If using MySQL the difference is mainly related to the storage requirements:
VARCHAR:L + 1bytes if column values require 0 – 255 bytes,L + 2bytes if values may require more than 255 bytesTEXT:L + 2bytes, whereL < 2^16
where L is the actual size of the entry
the_void
2010-08-17 11:37:39