1>
Binary data type
Binary data makes some data in hexadecimal format. Binary data types include binary, varbinary, and image.
Binary [(n)]: a fixed length of n Bytes of binary data. The maximum length is 8 KB.
Varbinary [(n)]: Variable Length of n Bytes of binary data. The maximum length cannot exceed 8 KB.
Image: used to store data with more than 8 KB of data, such Microsoft Word documents, Microsoft Excel Charts, and image data. The maximum length is-1 bytes.
2>
Integer Data Type
Integer data types include bit, Int, bigint, smallint, and tinyint.
Bit: the value of bit type data can only be 0 or 1. Usually, bit type data is used to indicate true/false logical relationships, such as on/off, YES/NO, and true/false. You cannot use indexes for bit columns.
INT: all positive and negative numbers from-231 to-231-1. Storage occupied by INT type data Space The size is 4 kb.
Bigint: all negative numbers in the range of-263 to-1 can be stored, and the storage space occupied is 8 bytes.
Smallint: stores all positive and negative numbers from-215 to-215-1, occupying 2 bytes of storage space.
Tinyint: can store 0 ~ The storage space occupied by All integers between 255 is 1 byte.
3>
Floating Point Data Type
Floating point data types include float and real, which are used to store numbers with a very large range.
Float: The data range of this type is-1.79e + 308 ~ 1.79e + 308.
Real: The data range of this type is-3.40e + 38 ~ 3.40e + 38.
The floating-point data type is prone to rounding errors, so it is generally not used in currency operations, but it is very convenient to use the floating-point data type in scientific operations or Statistical Computation operations that do not require absolute precision.
4>
Precise small tree data types
Exact decimal data types include decimal and numeric. You can specify the total digits on both sides of the decimal point and the digits on the right of the decimal point. The range of the two data types is from-1038-1 to 1038-1.
The difference between decimal and numeric is that columns of the numeric type can contain the identity keyword.
5>
Currency Data Type
The currency data type is specially used for currency data processing, including money and smallmoney.
Money: the range of currency values stored in the money data type ranges from-263 to 263-1, accurate to 10 ‰ of the currency unit ‰. The money data type must consist of two 2-byte integers. The first 4 bytes represent the integer part of the currency value, and the last 4 bytes represent the decimal part of the currency value.
Smallmoney: the currency value stored in the smallmoney data type is between-214748.3648 and 214748.3647, accurate to 10‰ of the currency unit ‰. The smallmoney data type must consist of two 2-byte integers. The first two bytes represent the integer part of the currency value, and the last two bytes represent the decimal part of the currency value.
6>
Date
/
Time Data Type
The date/time data type can store the combined data of date and time, including datetime and smalldatetime.
Datetime: stores the date and time data from January 1, 3.33 to 0.00333s or S. Each datetime data occupies 8 bytes of storage space.
Smalldatetime: the date and time data stored from January 1, January 1-20, 1900 to January 1, December 31 can be accurate to minutes. Each smalldatetime data occupies 4 bytes of storage space.
7>
Character Data Type
Character data types are composed of letters, numbers, and symbols. For example, 'beijing' and 'zyf123 @ 126.com 'are valid character data. Character data types can be divided into three types:
Char [(n)]: uses a fixed length to store characters. Each character occupies one byte of storage space, and its maximum length is 8000 characters. When using the char data type to define columns or variables, the maximum length of the data should be given. For example, declare @ name char (10) is defined ).
The variable name is of the char type and can contain up to 10 characters. If the length of the character actually assigned to the variable is shorter than the given maximum length, extra characters are filled with spaces. If the length of the character actually assigned to the variable exceeds the given maximum length, the characters exceeding the limit are truncated. Constant must be enclosed in single quotes.
Varchar [(n)]: used to store variable-length structured data that can contain up to 8000 characters. The storage space of the varchar data type varies according to the character numbers of each data stored.
Text: used to store a large number of variable-length character data. The maximum length is-1.
8> Unicode
Data Type
Unicode data types are used to store dubyte characters that can be stored with two characters. For example, Unicode data types such as man, Japanese, and Korean include nchar, nvarchar, and ntext.
Nchar (n): Unicode data of a fixed length. N in brackets is used to define the maximum length of character data. The value range of N is 1 ~ 4000. because both are stored in double-byte characters, the storage space of Unicode data is: the storage space of Unicode DATA = the number of characters * 2 (bytes ).
Nvarchar (n): used to store variable-length Unicode data. The N in parentheses is used to define the maximum length of character data. The value range of N is 1 ~ 4000.
Ntext: used to store Unicode data with a variable length. The maximum length of ntext breaks through the rules of the first two types of data and can store up to-1 Unicode data.
9> SQL _variant
Data Type
The SQL _variant data type can be applied to columns, parameters, variables, and function return values. Data of the SQL _variant data type can store data other than the text, ntext, image, and SQL _variant data types.
10>User-Defined Data Type
1.Create Custom Data Types
Example1
Create two custom data types. The first Custom Data TypeTelephone_code, The Data Length is15, Variable-length struct type, can be empty; the second Custom Data TypeZip, Fixed length:6, Cannot be empty.
Use sales
Go
Exec sp_addtype telephone_code, 'varchar (15) ', 'null'
Exec sp_addtype zip, 'Char (6) ', 'not null'
2.View User-Defined Data Types
Use stored proceduresSp_helpYou can view the features of user-defined data types.
Example2
ViewTelephone_code.
Exec sp_help telephone_code
3.Delete user-defined data types
Use stored proceduresSp_droptypeDelete a custom data type.
Example3
Delete the data type defined in lifecycleTelephone_code.
Exec sp_droptype telephone_code
Note: Only defined but not defined can be deleted The user-defined data type that is being used by tables or other database objects cannot be deleted.
