SQL Field Type

Text and image are 16 bytes in length and cannot be changed. during storage, the content is stored on the hard disk. The content address and content size are recorded in the table. Therefore, image and text cannot be indexed. Field Type
Different field types are used to store different types of data. When creating and using tables, you should understand five common field types: numeric, text, numeric, logic, and date.

Balanced data
Balanced data is very useful. When you need to store short string information, you always need to use string data. For example, you can put the information collected from the text box of HTML form in the character field.
To create a field to store variable-length string information, you can use the expression varchar. Consider the table guestbook you created earlier:
Create Table guestbook (visitor varchar (40), comments text, entrydate
Datetime)
In this example, the Data Type of the field visitor is varchar. Note the numbers in parentheses following the data type. This number specifies the maximum length of the string that this field can store. In this example, the visitor field can store a string of up to 40 characters. If the name is too long, the string is truncated and only 40 characters are retained.
A string of the varchar type can contain a maximum of 255 characters. To store longer string data, you can use text data (described in the next section ).
Another type of character data is used to store character data with a fixed length. The following is an example of using this data type:
Create Table guestbook (visitor char (40), comments text, entrydate
Datetime)
In this example, the field visitor is used to store fixed-length strings of 40 characters. The expression char specifies that this field should be a fixed-length string.
The differences between varchar and char data are subtle, but important. 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 Data
Character data limits the length of a string to no more than 255 characters. Text data allows you to store strings with more than 2 billion characters. When you need to store large strings of characters, you should use text-type data.
Here is an example of using text data:
Create Table guestbook (visitor varchar (40), comments text, entrydate
Datetime)
In this example, the field comments is used to store users' opinions on your site. 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.
Warning:
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 data
SQL Sever supports many different types of numeric data. You can store integers, decimals, and money.
Generally, when you need to store numbers in a table, you need to use INTEGER (INT) data. The number of int Data Tables ranges from-2,147,483,647 to 2,147,483,647. The following is an example of how to use int data:
Create Table visitlog (visitor varchar (40), numvisits INT)
This table can be used to record the number of visits to your site. As long as no one visits your site more than 2,147,483,647 times, the nubvisits field can store the number of visits.
To save memory space, you can use smallint data. Smallint data can store integers from-32768 to 32768. The usage of this data type is exactly the same as that of int type.
Finally, if you really need to save space, you can use tinyint data. Similarly, the use of this type is the same as that of the int type. The difference is that this type of field can only store integers from 0 to 255. Tinyint fields cannot be used to 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.
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. Here is an example of using this data type:
Create Table numeric_data (bignumber numeric (28, 0 ),
Fraction numeric (5, 4 ))
When this statement is executed, a table named numeric_data contains two fields will be created. The bignumber field can be a 28-digit integer. The fraction field can store decimals with five integers and four decimals.
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.
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. The following example shows how to use these two data types to represent money:
Create Table products (product varchar (40), price money,
Discount_price smallmoney)
This table can be used to store discounts and general prices of products. The data type of the field price is money, and the Data Type of the field discount_price is smallmoney.

Storage logic Value
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. Here is an example of how to use this field:
Create Table opinion (visitor varchar (40), good bit)
This table can be used to store information obtained from public opinion surveys on your sites. Visitors can vote to indicate whether they like your outlets. If they cast Yes, 1 is saved to the bit field. Otherwise, if they cast no, they store 0 in the field (in the next chapter, you will learn how to calculate the vote ).
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.

Storage Date and Time
When creating a network, you may need to record the number of visitors within a period of time. To store date and time, you need to use datetime data, as shown in the following example:
Create tabl visitorlog (visitor varchar (40), arrivaltime datetime,
Departuretime datetime)
This table can be used to record the time and date when visitors enter and exit your site. 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. It is important to understand this. In the next chapter, you will learn how to use a large number of SQL functions to read and operate on dates and times (see the "default" section below ). You can also use the date and time functions in VBScript and JScript to input a date and time to a datetime field.