database data types for SQL common statements-Space 2

System data Type:

1. Binary data type 2. Integer data type

3. Floating-point data type 4. Exact decimal data type

5. Currency data type 6. Date/Time Data type

7. Character data type 8. Unicode data type

9. Special data types

1. Binary data type:

Binary[n]: A binary number with a fixed length of n bytes.

Varbinary[n]: N bytes A variable-length binary number.

Image: A variable-length binary integer used to store more than 8KB of data. such as: Word documents, Excel charts, and image data. Maximum length of 2^31-1 characters.

2. Integer data type:

Bit: The value of the data can only be 0 or 1.

Int: 4 bytes of storage space.

BigInt: 8 bytes of storage space.

SmallInt: 2 bytes of storage space.

Tinyint: 1 bytes of storage space.

3. Floating-point data type:

Note: Currency operations generally do not use this data type and are prone to rounding errors, and we all know why.

Float: 8 bytes of storage space.

Real: 4 bytes of storage space.

4. Exact decimal Data type:

Decimal (P,s): Value range -10^38+1~10^38-1. You can specify the total number of digits p and the number of decimal digits s.

Numeric: Indicates a range of up to 38 bits. You can specify the total number of digits p and the number of decimal digits s.

Difference: The numeric type can have an identity keyword.

5. Currency data type:

Money: Accounts for 8 bytes. The precision is 19, and the decimal is 4 bits.

SmallMoney: occupies 4 bytes. The precision is 10, and the decimal is 4 bits.

6. Date/Time Data type:

DateTime: Used to store the date and time. accounts for 8 bytes.

smalldatetime: The date time range is smaller. accounts for 4 bytes.

7. Character data type:

CHAR (n): holds fixed-length characters, one byte per character, and the n value cannot exceed 8000.

VARCHAR (n): holds variable-length characters, and the n value cannot exceed 8000.

Text: A large number of variable-length characters, the maximum length of up to 2^31-1 characters.

8.Unicode Data type:

NCHAR (n): Holds fixed-length Unicode characters, and the n value cannot exceed 4000.

nvarchar (n): Holds variable-length Unicode characters, and the n value cannot exceed 4000.

ntext: Holds up to 2^30-1 of variable long Unicode characters.

9. Special Data types:

Timestamp: timestamp, which is a non-repeating binary number that is automatically generated by the database.

uniqueidentifier: Globally unique identifier. Uniquely identifies a row in multiple copies of a table

Table: can only be used to define local variables or function return values.

XML: Stores XML data.

User-defined Types

When you create a user-defined data type, you must provide a name, the system data type on which the new data type is based, and whether the data type allows null values.

Creates a user-defined data type using the Create type, in the format: Create type Type_name
{from System_type [NULL |　　Not NULL]} where: –type_name: Is the name of the user-defined data type.　　–system_type: Is the name of the system data type on which the user-defined data type is based, such as varchar, int, and so on. –null | Not NULL: Can be a null value. If the item is defaulted, it defaults to null.

database data types for SQL common statements-Space 2