Sqlserve data Type specific explanation

Decimal exact numeric type
The decimal data type can be used to store numeric data of a fixed precision and range from 10 to 38 powers-1 to 10 of 38 power-1. When you use such a 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 precise numerical type
The numeric data type is similar to decimal.

SmallMoney currency type
The smallmoney data type is used to represent money and currency values. Such data types can store data from 214748.3648 to 214748.3647, accurate to one of the most extreme of currency units.

Money currency type
The Money data type is used to denote monetary and currency values. Such data types can store data from 922 billion to 922 billion. One of the most accurate to the monetary unit

float Approximate numerical type
The float data type is an approximate numeric type that is used by floating point numbers.

It is said that the floating-point number is approximate, because not all the numbers within its range can be accurately expressed.

Floating-point numbers can be random numbers from -1.79E+308 to 1.79E+308

Real approximate numeric type
Real data types are approximate numeric types like floating-point numbers. It can represent a floating-point number between -3.40E+38 and 3.40E+38.

smalldatetime Date and Time type
The smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, accurate to one minute

DateTime Date-Time type
The datetime data type is used to represent the date and time.

Such data types store all date and time data from January 1, 1753 to December 31, 9999, with a precision of 300 minutes or 3.33 milliseconds.

Cursor Special Data type
The cursor data type is a special type of data that includes a reference to a cursor.

Such data types are used in stored procedures and cannot be used when creating tables

Timestamp Special Data type
The timestamp data type is a special type of data that is used to create a database-wide unique digital. Only one timestamp column can be in a table. Every time you insert or change a row. The value of the timestamp column changes.

Although it has "time" in its name, 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, the GUID. The GUID is truly globally unique. This number has almost no chance of being rebuilt in another system.

Ability to use the NEWID function or convert a string to a unique
Identifier to initialize the column with a unique identifier.

char character type
The char data type is used to store fixed-length, non-uniform encoded data of specified lengths.

When you define a column for this type, you must specify a column length.

When you always know the length of the data to be stored. This data type is very useful.

Like what. When you store data by zip code plus 4 character formatting. You know, it always takes 10 characters.

This data type has a maximum column width of 8,000 characters.

varchar character varchar data types, like char types, are used to store non-uniform encoded character data. Unlike the char type, this data type is variable length.

When you define a column for that data type. You want to specify the maximum length of the column. The biggest difference between it and the char data type is that the length of the stored data is not the length of the column.

Text character type
The text data type is used to store a large number of non-uniform encoded character data.

Such data types can have a maximum of 231-1 or 2 billion characters.

NCHAR Unified coded Character type
The nchar data type is used to store fixed-length, uniformly encoded character data.

Unified encoding uses a double-byte structure to store every character, rather than a single byte (in normal text).

It agrees with a large number of extended characters. This data type can store 4000 characters, which is added one more times on the byte space used. The

nvarchar    the Uniform encoded character type      the
nvarchar data type is used as the uniformly encoded character data of the variable length. This data type can store 4000 characters, using a byte space that is added one more times. The

ntext    the Uniform encoded character type      the
ntext data type is used to store a large number of uniformly encoded character-type data. Such data types can store 230-1 or nearly 1 billion characters, and the byte space used adds one-times

binary    binary data type    
Binary data types are used to store fixed-length binary data up to 8000 bytes long. You should use such a data type when the contents of the input table are close to the same length. The

varbinary     binary data type     the
varbinary data type is used to store variable-length binary data up to 8000 bytes long. When the content size of the input table is variable. You should use such data type   

image    binary data type     
Image The data type is used to store variable-length binary data up to 231-1 or approximately 2 billion bytes   



　　---------------------------------------------------
(1) binary data type  
binary data contains binary, Varbinary, and Image
Binary data types can be either fixed-length (binary) or variable-length.
binary[(N)] is a fixed n-bit binary data. The range of values for n is from 1 to 8000.

The size of its storage scenting is n + 4 bytes.
varbinary[(N)] is a binary data of n-bit variable length. The range of values for n is from 1 to 8000.

The size of its storage scenting is n + 4 bytes, not n bytes.
The data stored in the Image data type is stored as a bit string and is not interpreted by SQL Server. Must be interpreted by the application. Like what. Applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

(2) Character data type

The type of character data contains Char. Varchar and Text
Character data is a random combination of letters, symbols, and numbers.
Varchar is a variable-length character data. Its length does not exceed 8KB. Char is a fixed-length character data. It is up to 8KB in length.

More than 8KB of ASCII data can be stored using the text data type. For example, because Html documents are all ASCII characters. And in normal cases the length exceeds 8KB. So these documents can be stored in SQL Server with the Text data type.

(3) Unicode data type

Unicode data types contain Nchar,nvarchar and ntext
In Microsoft SQL Server. Traditional non-Unicode data types agree to use characters defined by a specific character set.

During the SQL Server installation process. Agree to choose a character set. With Unicode data types, columns can store characters that are defined by the Unicode standard, regardless of what they are.

In the Unicode standard, all characters defined in various character sets are included. With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types.
In SQL Server. Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with such character types can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, which can store up to 4,000 characters. You should use the Nchar character type when the length of the column is constant. can store up to 4,000 characters at a time. When you use the Ntext data type, the column can store more than 4,000 characters.

(4) Date and time data type

Date and time data types contain two types of Datetime and smalldatetime
Date and time data types are made up of valid dates and times. Like what. Valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before. The time in the latter one data type is a moment before. Date in the rear. In Microsoft SQL Server, date and time data types are included in datetime and smalldatetime two types. The date range stored is from January 1, 1753 to the end of December 31, 9999 (each value requires 8 bytes of storage).

When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).
The format of the date can be set. The commands for formatting dates are as follows:
Set DateFormat {format | @format _var|
Middle, Format | @format_var is the order of the dates.

Valid references include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after running Set DateFormat YMD. The format of the date is the month and day form, after the Set DateFormat DMY is run. The date is in the form of the sun and moon

(5) Numeric data types

Numeric data only includes numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers
Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967.

In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint.

The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the tinyint data type stores the data. The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space).

When you use the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage space). When using the tinyint data type. The range of stored data is from 0 to 255 (1 bytes of storage per value required).
The exact data type for the data in SQL Server is Decimal and Numeric. The amount of storage space for such data is determined by the number of digits after the number of bits in the data.
In SQL Server, the data type of the approximate decimal data is Float and Real.

For example, one of the three points is recorded as a score. 3333333, which can be expressed accurately when using approximate data types.

Therefore, the data retrieved from the system may not be the same as the data stored in the column.

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

In Microsoft SQL Server, the data type of the currency data is money and smallmoney
The Money data type requires 8 bytes of storage. The SmallMoney data type requires 4 bytes of storage.

(7) Special data types

Special data types contain data types that were not previously mentioned. There are 3 special data types, namely Timestamp, Bit and uniqueidentifier.

The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timestamp data is not related to inserting data or date and time.
The Bit is made up of 1 or 0.

When representing true or false, on, or off, use the Bit data type.

Like what. Ask if a client request for each visit can be stored in a column of such data type.
The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness.

For example, using such a data type in the customer Identification Number column can vary between customers.

2. User-defined data types

User-defined data types are based on the data types that are available in Microsoft SQL Server. When you must store the same data type in several tables, and to ensure that the columns have the same data type, length, and nullability. Ability to 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 you create a user-defined data type, you must provide three numbers: the name of the data type, the system data type that you are basing on, and the nullability of the data type.

(1) Create a user-defined data type

You can use Transact-SQL statements to create user-defined data types. System stored procedures sp_addtype be able to create user-defined data types. Its grammatical form 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, Char, and so on. Null_type indicates how the data type handles null values. It must be caused by a single-lead. such as ' null ', ' NOT null ' or ' nonull '.
Examples:
Use Cust
Exec sp_addtype ssn, ' Varchar (one) ', ' not Null '
Create a user-defined data type SSN. Its based system data type is a variable length of 11 characters and does not agree with NULL.
Examples:
Use Cust
Exec sp_addtype birthday,datetime, ' Null '
Creates a user-defined data type of birthday, which is based on the system data type DateTime, which agrees to null.
Examples:
Use master
Exec sp_addtype telephone, ' varchar ', ' not Null '
Eexc sp_addtype Fax, ' varchar ', ' Null '
Create two data types, telephone and fax

(2) Delete user-defined data types

Can be deleted when the user-defined data type is not required. The command to delete a user-defined data type is sp_droptype {' type '}.

Examples:
Use master
Exec sp_droptype ' ssn '
Note: A user-defined data type cannot be deleted when a column in a table is also using a user-defined data type, or if a default or rule is bound on it.

Description of the field type for SQL Server

The following is a description of the field type for SQL SERVER7.0 above version number.

For a description of the field type for SQL SERVER6.5, please refer to the instructions provided by SQL Server.

Descriptive Description of field type
Integer digits of bit 0 or 1
int integer number 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)
tinyint integer numbers from 0 to 255

Number of fixed and significant digits from -10^38 to 10^38-1 decimal
Synonyms for numeric decimal

Money data from -2^63 ( -922,337,203,685,477.5808) to 2^63-1 (922,337,203,685,477.5807), Min. currency Unit 10 per thousand
SmallMoney Currency data from -214,748.3648 to 214,748.3647. Minimum currency unit 10 per thousand

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 data from January 1, 1753 to 9999 12th 31. The minimum time unit is 3% seconds or 3.33 milliseconds
smalldatetime date and time data from January 1, 1900 to June 6, 2079, Min. time unit is minutes

Timestamp time stamp. A unique number for a database width
uniqueidentifier Global Unique identifier GUID

Char fixed-length non-Unicode character data up to a maximum of 8000
varchar variable-length non-Unicode character type data. Maximum length is 8000
Text variable length non-Unicode character data with a maximum length of 2^31-1 (2G)

NChar fixed-length Unicode character data with a maximum length of 8000
nvarchar variable-length Unicode character data up to a maximum of 8000
ntext variable-length Unicode character data, Max. 2^31-1 (2G)

Binary fixed-length binary data with a maximum length of 8000
varbinary variable-length binary data up to a maximum of 8000
Image variable length binary data with a maximum length of 2^31-1 (2G)


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Bit
1 bits with a value of 0 or 1
Int
Integer
4 bytes with a value of -2^31~2^31-1
Smallint
2 bytes. Value is -2^15~2^15-1
Tinyint
1 bytes with a value of 0~255
Decimal (P,s)
Digital data with a fixed precision of p and a width of s
Numeric
Money
8 bytes, which holds the currency type. Value is -2^63~2^63-1
Small Money
4 bytes, holding the currency type with a value of -214748.3648~+214748.3647 approximate numeric data type
Float (N)
n between the 1~24. 4 bytes. 7-bit Accuracy
N=1~7 for Real
n between 25~53, 8 byte, 15 bit precision
=8~15 to float
Datetime
8 bytes, describing the date and time of day. The accuracy of the value is 1/300 seconds
smalldatetime
4 bytes, describing the date and time of day, with a precision of minutes
Cursor
A reference to a cursor
Timestamp
8 bytes, the only data stored within the database
uniqueidentifier
16 bytes, which holds 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.

Maximum length is 231-1 characters
Nchar
Fixed-length Unicode string n=1~4000
National character (n),
National char (N)
Nvarchar
Fixed-length Unicode string n=1~4000
national character varying (n)
Ntext
Variable-length Unicode data with a maximum length of 230-1 characters
National text
Binary (N)
Fixed-length binary data, n between 1~8000, storage space of n+4 bytes
Varbinary (N)
Variable-length binary data. n=1~8000
Binary varying (n)
Tmage
Variable-length binary data. Size is 0~231-1
Attention:
1) For numeric data types, the width (scale) refers to the number of digits stored after the decimal point. Precision (precision) is the number of digits that can be stored, including decimal points.
2) The storage width of money and small money is 4.
3) The timestamp column value is actively updated by the system itself when each row is updated. The timestamp column cannot be part of a keyword or keyword.
4) The unique identity data type cannot use arithmetic operators (such as +,-etc.). Such data types can only be manipulated using equality. Unicode is the standard for uniformly storing data for all character sets. It uses two times as much storage space as non-Unicode data stores.

Bit integral type
The bit data type is integer and its value can be only 0, 1, or null.

Such data types are used to store data that has only two possible values, such as Yes or No, True or false, on, or off.

Note: A data type that is very space-saving, assuming that the requirements can be met as much as possible.

tinyint integral type
The tinyint data type can store integers from 0 to 255.

It is useful when you are only going to store a limited number of values. Such a data type occupies 1 bytes in the database.

Note: Assuming that the bit type is too monotonous to meet your needs, you can consider using the tinyint type, which is relatively safe and does not accept embedding of malicious script content.

smallint integral type
The smallint data type is capable of storing integers between 15 powers (-32768) from 2 to 15 powers (32767) of 2. Such data types are useful for storing numeric data that is often scoped to a specific range. Such data types occupy 2 bytes of space in the database.

Note: Assuming that the tinyint type is too monotonous to meet your needs, you can consider using the smallint type, which is relatively safe and does not accept embedding of malicious script content.

int integral type
The int data type can store integers from 2 to 31 powers (-2147483648) to 2 to 31 powers (2147483 647). Nearly all numeric data stored in a database can be used in such a data type.

This data type occupies 4 bytes in the database.

Note: Assume that smallint is not able to meet your needs. You might consider using a larger int type.

Sqlserve data Type specific explanation