SQL basics T-SQL Data Type

The main learning T-SQL data type: numeric data type, numeric data type, temporary data type, hybrid data type, with vardecimal decimal storage type.

1. Numeric data type

The numeric data type is used to represent the data. See the following table for details:

Data Type	Detailed description
INTEGER	Indicates the integer that can be saved in four bytes. The value range is-2,147,483,648 (-2 ~ 31) to 2,147,483,647 (2 ~ 31-1 ). INT is the abbreviation of INTEGER.
SMALLINT	Indicates the integer that can be saved in 2 bytes, ranging from-32768 (-2 ~ 16) to 32767 (2 ~ 16-1 ).
TINYINT	Indicates an integer greater than zero that can be saved in 1 byte. The value range is 0 to 255.
BIGINT	Indicates the integer that can be saved in 8 bytes. Range:-2 ~ 63 to 2 ~ 63-1
DECIMAL (p, [s])	The description is straight. P (precision) specifies the number of digits and the assumed decimal point s (scale) number. The DECIMAL value can be saved in 5-17 bytes, and the number of bytes is determined by the P value. DEC is the abbreviation of DECIMAL.
NUMERIC (p, [s])	Same as DECIMAL.
REAL	Applicable to floating point values. Including positive and negative values. The positive value ranges from 2.23E-308 to 1 .. 79E + 308, and the negative value is-1.18E-38 to-1.18E + 38.
FLOAT [(p)]	Indicates a floating point value, such as REAL. P defines the precision, that is, p <25 represents the single precision (4 bytes), p> = 25 is double precision (8 bytes ).
MONEY	Indicates the currency value. The MONEY value is the same as the 8-byte DECIMAL value. All are four digits after the decimal point.
SMALLMONEY	The data type is the same as that of MONEY. It is only stored in 4 bytes.

 

2. Balanced Data Type

The primary data type is in two common forms. They can be single-byte strings and Unicode strings. In addition, the string has a fixed or variable length. See the following table for details:

Data Type	Description
CHAR [(n)]	Represents a single byte, with a fixed length of characters, where n is the number of characters in the string. The maximum value of n is 8000. If n is omitted, the string length is assumed to be 1.
VARCHAR [(n)]	A variable-length string of a single byte (0 <n <= 8000 ). In contrast to the CHAR data type, the VARCHAR data value can be saved in its actual length.
NCHAR [(n)]	Saves Unicode characters of a fixed length. The main difference between the CHAR and NCHAR data types is that each character of the NCHAR data type can be saved in two bytes. Each character of the CHAR data type only uses one byte of storage space. Therefore, the number of characters in the NCHAR column is up to 4000.
NVARCHAR [(n)]	Saves Unicode strings with variable lengths. The main difference between the VARCHAR and NVARCHAR data types is that each NVARCHAR character can be saved in two bytes. VARCHAR only uses one byte of storage space. The maximum number of characters in the NVARCHAR column can be 4000.

 

Note: The VARCHAR data type is basically the same as the CHAR data type. There is only one difference. If a CHAR (n) string is shorter than n characters, then the other strings are filled with spaces. VARCHAR data types are often stored with data lengths.

 

3. Temporary data type

Temporary data types supported by the T-SQL are DATETIME, SMALLDATETIME, DATE, TIME, DATETIME2, and DETETINEOFFSET.

DATETIME and SMALLDATETIME data types specify the date and time. Each value is an integer and is saved in four or two bytes. The values of DATETIME and SMALLDATETIME are saved as two separate values. The DATETIME value range is 01/01/1900 to 12/31/9999. The value of SMALLDATETIME is 01/01/1900 to 06/06/2079. The time part can be saved in four or two bytes. It indicates the second (DATETIME) or minute (SMALLDATETIME) after midnight ).

If you only want to save the date or time, it is inconvenient to use DATETIME and SMALLDATETIME. SQL SERVER 2008 adds a new DATA type: DATE and TIME, which can only save the DATA or TIME of DATATIME DATA separately. The DATE data type is saved in 3 bytes and ranges from 01/01/0001 to 12/31/9999. The TIME data type is saved in 3-5 bytes and accurate to ns.

DATETIME2 is also a new data type used to store Date and Time data with high precision. This type of data can be defined as variable length as required. The storage size is 6-8 bytes. The precision of the time part is 100ns.

 

4. Hybrid Data Types

T-SQL's hybrid data types include: Binary, BIT, big object data type, CURSOR, UNIQUEIDENTIFIER, SQL _VARTANT, TABLE, XML, Spatial, HIERARCHYID, TIMESTAMP and custom data type.

4.1 BINARY and BIT

BINARY and VARBINARY data types are BINARY data types. They represent data objects in the system's internal format. They are used to store bits, so these values are hexadecimal numbers.

Data Type	Detailed description
BINARY [(n)]	Specify a bit string with a fixed length and n Bytes (0 <n <= 8000)
VARBINARY [(n)]	Specify a variable length for the string. The maximum value is n Bytes (0 <n <= 8000)
BIT	It is used to specify the Boolean data type. It contains three values: TRUE, FALSE, and NULL.

4.2 big Object Data Types

A large object is a data object with a maximum length of 2 GB. These data objects are generally used to store large text data, Loading modules, and audio/video files. T-SQL supports two different ways of specifying and accessing: using the Data Types of VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX); using the so-called text and image data types.

TEXT, NTEXT, and IMAGE data types constitute the so-called TEXT/IMAGE data types. IMAGE Data Objects include all the data (download module, audio/video), while TEXT and NTEXT data objects include TEXT data.

Save the default value for differences between text/image data types and other database values. The storage method is to use the B-tree structure pointing to the data field. For columns that contain text/image data types, a 16-byte indicator is stored in the data row to specify where data can be found.

If the text/image data is smaller than 32 KB, the indicator points to the root node in the B-tree structure. The node length is about 84 bytes. The physical module where the root node points to the data. If the total data volume is greater than 32 KB, the Database Engine establishes an intermediate node in the data module and the root node. Since each table contains more than one column with such data, all values in the column are stored together. However, a physical page can contain only text/image data in a single table.

Text/image data types have many limitations. They cannot be treated as local variables. They cannot be used as part of the index and cannot be used in clauses of WHERE, order by, and group. In some cases, you can use VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX) to replace text/image data types.

4.3 CURSOR

4.4 UNIQUEIDENTIFIER

The UNIQUEIDENTIFIER data type value is a unique identification number, which is a binary mechanism string stored in 16 bytes. It is closely related to the world's unique identifier and ensures the uniqueness of the world. Therefore, data and objects can be identified in distributed computers by using data types. The NEWID and NEWSEQUUENTIALID functions can be used to initialize columns or UNIQUEIDENTIFIER-type variables, and string constants are written in a special format such as hexadecimal numbers and hyphens.

4.5 SQL _VARTANT

SQL _VARIANT data types are used to store various data types at the same time, such as numerical values and string date values. Only values of the TIMESTAMP type cannot be stored. Each value in the SQL _VARIANT column has two parts: Date value and description value information. This information contains all attributes of the data type, such as length, range, and accuracy.

4.6 TABLE

4.7 XML

4.8 Spatial

4.9 HIERARCHYID

The UNIQUEIDENTIFIER data type is used to save the entire layer. It is installed as CLR custom data, including system functions for creation and operation on some level nodes: GetAncestor (), GetDescendant (), read () and Wriite ().

4.10 TIMESTAMP

The TIMESTAMP data type specifies the columns VARBINARY (8) and BINARY (8), which are determined by the invalid values in the column. The system contains the current value (not the date or time) of each database, so that the number of rows containing the TIMESTAMP column is automatically increased during insertion or update.

4.11 Custom Data Types

 

5. a decimal storage type with VARDECIMAL

Vardecimal is a storage format, not a data type.

The vardecimal storage format can greatly reduce the data storage size, but does increase the CPU overhead. The vardecimal storage format is used at the table level. That is to say, you cannot only store certain decimal columns in the table in the vardecimal storage format, but not other columns in the format. Decimal data is maintained as its own data type.