Description and comparison of SQL Server Field Types

Description of SQL Server Field Types

The following describes the Field Types of SQL Server 7.0 and later versions. For more information about the field types of SQL Server 6.5, see the description provided by SQL Server.

Bit: an integer of 0 or 1
INT: an integer from-2 ^ 31 (-2,147,483,648) to 2 ^ 31 (2,147,483,647)
Smallint: integer number from-2 ^ 15 (-32,768) to 2 ^ 15 (32,767)
Tinyint: integer number from 0 to 255

Decimal: number of fixed precision and valid digits from-10 ^ 38 to 10 ^ 38-1
Numeric: synonym for decimal

Money: from-2 ^ 63 (-922,337,203,685,477.5808) to 2 ^ 63-1 (922,337,203,685,477.5807), the minimum unit of currency is 10 points.
Smallmoney: The minimum monetary unit from-214,748.3648 to 214,748.3647

Float: a variable-precision number 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, January 1-9, 1753, 999, and the minimum time unit is 3% seconds or 3.33 milliseconds.
Smalldatetime: Date and Time data from January 1, January 1-20, 1900 to January 1, June 6. The minimum time unit is minute.

Timestamp: Timestamp, a unique number in the database width.
Uniqueidentifier: guid, a globally unique identifier.

CHAR: fixed-length non-Unicode bytes data with a maximum length of 8000
Varchar: Variable Length non-Unicode bytes data, maximum length: 8000
Text: Variable Length non-Unicode bytes data. The maximum length is 2 ^ 31-1 (2g)

Nchar: bytes data with a fixed length of Unicode. The maximum length is 8000.
Nvarchar: variable-length Unicode bytes. The maximum length is 8000.
Ntext: String-type data with Extended Unicode length. The maximum length is 2 ^ 31-1 (2g)

Binary: binary data with a fixed length. The maximum length is 8000.
Varbinary: variable-length binary data with a maximum length of 8000
Image: variable-length binary data with a maximum length of 2 ^ 31-1 (2g)

Introduction

Ms SQL Server is mainly used by five frequently used fields, character type, text type, numeric type, logic type, and date type! Many of my friends are overwhelmed by the mismatch of Field Types after converting access to SQL in some BBs. Let's introduce the features of these five data types!

Zheng Wen

[1] character data varchar and char

ProgramWhen you need to store short string information, we need to use string-type data. SQL has two types of string-type data! The differences between the varchar and char types are subtle, but important!

For example, if you enter a 4-character tol8 string in a varchar field with a length of 40 characters and then retrieve the data, the data length is 4 characters, if you replace varchar with Char, the data you take out will be 40 characters in length, and extra spaces will be appended to the string, for example, if tol8 has only four characters, the data retrieved from Char is tol8 plus 36 spaces! In the specific program design, I personally recommend the varchar type, because from some references, I found that the varchar type occupies less memory than Char!

The varchar storage can contain a maximum of 255 characters. If the length is exceeded, the text data we will discuss below will be used.

[2] Text Data Text

You can store more than 2 billion strings in text data. How about this? However, not all text data is used at all times, because it occupies a lot of space and consumes a lot of servers! Because even if you enter a null value like a text field, it will occupy 2 K space! At this time, there is no way to reclaim space except to delete the data!

[3] Data Type int, tinying, numeric

SQL supports multiple data types, allowing you to store integers, decimals, and money!

INT (integer data) indicates that the range is an integer from-2,147,483,647 to 2,147,483,647.

If your value is not that long, you can use smallint to indicate a range of-32768 to 32768 Integers to save memory space! They are used in the same way!

If you want to save space, you can use tinyint to store only integers ranging from 0 to 255. Note that tinyint cannot store negative numbers, and a tinyint occupies one byte, an int data occupies 4 bytes!

[4] data-type data bit

The check box on the Web page collects information and saves it to the database. This is the most common bit-type usage! It has only two values: 0 or 1.

[5] data-type data datetime, samlldatetime

The storage range of datetime is from the first millisecond of January 1, 1753 to the last millisecond of December 31, 9999. If you do not need to cover such a large date range, optional. The storage range of the smalldatetime type is from January 1, January 1-20, 1900 to January 1, June 6, but it can only be accurate to seconds!

[6] Special Data cursor

Here are some binary data types and some other data types that are not commonly used. I will not talk about them anymore!

If you want to learn more about SQL database, you can refer to some relevant professional books. After all, Microsoft SQL Server is a professional database software with powerful functions and good performance! If you want to further discuss the specific usage issues, you can ask questions in the Forum!