Confusing Data Types in SQL Server

Search for the article content from the Internet and sort it out.
(1) Char, varchar, text and nchar, nvarchar, ntext
Char and varchar are both between 1 and 8000 characters in length. The difference is that char is a fixed-length character data while varchar is a variable-length character data.
The so-called fixed length is fixed length. when the length of the input data does not reach the specified length, it will be automatically filled with English spaces behind it,
Make the length reach the corresponding length, while the variable-length data is not filled with spaces. Remove trailing spaces when reading long-character data.
Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.
Compared with the preceding three data types, the names only contain letters "N", which indicate that the characters of the Unicode data type are stored.
Friends who have written programs should be familiar with Unicode. Only one byte is required for English characters, but there are many Chinese characters and two
Byte storage, which can cause confusion when both English and Chinese characters exist. Unicode character sets are generated to solve the incompatibility problem of character sets,
All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000.
Compared with Char and varchar, nchar and nvarchar can store a maximum of 4000 characters, whether in English or Chinese characters, while char and varchar are the most
It can store 8000 English letters and 4000 Chinese characters. We can see that when using the nchar and nvarchar data types, you don't have to worry about whether the entered characters are English or
Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

(2) datetime and smalldatetime
Datetime: Date and Time data from January 1, 3% to seconds.
Smalldatetime: Date and Time data from January 1, January 1-20, 1900 to January 1, June 6, accurate to minutes.

(3) bigint, Int, smallint, tinyint, and bit
Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807.
INT: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647.
Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767.
Tinyint: integer data from 0 to 255.
Bit: an integer of 1 or 0.

(4) decimal and numeric
The two data types are equivalent. There are two parameters: P (precision) and S (number of decimal places ). P indicates that the Left and Right decimal places can be stored
The maximum number of decimal numbers. P must be the value from 1 to 38. S specifies the maximum number of decimal digits that can be stored on the right of the decimal point,
S must be the value from 0 to P. The default decimal point is 0.

(5) float and real
Float: floating point data from-1.79 ^ 308 to 1.79 ^ 308.
Real: floating point data from-3.40 ^ 38 to 3.40 ^ 38. In SQL Server, the synonym for real is float (24 ).

SQL Server Data Types

Data Type	Type	Description
Bit	Integer	The BIT data type is an integer, and its value can only be 0, 1, or null. This data type is used to store data with only two possible values, such as yes, no, true, or fa lse, on, or off.
Tinyint	Integer	The tinyint data type can store integers ranging from 0 to 255. It is useful when you only plan to store a limited number of values. This data type occupies 1 byte in the database
Smallint	Integer	The smallint data type can store integers from-215 (-32768) to 215 (32767. This type of data is useful for storing numeric data that is often limited to a specific range. This data type occupies 2 bytes of space in the database.
Int	Integer	Int data type can store integers from-231 (-2147483648) to 231 (2147483647. This type can be used for almost all numeric data stored in the database. This data type occupies 4 bytes in the database.
Bitint	Integer	Integer Data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807)
Decimal/numeric	Exact numeric type	The decimal data type can be used to store numeric data with fixed precision and range from-1038-1 to 1038-1. When using 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.
Money	Currency type	The money data type is used to indicate money and currency values. This data type can store data from-2 ^ 63/10000 (-922337203685477.5808) to (2 ^ 63-1)/10000 (922337203685477.5807), accurate to one thousandth of the currency unit
Smallmoney	Currency type	The smallmoney data type is used to indicate money and currency values. This data type can store data from-214748.3648 to 214748.3647, accurate to one thousandth of the currency unit
Float	Approximate numeric type	Float data is an approximate value type for floating point numbers. Floating Point Numbers are similar because not all numbers in the range can be accurately expressed. Floating Point number can be any number from-1.79e + 308 to 1.79e + 308
Real	Approximate numeric type	The real data type is similar to a floating point number and is an approximate value type. It indicates the floating point number between-3.40e + 38 and 3.40e + 38.
Datetime	Datetime type	The datetime data type is used to represent the date and time. This data type stores all the date and time data from January 1, 1/300 to 3.33 seconds or milliseconds.
Smalldatetime	Datetime type	The smalldatetime data type is used to indicate the date and time from January 1, January 1-20, 1900 to January 1, June 6, accurate to one minute.
Cursor	Special Data Type	The cursor data type is a special data type that contains a reference to the cursor. This type of data is used in the stored procedure and cannot be used when creating a table.
Timestamp	Special Data Type	The timestamp data type is a special data type used to create a unique digital within the database range. A table can have only one timestamp column. The value of the timestamp column changes each time a row is inserted or modified. Although its name contains "time", the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.
Uniqueidentifier	Special Data Type	The uniqueidentifier data type is used to store a globally unique identifier (guid. GUID is indeed globally unique. This number has almost no chance of being rebuilt in another system. You can use the newid function or convert a string to a unique identifier to initialize a column with a unique identifier.
Char	Character Type	The Char data type is used to store non-uniform encoded data with a fixed length. When defining a column of this type, you must specify the column length. This data type is useful when you always know the length of the data to be stored. For example, when you store data in zip code and 4 character format, you know that it always takes 10 characters. The column width of this data type is up to 8000 characters
Varchar	Character Type	The varchar data type, same as the char type, is used to store non-uniform encoding character data. Unlike char, the data type is variable. When defining a column as the data type, you must specify the maximum length of the column. The biggest difference between it and char data types is that the storage length is not the column length, but the data length.
Text	Character Type	The text data type is used to store a large amount of non-uniform encoding character data. This data type can contain a maximum of 2 billion to characters
Nchar	Unified encoding type	The nchar data type is used to store fixed-length, unified encoding data. Unified encoding uses a dual-byte structure to store each character, rather than a single byte (in common text ). It allows a large number of extended characters. This data type can store 4000 characters, doubling the byte space used
Nvarchar	Unified encoding type	The nvarchar data type is used as a variable-length uniform encoding type data. This data type can store 4000 characters, and the byte space is doubled.
Ntext	Unified encoding type	The ntext data type is used to store a large amount of uniform encoding data. This data type can store 230-1 or nearly 1 billion characters, and the byte space is doubled.
Binary	Binary data type	The binary data type is used to store binary data that can be up to 8000 bytes in length. When the content of the input table is close to the same length, you should use this data type.
Varbinary	Binary data type	The varbinary data type is used to store up to 8000 bytes of long binary data. This type of data should be used 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 2 billion-1-1 or approximately bytes.