In this document, we will briefly describe five data types in SQL.
1. Character
VARCHAR VS CHAR
The difference between VARCHAR and CHAR data is subtle but very important. Both of them are used to store characters, of which the length is less than 255. (the maximum length of VARCHAR type in the SQL server to 8,000, and oracle to 4,000). If you input data Bill Gates to a VARCHAR field, of which the length is 40 characters, when you take out this data from the field afterwards, the length of the data is 10 characters – the length of the string Bill Gates. However, if you input the string to a CHAR field, of which the length is 40 characters, when you take out the data, its length will be 40 characters. And the string will be attached to extra space. When you build your own site, you will find it more convenient to use VARCHAR than CHAR field. As you need not to worry about cutting the extra space in your data while using VARCHAR field. Moreover, VARCHAR type field takes up less memory and hard disk space then CHAR. However, CHAR field is better than VARCHAR in the efficiency of reading. For the field of which the length of the characters has been limited, CHAR can read faster, better than VARCHAR.
2. Test (SQL Server)
With text data, you can store strings with more than two billion characters. When storing large characters, you should use text data. Different from character data, text data has no length. Usually, the data in a text field is either empty or great. When collecting data from multiple lines of text edit box (TEXTAREA) of HTML FORM, you should store the information collected in the text field. However, whenever, do not use text field as long as you can avoid, as text field is big and slow, abusing it will make the server slow down. What’s more, text field will take up much disk space. Once you have entered any data (or even null) to text field, there will be a 2K space assigned to the data automatically. And you cannot recover this storage space unless deleting the record.
3. Numeric
SQL supports many different types of numeric data. You can store integers INT, decimals NUMERIC, and the amount of money MONEY.
INT VS SMALLINT VS TINYINT
The difference among them is just the length of characters:
INT data shows the range from integers -2,147,483,647 to 2,147,483,647
SMALLINT data can store integers from -32768 to 32768
TINYINT field can store integers only from 0 to 255, and cannot be used to store negatives
MUMERIC
In order to have more control over data stored in the field, you can use NUMERIC data to express the integer and fractional parts of a number. NUMERIC data allows you to express very large numbers – much greater than INT data. A NUMERIC field can store number from -1038 to 1038. It also can represent the fractional part of number. When defining a NUMERIC field, you need to specify the size of fractional part and integer part.
MONEY VS SMALLMONEY
MONEY data can store the amount of money from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, while SMALLMONEY data only from --214,748.3648 to 214,748.3647.
4. Logical
If you use a check box to collect information from the web, you can store this information in BIT field, which can only take two values: 0 or 1.
5. Date
DATETIME VS SMALLDATETIME (SQL Server)
A data range of DATATIME field can be stored from the first millisecond of January 1, 1753 to the last millisecond of December 31, 9999. And a SMALLDATETIME field can store the date from January 1, 1900 to June 6, 2079, and it can only be accurate to the second. The date and time range of SMALLDATETIME data is smaller than DATETIME data, and not as accurate as it. DATETIME field uses 8 bytes to store date and time values, while SMALLDATETMIE field only using 4 bytes to store date and time accurate to 1 minute. An important point we need to know is that DATETIME field does not include actual data before you enter data and time.
