SQL Server Data type

Bit integral type
The bit data type is integral, and its value can only be 0, 1, or null. 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.
Note: A data type that is space-saving and should be used as much as possible if it satisfies the requirements.

tinyint integral type
The tinyint 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.
Note: If the bit type is too monotonous to meet your needs, you can consider using the tinyint type because this type is relatively safe and does not accept embedding of malicious script content.

smallint integral type
The smallint data type can store integers from 15 powers (-32768) of 2 to 15 powers (32767) of 2. 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.
Note: If the tinyint type is too monotonous to meet your needs, you can consider using the smallint type because this type is relatively safe and does not accept embedding of malicious script content

int integral type
The int data type can store integers from 2 to 31 powers (-2147483648) to 2 to 31 powers (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.
Note: If smallint is not able to meet your needs, you can consider using a larger int type.

Bint Integral type
The Bint data type represents a range of values from -2^31 to 2^31-1, that is, you can use an int data type to express an integer between 2,147,483,648 and 2,147,483,647 (that is, about plus or minus 2 billion). An int type occupies four bytes of storage space.
The bigint can accurately represent integers from -2^63 to 2^63-1 (that is, from 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), which takes up eight bytes of storage space

Decimal exact numeric type
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. Precision is the number of digits stored on the right of the decimal point

numeric precise numerical type
The numeric data type is similar to decimal.

SmallMoney currency type
The smallmoney data type is used to represent 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 currency type
The Money data type is used to denote monetary 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

float Approximate numerical type
The float data type 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 any number from -1.79E+308 to 1.79E+308

Real approximate numeric type
Real data types are approximate numeric types like floating-point numbers. It can represent a floating-point number between -3.40E+38 and 3.40E+38.

char character type
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 character varchar data types, like char types, are 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.

Text character type
The text data type is 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.

NCHAR Unified coded Character type
The nchar data type is 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.

Nvarchar Unified coded character type
The nvarchar data type is 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 Unified coded character type
The ntext data type is 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

Date is the newly introduced data type for SQL Server 2008. It represents a day, and does not contain a time part, which can be expressed from January 1 to December 31, 9999 of the year of the A.D.. Storage space that requires only 3 bytes is the newly introduced data type for SQL Server 2008. It represents a day, and does not contain a time part, which can be expressed from January 1 to December 31, 9999 of the year of the A.D.. Requires only 3 bytes of storage space

TIME specifies the number of digits for the decimal part of the second.
This can be an integer from 0 to 7.
The default decimal precision is 7 (100ns).
The default string literal format
(For downlevel clients)
hh:mm:ss[. nnnnnnn]
For more information, see the "Backward compatibility for downlevel clients" section later in this chapter.
Range
00:00:00.0000000 to 23:59:59.9999999
Element range
HH is a two-digit number representing the hour, ranging from 0 to 23.
MM is a two-digit number that represents minutes, ranging from 0 to 59.
The SS is a two-digit number representing seconds, ranging from 0 to 59.
N is a 0 to 7 digit number, ranging from 0 to 9999999, which represents the number of seconds of the decimal part.
Character length
Minimum 8 bits (HH:MM:SS), maximum 16 bits (HH:MM:SS. nnnnnnn)

smalldatetime Date and Time type
The smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, accurate to one minute

DateTime Date-Time type
The datetime data type is used to represent the date and time. This data type stores all date and time data from January 1, 1753 to December 31, 9999, accurate to 1% seconds or 3.33 milliseconds.

The DateTime2 field type corresponds to a time format of Yyyy-mm-dd HH:mm:ss.fffffff, 7 F, accurate to 0.1 microseconds (μs), example 2014-12-03 17:23:19.2880929.
If the date function of SQL is assigned, the DateTime field type is GETDATE () and the DateTime2 field type is Sysdatetime ()

The DateTimeOffset data type, the time zone offset portion is added, and the time zone offset is expressed as [+|-] hh:mm. HH is a 2-digit range from 00 to 14 that represents the number of hours of the time zone offset. MM is a 2-digit range from 00 to 59, which represents the number of additional minutes for the time zone offset. The time format is supported to a minimum of 100 nanoseconds. The required + or-symbol indicates whether the time zone offset is added or subtracted from UTC (Universal Reconcile time or Greenwich Mean time) to obtain local time. Examples of Use:
Declare @dt as DateTimeOffset (8)
Set @dt = ' 2008-08-08 08:08:08.0 '
Select @dt

Cursor Special Data type
The cursor data type is a special type of data that contains a reference to a cursor. This data type is used in stored procedures and cannot be used when creating tables

Timestamp Special Data type
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.

Binary binary data type
Binary data types are used to store fixed-length binary data up to 8000 bytes long. You should use this data type when the contents of the input table are close to the same length.

varbinary binary data types
The varbinary data type is used to store variable-length binary data up to 8000 bytes long. You should use this data type when the content size of the input table is variable

Image binary Data type
The image data type is used to store variable-length binary data up to 231-1 or approximately 2 billion bytes

uniqueidentifier Special Data type
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 use the NEWID function or convert a string to a unique
Identifier to initialize the column with a unique identifier.

Xml

Sql_variant is equivalent to the object type in C #. It can be stored in many different formats. For example, a sql_variant column can hold a number, or it can hold text.
Of course, because of this feature, they must be in the reading of some conversion, otherwise it is not known what type. And this conversion can be risky, which is called unsafe type.

The Geography Geospatial data Type Geography is implemented as a. NET Common language Runtime (CLR) data type in SQL Server. This type represents data in a circular earth coordinate system. The SQL Server Geography data type is used to store ellipsoidal (circular earth) data such as GPS latitude and longitude coordinates.

The geometry planar spatial data type geometry is implemented as a common language-time (CLR) data type in SQL Server. This type represents the data in the Euclidean (planar) coordinate system.

The HierarchyID type is actually a CLR custom data type Open in turn: Database--System Database->master-> programmability--type--system data type->CLR data type HierarchyID, you can see the data type.

Field type description
Integer digits of bit 0 or 1
int integer number from -2^31 (-2,147,483,648) to 2^31 (2,147,483,647)
smallint integer numbers from -2^15 (-32,768) to 2^15 (32,767)
tinyint integer numbers from 0 to 255

Number of fixed and significant digits from -10^38 to 10^38-1 decimal
Synonyms for numeric decimal

Money data from -2^63 ( -922,337,203,685,477.5808) to 2^63-1 (922,337,203,685,477.5807), Min. currency Unit 10 per thousand
SmallMoney Currency data from -214,748.3648 to 214,748.3647, Min. currency Unit 10 per thousand

Float variable-precision numbers from -1.79E+308 to 1.79E+308
Real variable-precision numbers from -3.04E+38 to 3.04E+38

DateTime date and time data from January 1, 1753 to 9999 12th 31, the minimum time unit is 3% seconds or 3.33 milliseconds
smalldatetime date and time data from January 1, 1900 to June 6, 2079, Min. time unit is minutes

Timestamp timestamp, unique number of a database width
uniqueidentifier Global Unique identifier GUID

Char fixed-length non-Unicode character data up to a maximum of 8000
varchar variable length non-Unicode character data with a maximum length of 8000
Text variable length non-Unicode character data with a maximum length of 2^31-1 (2G)

NChar fixed-length Unicode character data with a maximum length of 8000
nvarchar variable-length Unicode character data up to a maximum of 8000
ntext variable-length Unicode character data, Max. 2^31-1 (2G)

Binary fixed-length binary data with a maximum length of 8000
varbinary variable-length binary data up to a maximum of 8000
Image variable length binary data with a maximum length of 2^31-1 (2G)

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Bit
1 bits with a value of 0 or 1
Int
Integer
4 bytes with a value of -2^31~2^31-1
Smallint
2 bytes with a value of -2^15~2^15-1
Tinyint
1 bytes with a value of 0~255
Decimal (P,s)
Digital data with a fixed precision of p and a width of s
Numeric
Money
8 bytes, stored in currency type with a value of -2^63~2^63-1
Small Money
4 bytes, holding the currency type with a value of -214748.3648~+214748.3647 approximate numeric data type
Float (N)
n Between 1~24, 4 byte, 7 bit precision
N=1~7 for Real
n between 25~53, 8 byte, 15 bit precision
=8~15 to float
Datetime
8 bytes, which describes the date and time of day, and the accuracy of the value is 1/300 seconds
smalldatetime
4 bytes, describing the date and time of day, with a precision of minutes
Cursor
A reference to a cursor
Timestamp
8 bytes, the only data stored within the database
uniqueidentifier
16 bytes, which holds the globally unique identifier (GUID)
Char (N)
Fixed length of non-Unicode string, n=1~8000
Character (N)
Varchar (N)
Variable length, non-Unicode string n=1~8000
Char varying (n)
Text
Variable-length non-Unicode data in the server code page. Maximum length is 231-1 characters
Nchar
Fixed-length Unicode string n=1~4000
National character (n),
National char (N)
Nvarchar
Fixed-length Unicode string n=1~4000
national character varying (n)
Ntext
Variable-length Unicode data with a maximum length of 230-1 characters
National text
Binary (N)
Fixed-length binary data, n between 1~8000, storage space of n+4 bytes
Varbinary (N)
Variable-length binary data, n=1~8000
Binary varying (n)
Tmage
Variable-length binary data with a size of 0~231-1
Attention:
1) For numeric data types, the width (scale) refers to the number of digits that are stored after the decimal point, and the precision (precision) is the number of digits that can be stored that contains the decimal point.
2) The storage width of money and small money is 4.
3) The timestamp column value is automatically updated when each row is updated, and the timestamp column cannot be part of the keyword or keyword.
4) The unique identity data type cannot use arithmetic operators such as +,-and so on, and this data type can only use equality comparison operations. Unicode is the standard for uniformly storing data for all character sets. It uses two times as much storage space as non-Unicode data stores.

SQL Server Data type