In SQL Server, for example, there are 33 types of data for SQL Server, which are summarized in categories:
| Exact numbers |
Unicode string |
| Approximate numbers |
Binary string |
| Date and time |
Other data types |
| String |
|
1. Numeric data type 1.1 integer data type
| Data category |
Range |
Store |
| bigint |
-2 of 63 square to 2 63 square-1 |
8 bytes |
| Int |
-2 of 31 square to 2 31 square-1 |
4 bytes |
| smallint |
-2 of 15 square to 2 15 square-1 |
2 bytes |
| tinyint |
0~255 |
1 bytes |
The int data type is the primary integer data type, which exceeds the storage range of int and is stored with bigint. The function returns BIGINT,SQL server does not automatically promote other integer data types to bigint when the argument expression is of type bigint. 1.2 Decimal data types decimal and numeric are numeric data types with fixed precision and decimal places. Its syntax is decimal "(p", S ")" and numeric "(p", S ")" (p: precision, which is a numeric value in the 1~38 range, the default is 18,s: the number of decimal digits, must be a value between 0~p, the default scale is 0, and the storage size varies based on precision). Fixed precision and number of decimal digits. When using the maximum precision, the valid values from 10 to 38 +1 to 10 of the 38-square -1.numeric are functionally equivalent to decimal. 1.3 Currency types have special money and smallmoney two, both of which are accurate to one out of 10,000 of the currency units they represent.
| Data type |
Range |
Storage size |
| Money |
-2 of 63 square to 2 63 square-1 |
8 bytes |
| SmallMoney |
-2 of 31 square to 2 31 square-1 |
4 bytes |
1.4 Approximate numbersThe two data types, float and real, are used to represent the approximate data type of floating-point numeric data, and the floating-point data is approximate. As a result, not all values within all data ranges can be accurately represented, so they are called approximate numbers.
| Data type |
Range |
Storage size |
| Float |
-1.79e+308~-2.23e-308 and 2.23e-308~1.79e+308 |
Depending on the value of N, for example, float (50) |
| Real |
-3.40e+38~1.18e-38, 0 and 1.18e-38~3.40e+38 |
4 bytes |
The float type syntax is: float "(n)". n is the number of bits (expressed in scientific notation) used to store the mantissa of the float value. Therefore, you can determine the accuracy and storage size. n should be a number between 1~53, and the default value for n is.when the value range for n is 1~24, the precision is 7 digits and the storage size is 4 bytes. When the value range for n is 25~53, the precision is 15 digits and the storage size is 8 bytes. the ISO synonym for real is float (in). 1.5bit Type The bit type has a value range of 1, 0, or null for integer data types. The storage of the bit column varies depending on the storage size.
A string value of true and false can be converted to the following bit value, and true is converted to 1,false to 0. 2. Character data type There are 6 kinds of character data types, and they are divided into two types: string and Unicode string.string includes char, varchar, text;
The Unicode string contains nchar, nvarchar, ntext.
N "string" means that the string is a Unicode string. Unicode constants are interpreted as Unicode data and do not apply to code pages for evaluation. Unicode constants do have collations that are primarily used to control comparisons and to differentiate between sizes. each character in the Unicode data is stored using two bytes, while ordinary character data is stored using a single byte. Char is a fixed-length string, and varchar is a variable-length string. However, whether it is a fixed length or a variable length string, there should be a capacity. Char "(n)" fixed length, n bytes in length, and n range is 1~8000. Instead of the varchar "(n|max)" variable length, the value range of n is 1~8000.max indicates that the maximum storage size is 231-1 bytes. The storage size is the actual length of the input data plus 2 bytes, so the length of the input data can be 0 characters. Note:1. If the site supports multiple languages, consider using the Unicode nchar or nvarchar data type to maximize the elimination of character conversion issues. If you use char or varchar, it is recommended that a, if the size of the column data item is the same, use Char;b, if the column data item size difference is quite large, then use Varchar,c, if the column data item size differs greatly, and the size may exceed 8000 bytes, The varchar (max) is used. 2. When the Collation code page uses double-byte characters, the storage size is still n bytes, then the stored characters will be divided by 2, for example, varchar (10) can only put 5 characters. 3. If you are storing Chinese characters, it is best to use a Unicode string type. 4. If the amount of data is large, it is not recommended to use the text or nchar type, but instead use varchar (max) or nvarchar (max).5. If the size of the column data items is always different, use varchar, because the fixed length is the space padding if the specified number of digits is insufficient. 3. Date and time data type
| Data type |
Format |
Range |
Accuracy |
Storage size |
| Time |
HH:MM:SS[.NNNNNNN] |
00:00:00.0000000~23:59:59.9999999 |
100 nanoseconds |
bytes |
| Date |
Yyyy-mm-dd |
0001-01-01~9999-12-31 |
1 days |
3 bytes |
| smalldatetime |
YYYY-MM-DD Hh:mm:ss |
1900-01-01~2079-06-06 |
1 minutes |
4 bytes |
| Datetime |
YYYY-MM-DD HH:MM:SS[.NNN] |
1754-01-01~9999-12-31 |
0.00333 seconds |
8 bytes |
| DateTime2 |
YYYY-MM-DD HH:MM:SS[.NNNNNNN] |
0001-01-01 00:00:00.000000~9999-12-31
23:59:59.9999999 |
100 nanoseconds |
6~8 bytes |
| DateTimeOffset |
YYYY-MM-DD HH:MM:SS[.NNNNNNN]
[+|-]hh:mm |
0001-01-01 00:00:00.000000~9999-12-31
23:59:59.9999999 |
100 nanoseconds |
8~19 bytes |
4. Binary data type Binary data types provide the possibility of storing files in a database. In daily programming, you can convert files to binary and then into databases, slices, files, and so on. But this is not a recommended usage. It is best to use varbinary (max) instead of the image data type. Binary "(n)": fixed-length binary data type,The value range for n is 1~8000, and the storage size is n bytes. ；varbinary "(N|max)": variable-length binary data with a length of n bytes, and the value range of n is 1~8000. Max has a maximum storage size of 231-1 bytes. The storage size is: The actual length of the input data + 2 bytes. So the length of the input data can be 0 bytes. if n is not specified in a data definition or variable declaration statement, the default length is 1. If n is not specified with the CAST function, the default length is.5. Other data Types cursor: This is a data type for a variable or stored procedure output parameter that contains a reference to the cursor. Variables created with the cursor data type can be empty. You cannot use the cursor data type for columns in the CREATE TABLE statement. timestamp: The sequence used to represent the SQL Server activity, expressed in binary format. Data is not related to inserting data or date and time. Timestamp is the line version. However, it is not recommended to use timestamp syntax in SQL Server. uniqueidentifier: Consists of 16-byte hexadecimal digits that represent globally unique. It is useful when a table's record row requires uniqueness. Many times this type is used as the data primary key, because it is basically not duplicated. HierarchyID: is a variable-length system data type. You can use HierarchyID to represent locations in a hierarchy. Sql_variant: Various data types that support SQL Server support (excluding text, ntext, image, timestamp, sql_variant, and so on). Can be in the return values of columns, parameters, variables, and user-defined functions. Enables these database objects to support values for other data types.
Talk about the data type of SQL Server
