SqlServer database data unit type

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

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

Bit integer
BitDataTypeIt is an integer and its value can only be 0, 1, or null. ThisDataTypeUsed to store only two possible valuesData, Such as Yes, No, True, False, On, or Off.

Note: This is a space-saving method.DataTypeIf you can meet your needs, use it as much as possible.

Tinyint integer
TinyintDataTypeIt can store integers ranging from 0 to 255. It is useful when you only plan to store a limited number of values. ThisDataTypeInDataLibraryOccupies 1 byte.

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
SmallintDataTypeIt can store integers between the 15 power (-32768) of-2 and the 15 power (32767) of 2. ThisDataTypeStore some numeric types that are often limited to a specific rangeDataVery useful. ThisDataTypeInDataLibraryOccupies 2 bytes of space.

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
IntDataTypeIt can store integers between the 31 Power (-2147483648) of-2 and the 31 Power (2147483 647) of 2. StoreDataLibraryAlmost all numericDataYou can use thisDataType. ThisDataTypeInDataLibraryOccupies 4 bytes.

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

Decimal exact numeric type
DecimalDataTypeIt can be used to store the fixed precision and range of the 38 power-1 from-10 to 10.Data. ThisDataTypeThe 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
NumericDataTypeSimilar to decimal.

Smallmoney currency type
SmallmoneyDataTypeUsed to indicate the value of money and currency. ThisDataTypeCan store data from-214748.3648 to 214748.3647Data, Accurate to currencyUnitOne thousandth

Money currency type
MoneyDataTypeUsed to indicate the value of money and currency. ThisDataTypeCan store data from-922 billion to 922 billionData, Accurate to currencyUnitOne thousandth

Float approximate numeric type
FloatDataTypeIs 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
RealDataTypeSimilar 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
SmalldatetimeDataTypeUsed to indicate the date and time from January 1, January 1-20, 1900 to January 1, June 6, accurate to one minute

Datetime type
DatetimeDataTypeUsed to represent the date and time. ThisDataTypeStore all dates and times from January 1Data, Accurate to 1/300 seconds or 3.33 milliseconds.

Cursor specialDataType
CursorDataTypeIs a specialDataTypeIt contains a reference to the cursor. ThisDataTypeUsed in the stored procedure and cannot be used when creating a table

Special timestampDataType
TimestampDataTypeIs a specialDataTypeTo createDataLibraryThe only digital in the 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. InDataLibraryThe timestamp value is unique.

Uniqueidentifier specialDataType
UniqueidentifierDataTypeIt 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
CharDataTypeUsed to store fixed-length non-uniform encoding typeData. When defining a column to thisTypeYou must specify the column length. When you always know what to storeDataThe lengthDataTypeVery useful. For exampleDataYou know, it always takes 10 characters. ThisDataTypeThe column width cannot exceed 8000 characters.

Varchar simplified varcharDataType, Same as charTypeThe same is used to store non-uniform encoding characters.Data. Unlike charDataTypeVariable Length. When Column 1 is defined as thisDataTypeYou must specify the maximum length of the column. It corresponds to charDataTypeThe biggest difference is that the storage length is not the column length,DataLength.

Text character type
TextDataTypeUsed to store a large number of non-uniform encoding charactersData. ThisDataTypeA maximum of 2 billion to characters can be entered.

Nchar unified encoding type
NcharDataTypeUsed to store fixed-length, unified encoding typeData. 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. ThisDataType4000 characters can be stored, doubling the byte space used.

Nvarchar uniform encoding type
NvarcharDataTypeUsed as a variable-length uniform encoding typeData. ThisDataType4000 characters can be stored, doubling the byte space used.

Ntext unified encoding type
NtextDataTypeUsed to store a large number of uniform encoding typesData. ThisDataTypeIt can store 230-1 or nearly 1 billion characters and doubles the byte space used

BinaryDataType
BinaryDataTypeUsed to store binary data with a fixed length of up to 8000 bytesData. When the content in the input table is close to the same length, you should use thisDataType.

Varbinary binaryDataType
VarbinaryDataTypeUsed to store binary data that can be up to 8000 bytes longData. When the content of the input table is variable, you should use thisDataType

Image BinaryDataType
ImageDataTypeUsed to store variable-length binary dataData, Up to 231-1 or approximately 2 billion bytes



---------------------------------------------------
(1) binaryDataType
BinaryDataIncluding Binary, Varbinary, and Image
BinaryDataTypeIt 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 string with n-bit varying length.Data. The value range of n is from 1 to 8000. The memory size is n + 4 bytes, not n Bytes.
In the ImageDataTypeStored inDataIt is stored in bits, not explained by SQL Server, but must be explained by applications. For example, applications can use BMP, TIEF, GIF, and JPEG formatsDataStored in ImageDataType.

(2) charactersDataType

CharacterDataOfTypeIncluding Char, Varchar, and Text
CharacterDataIs composed of any combination of letters, symbols, and numbers.Data.
Varchar is a variable-length character.DataThe length cannot exceed 8 KB. Char is a fixed-length characterDataThe maximum length is 8 KB. More than 8 KB ASCIIDataYou can use TextDataTypeStorage. For example, because all Html documents are ASCII characters and the length exceeds 8 KBDataTypeStored in SQL Server.

(3) UnicodeDataType

UnicodeDataTypeIncluding Nchar, Nvarchar, and Ntext
In Microsoft SQL Server, traditional non-UnicodeDataTypeCharacters defined by a specific character set are allowed. You can select a character set during SQL Server installation. Use UnicodeDataTypeThe column can store any characters defined by the Unicode standard. The Unicode Standard contains all characters defined in various character sets. Use UnicodeDataType, The solution is to use non-UnicodeDataTypeThe size of the shard occupied is twice.
In SQL Server, UnicodeDataUse Nchar, Nvarchar, and NtextDataTypeStorage. 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 is usedDataTypeThe column can store more than 4000 characters.

(4) Date and TimeDataType

Date and TimeDataTypeIncluding Datetime and SmalldatetimeType
Date and TimeDataTypeIt consists of a valid date and time. For example, valid date and timeDataIncluding "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01 AM 8/17/98 ". PreviousDataTypeIs the date before, the time is in the nextDataTypeIt is the time before and the date is later. In Microsoft SQL Server, the date and timeDataTypeIncluding 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 SmalldatetimeDataTypeThe 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) NumbersDataType

NumberDataContains only numbers. NumberDataTypeIncluding 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 ServerDataTypeIs Int, Smallint, and Tinyint. IntDataTypeStorageDataThe value range is greater than Smallint.DataTypeStorageDataAnd Smallint dataTypeStorageDataThe value range is greater than Tinyint.DataTypeStorageData. Use IntDataBusi StorageDataThe range is from-2 147 483 648 147 to 2 483 647 (each value requires 4 bytes of storage space ). Use SmallintDataTypeTime, storageDataRange:-32 768 to 32 767 (each value requires 2 bytes of storage space ). Use TinyintDataTypeTime, storageDataThe range is from 0 to 255 (each value requires 1 byte storage space ).
Precise XiaoyanDataIn SQL ServerDataTypeDecimal and Numeric. ThisDataThe storage space occupied is based onDataAfter the number of digits to determine.
In SQL Server, approximate decimal placesDataOfDataTypeFloat and Real. For example, the 1/3 score is recorded. 3333333. When approximation is usedDataTypeCan be accurately expressed. Therefore,DataMay be stored in this columnDataNot exactly.

(6) CurrencyDataIndicates the number of positive or negative currencies.

In Microsoft SQL ServerDataOfDataTypeIt's Money and Smallmoney.
MoneyDataType8 storage bytes, SmallmoneyDataTypeFour storage bytes are required.

(7) SpecialDataType

SpecialDataTypeIncluding those not mentioned earlierDataType. SpecialDataTypeThere are three types: Timestamp, Bit, and Uniqueidentifier.
Timestamp is used to indicate the sequence of SQL Server activities, in the binary projection format. TimestampDataAnd insertDataOr there is no relationship between the date and the time.
Bit is composed of 1 or 0. When it indicates true or false, ON, or OFF, use BitDataType. For example, to check whether each client request can be stored inDataType.
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, useDataTypeDifferent customers can be differentiated.

2. User-DefinedDataType

User-DefinedDataTypeBased onDataType. When several tables must store the sameDataTypeTo ensure that these columns have the sameDataTypeYou can use the customDataType. For example, you can define a type called postal_codeDataType, Which is based on CharDataType.
When you create a user-definedDataTypeThree numbers are required:DataTypeName of, the system based onDataTypeAndDataTypeCan be empty.

(1) create a user-definedDataType

Create a user-definedDataTypeYou can use a Transact-SQL statement. The system stored procedure sp_addtype can be used to create user-definedDataType. The syntax format is as follows:
Sp_addtype {type}, [, system_data_bype] [, 'null _ type']
Specifically, type is user-definedDataType. System_data_type is provided by the system.DataTypeSuch as Decimal, Int, and Char. Null_type indicatesDataTypeHow 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-definedDataTypeSsn, ITS Based SystemDataTypeIt is a string of 11 characters and cannot be blank.
Example:
Use cust
Exec sp_addtype birthday, datetime, 'null'
Create a user-definedDataTypeBirthday, which is based on the systemDataTypeIt 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 twoDataTypeThat is, telephone and fax

(2) Delete a user-definedDataType

When the User-DefinedDataTypeYou can delete an instance if it is not needed. Delete a user-definedDataTypeThe command is sp_droptype {'type '}.
Example:
Use master
Exec sp_droptype 'ssn'
Note: When the columns in the table are still using the User-DefinedDataTypeOr when there is a default or rule bound to it, this User-DefinedDataTypeCannot be deleted.

SQL SERVER fieldTypeDescription

The following are SQL SERVER7.0 and later fields.TypeDescription. SQL SERVER6.5 FieldTypeFor 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

Money from-2 ^ 63 (-922,337,203,685,477.5808) to 2 ^ 63-1 (922,337,203,685,477.5807)Data, Min currencyUnit10 thousandth
Smallmoney from-214,748.3648 to 214,748.3647Data, Min currencyUnit10 thousandth

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 from January 1Data, Minimum timeUnit3% seconds or 3.33 milliseconds
Smalldatetime: Date and time from January 1, January 1-20, 1900 to January 1, June 6Data, Minimum timeUnitMinutes

Timestamp,DataLibraryUnique number in width
Uniqueidentifier GUID

Char fixed length non-Unicode Character TypeDataThe maximum length is 8000.
Varchar variable length non-Unicode Character TypeDataThe maximum length is 8000.
Text extended non-Unicode Character TypeData, The maximum length is 2 ^ 31-1 (2G)

Nchar fixed-length Unicode Character TypeDataThe maximum length is 8000.
Nvarchar variable-length Unicode Character TypeDataThe maximum length is 8000.
Ntext variable-length Unicode Character TypeData, The maximum length is 2 ^ 31-1 (2G)

Binary fixed-length binaryDataThe maximum length is 8000.
Varbinary variable-length binaryDataThe maximum length is 8000.
Image variable-length binaryData, The maximum length 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)
NumberData, Fixed precision is P, width is 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 valuesDataType
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, stored inDataLibraryUniqueData
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 in server code pageData. 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 with Variable LengthData, The maximum length is-1 Characters
National text
Binary (n)
Fixed-length binaryData, N in 1 ~ The storage space is n + 4 bytes.
Varbinary (n)
Variable-length binaryData, N = 1 ~ 8000
Binary varying (n)
Tmage
Variable-length binaryData, The size is 0 ~ 231-1
Note:
1) For the numeric typeDataTypeWidth (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 IDDataTypeArithmetic Operators (such as + and-) cannot be used.DataTypeOnly equal comparison operations can be used. Unicode is the consistent storage of all character sets.Data. It must use twice the non-UnicodeDataStorage space.