Five data types in SQL: numeric, text, numeric, logical, and date

This section briefly describes five data types in SQL: numeric, text, numeric, logical, and date.

Character Type

Varchar and char

The differences between varchar and char data are subtle, but important. They are used to store strings of less than 255 characters.

Assume that you enter the data Bill Gates in a 40-character varchar field. When you extract this data from this field, the length of the data you extract is 10 characters-the length of the string Bill Gates. Now, if you input a string into a char field with a length of 40 characters, the length of the retrieved data will be 40 characters. Extra spaces will be appended to the string.

When you create your own site, you will find it easier to use varchar fields than char fields. When using varchar fields, you do not need to worry about cutting out unnecessary spaces in your data.

Another outstanding advantage of the varchar field is that it can occupy less memory and hard disk space than the char field. When your database is large, this memory and disk space savings will become very important.

Text Type

Text

You can use text data to store strings with more than 2 billion characters. When you need to store large strings of characters, you should use text-type data.

Note that text data has no length, but the plain data mentioned in the previous section has a length. Data in a text field is usually either empty or large.

When you collect data from the HTML form multi-line text editing box (textarea), you should store the collected information in text fields. However, whenever you avoid using a text field, you should not apply it. Text fields are both large and slow. Misuse of text fields slows down the server. Text fields also consume a large amount of disk space.

Once you input any data (or even null values) to a text field, 2 K space is automatically allocated to the data. You cannot reclaim this bucket unless you delete this record.

Numeric type

SQL supports many different numeric data types. You can store integer int, decimal numeric, and money.

Int vs smallint vs tinyint
Their difference is only the character length: the number range of int-type data tables is an integer from-2,147,483,647 to 2,147,483,647. smallint-type data can store tinyint-type fields from-32768 to 32768 only integers ranging from 0 to 255, cannot store negative numbers
Generally, the smallest integer data should be used as much as possible to save space. A tinyint data occupies only one byte, and an int data occupies four bytes. This seems to be a little different, but in a relatively large table, the number of bytes increases rapidly. On the other hand, once you have created a field, it is very difficult to modify it. Therefore, to ensure security, you should predict the maximum value that a field needs to store, and then select the appropriate data type.

Muneric

In order to have more control over the data stored in a field, you can use numeric data to simultaneously represent the integer and decimal parts of a number. Numeric data enables you to represent a very large number-much larger than int data. A numeric field can store numbers from-1038 to 1038. Numeric data also enables you to represent the number of fractional parts. For example, you can store a decimal number of 3.14 in a numeric field.

When defining a numeric field, you must specify the size of the integer part and the decimal part at the same time. For example: muneric)

The integer part of a numeric data can only have a maximum of 28 digits. the digits in the decimal part must be smaller than or equal to the digits in the integer part, and the decimal part can be zero.

Money vs smallmoney

You can use Int or numeric data to store money. However, there are two other data types dedicated for this purpose. If you want your outlets to earn a lot of money, you can use money-type data. If you are not ambitious, you can use smallmoney data. Money data can store money from-922,337,203,685,477.5808 to 922,337,203,685,477.5807. If you need to store a larger amount than this, you can use numeric data.

Smallmoney data can only store the amount of money from-214,748.3648 to 214,748.3647. Similarly, if possible, you should use the smallmoney type to replace the money type data to save space.

Logical type

Bit

If you use the check box to collect information from a webpage, you can store this information in the bit field. A bit field can have only two values: 0 or 1.

Note: after creating a table, you cannot add a bit field to the table. If you want to include a bit field in a table, you must complete it when creating the table.

Date type

Datetime vs smalldatetime

A datetime field can store the date range from the first millisecond of January 1, January 1, 1753 to the last millisecond of January 1, December 31, 9999.

If you do not need to cover such a large range of dates and times, you can use smalldatetime data. It is used in the same way as datetime data, except that it can represent a smaller date and time range than datetime data, and is not as precise as datetime data. A smalldatetime field can be stored from January 1, January 1-20, 1900 to January 1, June 6. It can only be accurate to seconds.

Datetime fields do not contain actual data before you enter the date and time.