March 22, 2005 at 10:47 am
Can someone explain Data Type lengths to me?
From what I can tell the meaning of the word Length depends on the actual datatype being used. Is this correct?
For instance if I use a VARCHAR of length 200 it literally means 200 characters.
If I use an INT of Length 4 does this mean a maximum range of 0 to 9999, -9999 to 9999, or something else?
If I have a TEXT of Length 16 it seems to mean 16Bit or 65,536 characters. Is this correct?
It's quite confusing because having moved from Access to SQL Server (via MySQL) I seem to think that Access dealt mainly in Bits rather than specific field sizes.
Thanks in advance,
Chris
March 22, 2005 at 11:56 am
Read "Data Types" in SQL BOL
But length means the number of bytes a datatype will take to store in the database with the excetpion of unicode types (NCHAR, NVARCHAR, etc) you have to double those as they take two bytes for each character.
And then there are BLOB tpyes (Binary Large OBbjects) like text and ntext they use a pointer to the page where the first of the data is stored this is 16 bytes in length itself.
So to store the following items
AND
THE
CAT
You would choose a type of char(3) which uses 3 bytes but for numbers
123
199
255
You would choose tinyint which takes 1 byte (remember numbers can be stored in hexdecimal format so that 00 = 0 and FF = 255, each hex pair represents 1 byte which is made of 8 bits).
Lot more you could know but this is basic.
March 23, 2005 at 12:21 am
If I have a TEXT of Length 16 it seems to mean 16Bit or 65,536 characters. Is this correct?
Like Antares already said, read BOL.
...and forget the 16 you see in Enterprise Manager for a BLOB column. This is just the BLOB root pointer. Completely irrelevant. Each BLOB column can store up to 2 GB. Specially for BLOBs I always like to post this link http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx as I think this is the most comprehensive discussion on BLOBs and SQL Server I have found so far.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply