Sqlserve Data Type Details, sqlserve Data Type

Sqlserve Data Type Details, sqlserve Data Type

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

Numeric exact numeric type
The numeric data type is similar to decimal.

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

Money currency type
The money data type is used to indicate money and currency values. This data type can store data from-922 billion to 922 billion, 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.

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.

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, accurate to 3.33 seconds or milliseconds.

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 be 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 varchar data type, which is the same as char type, 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 uniform 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 data type
The binary data type is used to store binary data that can be up to 8000 bytes in length. This data type should be used when the input table content is close to the same length.

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.



---------------------------------------------------
(1) binary data type
Binary data includes Binary, Varbinary, and Image
The Binary data type can be either fixed-length (Binary) or 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.
The data stored in the Image data type is stored as a bit string, which is not explained by SQL Server and must be explained by the application. For example, an application can store data in the Image data type in BMP, TIEF, GIF, and JPEG formats.

(2) character data type

Character data types include 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. ASCII data larger than 8 KB can be stored as Text data. For example, because all Html documents are ASCII characters and generally exceed 8 kb in length, these documents can be stored in SQL Server as Text data.

(3) Unicode Data Type

Unicode data types include Nchar, Nvarchar, and Ntext
In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by specific character sets. You can select a character set during SQL Server installation. The Unicode data type allows columns to store any characters defined by the Unicode standard. The Unicode Standard contains all characters defined in various character sets. When Unicode data types are used, the odds are two times the bytes used by non-Unicode data types.
In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data. Columns stored with this character type can store characters in multiple character sets. When the length of a column changes, the Nvarchar character type should be used. At this time, a maximum of 4000 characters can be stored. When the column length remains unchanged, the Nchar character type should be used. Similarly, a maximum of 4000 characters can be stored. When the Ntext data type is used, this column can store more than 4000 characters.

(4) Date and Time Data Types

Date and Time data types include Datetime and Smalldatetime.
The date and time data types are composed of 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 ". The previous data type is before the date, the time is before the last data type is before the time, and the date is after. In Microsoft SQL Server, the date and time data types include Datetime and Smalldatetime. The stored date range starts from January 1, January 1, 1753, end on April 9, December 31, 9999 (each value requires 8 storage bytes ). When the Smalldatetime data type is used, the stored date range is from January 1, January 1, 1900 to January 1, December 31, 2079 (each value requires four 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 Data Types

Numeric data only contains numbers. Numeric data types include 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, integer data types are Int, Smallint, and Tinyint. The range of Int data type stored data is greater than that of Smallint data type stored data, while that of Smallint data type stored data is greater than that of Tinyint data type stored data. 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 ). When the Smallint data type is used, the data storage range is from-32 768 to 32 767 (each value requires 2 bytes of storage space ). When the Tinyint data type is used, the data storage range is from 0 to 255 (each value requires 1 byte storage space ).
The exact data types in SQL Server are Decimal 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.
In SQL Server, the Data Types of approximate decimal data are Float and Real. For example, the 1/3 score is recorded. 3333333. It can be accurately expressed when an approximate data type is used. 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.

In Microsoft SQL Server, the Data Types of currency data are Money and Smallmoney.
The Money data type requires 8 storage bytes, And the Smallmoney data type requires 4 Storage bytes.

(7) Special Data Types

Special data types include data types not mentioned previously. There are three special data 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. The Bit data type is used when it indicates true or false, ON, or OFF. For example, you can query whether a client request for each access can be stored in a column of 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, you can use this data type in the Customer ID column to distinguish different customers.

2. User-Defined Data Types

The user-defined data type is based on the data type provided in Microsoft SQL Server. When several tables must store the same data type, and to ensure that these columns have the same data type, length, and can be empty, you can use user-defined data types. For example, you can define a data type called postal_code, which is based on the Char data type.
When creating a user-defined data type, three data types must be provided: the data type name, the system data type based on, and the data type can be empty.

(1) create a user-defined data type

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

(2) Delete user-defined data types

You can delete a data type that is not required. The command for deleting user-defined data types is sp_droptype {'type '}.
Example:
Use master
Exec sp_droptype 'ssn'
Note: When columns in a table are still using user-defined data types, or are bound with default or rules, the user-defined data types cannot be deleted.

Description of SQL SERVER Field Types

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

Field type description
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, which stores the currency type; value range:-2 ^ 63 ~ 2 ^ 63-1
Small money
4 bytes, which stores the currency type. The value ranges from-214748.3648 ~ + 214748.3647 approximate numeric data types
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 data types, width (scale) refers to the number of digits stored after the decimal point, while precision refers to the number of digits that can be stored including 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. The timestamp Column cannot be a part of a keyword or keyword.
4) Arithmetic Operators (such as + and-) cannot be used to uniquely identify data types. Such data types can only use equal comparison operations. Unicode is the standard for consistent data storage for all character sets. It uses twice the storage space of Non-Unicode data storage.

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, 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
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.

Note: If the bit type is too monotonous to meet your needs, you can consider using the tinyint type, because this type is relatively secure and does not accept the embedding of malicious script content.

 

Smallint integer
The smallint data type can store integers between the 15 power (-32768) of-2 and the 15 power (32767) of 2. 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.

Note: If the tinyint type is too monotonous to meet your needs, you can use the smallint type because it is relatively secure and does not accept malicious script content embedding. 

 

Int integer
The int data type can store integers from the 31 Power (-2147483648) of-2 to the 31 Power (2147483 647) of 2. This type can be used for almost all numeric data stored in the database. This data type occupies 4 bytes in the database.

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