SQL Server database field type description

SQL Server database field type description

There are currently x field types in SQL Server database, broadly divided into 9 categories, namely string type, binary character string data type, Unincode string data, integer type, exact data type, approximate value floating point numeric data type, datetime data type, currency data type, Special data types. Of course, SQL Server also provides user-defined data types, but in this article I do not discuss.

String type

Char

The char data type is used to store fixed-length, non-uniform encoded data of specified lengths. When you define a column for this type, you must specify a column length. This data type is useful when you always know the length of the data you want to store. For example, when you store data by zip code plus 4 character formatting, you know that you always need to use 10 characters. This data type has a maximum column width of 8,000 characters.

Varchar

The varchar data type does not exceed 8KB in length, that is, no more than 8,000 bytes, and the same char type is used to store non-uniform encoded character data. Unlike the char type, this data type is variable length. When you define a column for that data type, you specify the maximum length of the columns. The biggest difference to the char data type is that the length of the stored data is not the length of the column.

Note: One of the characters is two bytes.

Text

Used to store a large number of non-uniform encoded character data. This data type can have a maximum of 231-1 or 2 billion characters

Varchar (MAX)

Data that is used, up to 2^30-1 bytes, appears in Microsoft SQLServer2005 and later. Microsoft's argument is that this data type is used instead of the previous text data type, with a large value data type, which is much more flexible than before in the operation of the large value data. For example: Before the text is not ' like ', with varchar (max) There is no such problem, because varchar (max) in the Behavior and varchar (n) on the same, so can be used in varchar can be used in varchar (max On

Binary code String type

Binary

Used to store fixed-length binary data up to 8000 bytes long. Binary can be either fixed-length or variable-length. binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

Varbinary

Used to store variable-length binary data up to 8000 bytes long. The content size of the input table field is variable.

Image

Data types are used to store variable-length binary data up to 231-1 or approximately 2 billion bytes. The stored data is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the image data type using BMP, Tief, GIF, and JPEG formats.

Varbinary (MAX)

Used in versions of Microsoft SQLServer2005 and above, similar to varchar (MAX), which is used instead of the image big data type. Similarly, it can be retrieved through the Where condition like.

Unincode String Type

Nchar

Used to store fixed-length, uniformly encoded character data. Uniform encoding uses a double-byte structure to store each character, rather than a single byte (in normal text). It allows for a large number of extended characters. This data type can store 4000 characters, which is used to increase the byte space by one times.

Nvarhcar

Used as a uniformly encoded character type data that is variable length. This data type can store up to 4000 characters, and the byte space used increases by one times.

Ntext

Used to store a large number of uniformly encoded character-type data. This data type can store 230-1 or nearly 1 billion characters, and the byte space used increases by one times.

Nvarchar (MAX)

Used in versions of Microsoft SQLServer2005 and above, similar to varchar (MAX), is used to replace the ntext large data type. Similarly, it can be retrieved through the Where condition like.

Description: in Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server Setup, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types.

Integer type

Bit

The bit data type is an integral type, a data type that is space-saving, with values of only 0, 1, or null values. This data type is used to store data that has only two possible values, such as Yes or No, True or false, on, or off.

Tinyint

is relatively safe and does not accept embedding of malicious script content. The data type can store integers from 0 to 255. It is useful when you only intend to store a limited number of values. This data type occupies 1 bytes in the database.

Smallint

The smallint data type is also relatively safe and does not accept the embedding of malicious script content. It can store integers from 2 to 15 powers (-32768) to 2 15 powers (32767). This data type is useful for storing numeric data that is often scoped to a specific range. This data type occupies 2 bytes of space in the database.

Int

You can store integers from 2 to 31 power (-2147483648) to 31 power of 2 (2147483 647). Almost all numeric data stored in a database can be used with this data type. This type of data occupies 4 bytes in the database.

Bigint

Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). The storage size is 8 bytes.

Exact data type

Decimal

The decimal data type can be used to store numeric data of a fixed precision and range from 10 to 38 powers-1 to 10 of 38 power-1. When you use this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. The precision is the number of digits stored on the right of the decimal point.

Numberic

The number represented can reach 38 bits, and the number of bytes used to store the data varies with the number of bits used for use, and the only difference is that the decimal data type with decimals can be used to store numbers when the data values must be stored exactly as specified.

Approximate floating-point numeric data types

Float

It is an approximate numeric type that is used by floating point numbers. It is said that the floating-point number is approximate, because not all the numbers in its range can be accurately represented. Floating-point numbers can be variable-precision floating-point numbers from -1.79E+308 to 1.79E+308

Real

Like float, it is just a floating-point number with a variable precision between -3.40E+38 and 3.40E+38.

Date Time Data type

smalldatetime

It is used to indicate the date and time from January 1, 1900 to June 6, 2079, which can be exactly one minute.

Datetime

It is used to store all date and time data from January 1, 1753 to December 31, 9999, accurate to 1% seconds or 3.33 milliseconds

Description: The date and time data type consists of a valid date and time. For example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before, the time in the last data type is a moment before, and the date in the back. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 Storage bytes). When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).

The format of the date can be set. The commands for formatting dates are as follows:

Set DateFormat {format | @format _var|

Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.

For example, when the set DateFormat YMD is executed, the date is formatted as a month and day form; When the set DateFormat DMY is executed, the date is in the form of sun and moon.

Currency data type

SmallMoney

Used to denote money and currency values. This data type can store data from 214748.3648 to 214748.3647, accurate to one out of 10,000 of the currency unit.

Money

It represents money and currency values. This data type can store data from 922 billion to 922 billion, accurate to one out of 10,000 of the currency unit

Special data types

Timestamp

The timestamp data type is a special type of data that is used to create a database-wide unique digital. There can be only one timestamp column in a table. Each time you insert or modify a row, the value of the timestamp column changes. Although it has "time" in its name, the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.

uniqueidentifier

The uniqueidentifier data type is used to store a globally unique identifier, the GUID. The GUID is truly globally unique. This number has little chance of being rebuilt in another system. You can initialize a column with a unique identifier by using the NEWID function or by converting a string to a unique identifier.

Xml

XML data types are used in versions of Microsoft SQLServer2005 and above, and XML documents and fragments can be stored in a SQL Server database. An XML fragment is an XML instance that lacks a single top-level element. You can create columns and variables of XML type and store XML instances in them. Note that the stored XML data type indicates that the instance size cannot exceed 2 GB. You can selectively associate an XML schema collection with a column, parameter, or variable of an XML data type. The schemas in the collection are used to validate and type XML instances. In this case, the XML is typed.

sql_variant

Sql_variant data types are used in a similar way to variant data types in Microsoft Visual Basic. Sql_variant enables a single column, parameter, or variable to store data values of different data types. For example, a sql_variant column can hold int, decimal, char, binary, and nchar values. Each sql_variant column instance records data values and metadata information. These include basic data types, maximum size, scale, precision, and collation.

The sql_variant object can hold all SQL Server data types except for the following types: text, ntext, image, varchar (max), nvarchar (max), varbinary (max), XML, Data for timestamp and Microsoft. NET Framework Common Language runtime [CLR] user-defined types. In addition, sql_variant data instances cannot have sql_variant as their base data type. Constants of all types can be specified in predicates or assignments that reference the sql_variant column. If the sql_variant value is null, it is considered to have no underlying base data type. This rule applies even if the null value comes from a variable or column that has a special data type.

SQL Server database field type description