Sqlserver Field Types

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, False, On, or Off. note: a data type that saves a lot of space. If you can meet your needs, use it as much as possible. Tinyint integer tinyint data type can be stored from 0 to 2

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, False, On, or Off. note: a data type that saves a lot of space. If you can meet your needs, use it as much as possible. Tinyint integer tinyint data type can be stored from 0 to 2

Bit integer
Bit DataTypeIt is an integer and its value can only be 0, 1, or null. Such dataTypeStore data with only two possible values, such as Yes, No, True, False, On, or Off.

Note: A type of data that saves a lot of spaceTypeIf you can meet your needs, use it as much as possible.

Tinyint integer
Tinyint dataTypeIt can store integers ranging from 0 to 255. It is useful when you only plan to store a limited number of values. Such dataTypeOccupies 1 byte in the database.

NOTE: If bitTypeToo monotonous to meet your needs, you can consider using tinyintTypeBecause of thisTypeIt is relatively secure and does not accept the embedding of malicious script content.

Smallint integer
Smallint dataTypeIt can store integers between the 15 power (-32768) of-2 and the 15 power (32767) of 2. Such dataTypeIt is useful for storing numeric data that is often limited to a specific range. Such dataTypeOccupies 2 bytes of space in the database.

NOTE: If tinyintTypeToo monotonous to meet your needs, you can consider using smallintTypeBecause of thisTypeIt is relatively secure and does not accept the embedding of malicious script content.

Int integer
Int dataTypeIt can store integers between the 31 Power (-2147483648) of-2 and the 31 Power (2147483 647) of 2. Almost all numeric data stored in the database can be used.Type. Such dataTypeOccupies 4 bytes in the database.

NOTE: If smallint does not meet your requirements, you can consider using a larger intType.

Decimal exact numeric type
Decimal dataTypeIt can be used to store fixed precision and range of numeric data of 38 power-1 to 10 from-10. Use this dataTypeThe range and precision must be specified. 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 exact numeric type
Numeric dataTypeSimilar to decimal.

Smallmoney currency type
Smallmoney dataTypeUsed to indicate the value of money and currency. Such dataTypeIt can store data from-214748.3648 to 214748.3647, accurate to one thousandth of the currency unit

Money currency type
Money dataTypeUsed to indicate the value of money and currency. Such dataTypeIt can store data from-922 billion to 922 billion, accurate to one thousandth of the currency unit

Float approximate numeric type
Float dataTypeIs an approximate value.TypeFor floating point. 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
Real DataTypeSimilar to floating point, it is an approximate valueType. It indicates the floating point number between-3.40E + 38 and 3.40E + 38.

Smalldatetime datetime type
Smalldatetime dataTypeUsed to indicate the date and time from January 1, January 1-20, 1900 to January 1, June 6, accurate to one minute

Datetime type
Datetime dataTypeUsed to represent the date and time. Such dataTypeIt stores all the date and time data from January 1, 1/300, accurate to 3.33 seconds or milliseconds.

Cursor Special Data Type
Cursor dataTypeIs a special type of dataTypeIt contains a reference to the cursor. Such dataTypeUsed in the stored procedure and cannot be used when creating a table

Timestamp Special Data Type
Timestamp dataTypeIs a special type of dataTypeUsed 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
Uniqueidentifier dataTypeIt 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 be unique.
Identifier to initialize a column with a unique identifier.

Char character type
Char dataTypeIt is used to store data of a fixed length non-uniform encoding type. When defining a column to thisTypeYou must specify the column length. When you always know the length of the data to be stored, this dataTypeVery useful. For example, when you store data in zip code and 4 character format, you know that it always takes 10 characters. This dataTypeThe column width cannot exceed 8000 characters.

Varchar complex-type varchar dataType, Same as charTypeThe same is used to store non-uniform encoding character data. This data is different from the char type.TypeVariable Length. When defining a column as this dataTypeYou must specify the maximum length of the column. It corresponds to char data.TypeThe biggest difference is that the storage length is not the column length, but the data length.

Text character type
Text DataTypeIt is used to store a large amount of non-uniform encoding character data. Such dataTypeA maximum of 2 billion to characters can be entered.

Nchar unified encoding type
Nchar dataTypeIt 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 dataType4000 characters can be stored, doubling the byte space used.

Nvarchar uniform encoding type
Nvarchar dataTypeIt is used as a variable-length unified encoding type data. This dataType4000 characters can be stored, doubling the byte space used.

Ntext unified encoding type
Ntext dataTypeIt is used to store a large amount of unified encoding data. Such dataTypeIt can store 230-1 or nearly 1 billion characters and doubles the byte space used

Binary dataType
Binary dataTypeIt is used to store binary data up to 8000 bytes in length. This type of data should be used when the content in the input table is close to the same length.Type.

Varbinary binary dataType
Varbinary dataTypeIt is used to store up to 8000 bytes of long binary data. This type of data should be used when the size of the input table is variable.Type

Image Binary dataType
Image DataTypeUsed to store variable-length binary data, up to 2 billion-1-1 or approximately bytes



---------------------------------------------------
(1) binary dataType
Binary data includes Binary, Varbinary, and Image
Binary dataTypeIt can be either a fixed length (Binary) or a variable length.
Binary [(n)] is a fixed n-bit Binary data. The value range of n is from 1 to 8000. The memory size is n + 4 bytes.
Varbinary [(n)] is a binary data with n-bit varying length. The value range of n is from 1 to 8000. The memory size is n + 4 bytes, not n Bytes.
In the Image dataTypeThe stored data is stored in a bit string, which is not explained by SQL Server and must be explained by the application. For example, applications can use BMP, TIEF, GIF, and JPEG formats to store data in ImageType.

(2) character dataType

Character dataTypeIncluding Char, Varchar, and Text
Character data is composed of any combination of letters, symbols, and numbers.
Varchar is a variable-length data with a length of no more than 8 KB. Char is a fixed-length character data with a maximum length of 8 KB. Text data can be used for ASCII data larger than 8 KB.TypeStorage. For example, because all Html documents are ASCII characters and the length exceeds 8 kb in general, these documents can use Text data.TypeStored in SQL Server.

(3) Unicode dataType

Unicode dataTypeIncluding Nchar, Nvarchar, and Ntext
In Microsoft SQL Server, traditional non-Unicode dataTypeCharacters defined by a specific character set are allowed. You can select a character set during SQL Server installation. Use Unicode dataTypeThe column can store any characters defined by the Unicode standard. The Unicode Standard contains all characters defined in various character sets. Use Unicode dataTypeThe solution is to use non-Unicode data.TypeThe size of the shard occupied is twice.
In SQL Server, Unicode data uses Nchar, Nvarchar, and Ntext data.TypeStorage. Use this characterTypeThe stored columns can store characters in multiple character sets. When the length of a column changes, the Nvarchar character should be used.TypeIn this case, up to 4000 characters can be stored. When the column length is fixed, Nchar characters should be usedTypeAt the same time, a maximum of 4000 characters can be stored. When Ntext data is usedTypeThe column can store more than 4000 characters.

(4) Date and Time DataType

Date and Time DataTypeIncluding Datetime and SmalldatetimeType
Date and Time DataTypeIt 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: 01 AM 8/17/98 ". Previous dataTypeIs the date before, the time is in the next dataTypeIt is the time before and the date is later. In Microsoft SQL Server, date and time dataTypeIncluding Datetime and SmalldatetimeTypeThe storage date range is from January 1, January 1, 1753 to January 1, December 31, 9999 (each value requires 8 storage bytes ). Use Smalldatetime dataTypeThe storage date range is from January 1, January 1, 1900 to January 1, December 31, 2079 (each value requires 4 Storage bytes ).
The date format can be set. The command to set the date format is as follows:
Set DateFormat {format | @ format _ var |
Where, format | @ format_var is the date order. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after you run Set DateFormat YMD, the date format is in the format of year, month, and day. After you run Set DateFormat DMY, the date format is in the format of day, month, and year.

(5) Digital DataType

Numeric data only contains numbers. Digital DataTypeIncluding positive and negative numbers, decimals (floating point numbers), and Integers
Integers are composed of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, data stored in IntegersTypeIs Int, Smallint, and Tinyint. Int dataTypeThe range of stored data is greater than that of Smallint data.TypeData storage range, while Smallint dataTypeThe range of stored data is greater than that of Tinyint.TypeData storage range. The range of data stored by dogs using Int data is from-2 147 483 648 147 to 2 483 647 (each value requires 4 bytes of storage space ). Use Smallint dataTypeThe data storage range is-32 768 to 32 767 (each value requires 2 bytes of storage space ). Use Tinyint dataTypeThe data storage range is from 0 to 255 (each value requires one byte storage space ).
Precise data in SQL ServerTypeDecimal and Numeric. The storage space occupied by such data is determined based on the number of digits after the number of digits of the data.
Approximate decimal data in SQL ServerTypeFloat and Real. For example, the 1/3 score is recorded. 3333333. When using approximate dataTypeCan be accurately expressed. Therefore, the data retrieved from the system may be different from the data stored in this column.

(6) currency data indicates the number of positive or negative currencies.

Currency data in Microsoft SQL ServerTypeIt's Money and Smallmoney.
Money dataType8 storage bytes and Smallmoney data are requiredTypeFour storage bytes are required.

(7) Special DataType

Special DataTypeIncluding data not mentioned earlierType. Special DataTypeThere are three types: Timestamp, Bit, and Uniqueidentifier.
Timestamp is used to indicate the sequence of SQL Server activities, in the binary projection format. The Timestamp data has no relationship with the inserted data or the date and time.
Bit is composed of 1 or 0. When it indicates true or false, ON, or OFF, Bit data is used.Type. For example, you can check whether the client request for each access can be stored in this data.Type.
Uniqueidentifier consists of 16-byte hexadecimal numbers, indicating a globally unique one. GUID is useful when the record rows in a table must be unique. For example, use this data in the Customer ID columnTypeDifferent customers can be differentiated.

2. User-defined dataType

User-Defined dataTypeBased on the data provided in Microsoft SQL ServerType. When the same data must be stored in several tablesTypeAnd to ensure that these columns have the same dataTypeUser-Defined data can be used for length, and can be empty.Type. For example, you can define a data type called postal_code.TypeIt is based on Char dataType.
When creating user-defined dataTypeThree data numbers must be provided:TypeThe name and System DataTypeAnd DataTypeCan be empty.

(1) create user-defined dataType

Create user-defined dataTypeYou can use a Transact-SQL statement. The system stored procedure sp_addtype can be used to create user-defined data.Type. The syntax format is as follows:
Sp_addtype {type}, [, system_data_bype] [, 'null _ type']
Where, type is user-defined dataType. System_data_type is the data provided by the system.TypeSuch as Decimal, Int, and Char. Null_type indicates the dataTypeHow to handle NULL values must be enclosed by single quotes, such as 'null', 'not null', or 'nonull '.
Example:
Use cust
Exec sp_addtype ssn, 'varchar (11) ', "Not null'
Create a user-defined dataTypeSsn, which is based on system dataTypeIt is a string of 11 characters and cannot be blank.
Example:
Use cust
Exec sp_addtype birthday, datetime, 'null'
Create a user-defined dataTypeBirthday, which is based on system dataTypeIt is DateTime and can be null.
Example:
Use master
Exec sp_addtype telephone, 'varchar (24), 'not null'
Eexc sp_addtype fax, 'varchar (24) ', 'null'
Create two dataTypeThat is, telephone and fax

(2) Delete user-defined dataType

When user-defined dataTypeYou can delete an instance if it is not needed. Delete user-defined dataTypeThe command is sp_droptype {'type '}.
Example:
Use master
Exec sp_droptype 'ssn'
Note: When the columns in the table are still using user-defined dataTypeWhen there is a default or rule bound to it, the user-defined dataTypeCannot be deleted.

SQL SERVERFieldTypeDescription

The following are SQL Server and later versionsFieldTypeDescription. SQL SERVER6.5FieldTypeFor more information, see the description provided by SQL SERVER.

FieldTypeDescription
Integer Number of bit 0 or 1
Int integer 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)
Integer Number of tinyint from 0 to 255

Decimal's precision and number of valid digits from-10 ^ 38 to 10 ^ 38-1
Synonym for numeric decimal

The minimum unit of money is 10 thousandth of the currency data from-2 ^ 63 (-922,337,203,685,477.5808) to 2 ^ 63-1 (922,337,203,685,477.5807 ).
The minimum monetary unit of smallmoney ranges from-214,748.3648 to 214,748.3647.

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 data from the date and time data of January 1, January 1-9, 1753, 999, the minimum time unit is 3% seconds or 3.33 milliseconds.
The minimum time unit of smalldatetime is minute.

Timestamp, a unique number in the database width
Uniqueidentifier GUID

Char fixed length non-Unicode bytes data, maximum length: 8000
Varchar variable length non-Unicode character type data, maximum length is 8000
Text variable length non-Unicode bytes data, maximum length: 2 ^ 31-1 (2G)

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

Binary fixed-length binary data, up to 8000
Varbinary variable-length binary data; maximum length: 8000
The maximum length of an image is 2 ^ 31-1 (2G)


-------------------------------------
Bit
1 bit; Value: 0 or 1
Int
Integer
4 bytes; value range:-2 ^ 31 ~ 2 ^ 31-1
Smallint
2 bytes; value range:-2 ^ 15 ~ 2 ^ 15-1
Tinyint
1 byte; value range: 0 ~ 255
Decimal (p, s)
Numeric data with a fixed precision of P and a width of S
Numeric
Money
8 bytes, storing currencyType, The value is-2 ^ 63 ~ 2 ^ 63-1
Small money
4 bytes, storing currencyType, Value range:-214748.3648 ~ + 214748.3647 approximate numerical dataType
Float (n)
N in 1 ~ Between 24, 4 bytes, 7-bit precision
N = 1 ~ 7 is real
N in 25 ~ Between 53, 8 bytes, 15-bit precision
= 8 ~ 15 is float
Datetime
8 bytes to describe the date and time of a day. The value is accurate to 1/300 seconds.
Smalldatetime
4 bytes, which describes the date and time of a day, precise to minutes
Cursor
References to a cursor
Timestamp
8 bytes, unique data stored in the database
Uniqueidentifier
16 bytes, stores the Globally Unique Identifier (GUID)
Char (n)
The fixed length of a 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. The maximum length is 23-1 Characters
Nchar
Unicode string of fixed length n = 1 ~ 4000
National character (n ),
National char (n)
Nvarchar
Unicode string of fixed length n = 1 ~ 4000
National character varying (n)
Ntext
Unicode data with a variable length. The maximum length is-1 characters.
National text
Binary (n)
Binary data of fixed length, where n ranges from 1 ~ The storage space is n + 4 bytes.
Varbinary (n)
Variable Length binary data, n = 1 ~ 8000
Binary varying (n)
Tmage
Variable-length binary data; Size: 0 ~ 231-1
Note:
1) For numeric dataTypeWidth (scale) refers to the number of digits after the decimal point, while precision refers to the number of digits including the decimal point that can be stored.
2) the storage width of money and small money is 4.
3) The timestamp column value is automatically updated when each row is updated. The timestamp Column cannot be a part of a keyword or keyword.
4) Unique identifier dataTypeArithmetic Operators (such as + and-) cannot be used.TypeOnly equal comparison operations can be used. Unicode is the standard for consistent data storage for all character sets. It uses twice the storage space of Non-Unicode data storage.