MSSQL field Type--(reprint)

Reprint: http://www.cnblogs.com/skylaugh/archive/2006/07/31/464107.html

A data type is a property of the data that represents the type of information represented by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the categories and names of the data types defined are more or less different. SQL Server provides 25 types of data:
· binary[(N)]
· varbinary[(N)]
· char[(N)]
· varchar[(N)]
· nchar[(N)]
· nvarchar[(N)]
· Datetime
· smalldatetime
· decimal[(P[,s])
· numeric[(P[,s])
· float[(N)]
· Real
· Int
· Smallint
· Tinyint
· Money
· SmallMoney
· Bit
· Cursor
· Sysname
· Timestamp
· uniqueidentifier
· Text
· Image
· Ntext
(1) Binary data type

Binary data includes 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. where n is the range of values 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. where n is the range of values 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, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the image data type using BMP, Tief, GIF, and JPEG formats.
(2) Character data type

The types of character data include Char,varchar and text
Character data is data that is any combination of letters, symbols, and numbers.
VARCHAR is a variable-length character data whose length does not exceed 8KB. Char is a fixed-length character data with a length of up to 8KB. ASCII data over 8KB can be stored using the text data type. For example, because HTML documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the text data type.
(3) Unicode data type

Unicode data types include Nchar,nvarchar and ntext
In Microsoftsqlserver, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server installation, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column. 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 this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the nchar character type, and you can store up to 4,000 characters at this 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 include datetime and smalldatetime two types
Date and time data types are made up of valid dates and times. For example, valid date and time data includes "4/01/9812:15:00:00:00pm" and "1:28:29:15:01am8/17/98". The previous data type is the date before, the time in the last data type is a moment before, and the date in the back. In Microsoftsqlserver, when the date and time data types include datetime and smalldatetime two types, the stored date range starts from January 1, 1753 and ends on 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 command for formatting the date is as follows:
setdateformat{format| @format_var |
Where the format| @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MyD, and Dym. By default, the date format is MDY.
For example, when the SETDATEFORMATYMD is executed, the date is formatted as a month and day form; When Setdateformatdmy is executed, the date is formatted as Sun and moon Years
(5) numeric data type

Numeric data contains only 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 Micrsoftsqlserver, the data types stored in 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 2147483648 to 2147483647 (each value requires 4 bytes of storage space). When you use the smallint data type, the range of stored data ranges from 32768 to 32767 (each value requires 2 bytes of storage space). When you use the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space).
The exact data type of the Louboutin data in SQL Server is decimal and numeric. The amount of storage space that this data occupies is determined by 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-third of this score is recorded. 3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.
(6) Currency data indicates the number of positive or negative currencies.

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

(7) Special data types

Special data types include 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. For example, ask if a client request for each visit can be stored in a column of this 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 this data type in the customer Identification Number column can differentiate between different customers.
2. User-defined data types

User-defined data types are based on the data types provided in Microsoftsqlserver. You can use a user-defined data type 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. 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 can be used to create user-defined data types. Its grammatical form is as follows:
sp_addtype{type},[,system_data_bype][, ' Null_type ']
Where type is the name of the user-defined data type. System_data_type are system-supplied data types, such as decimal, Int, Char, and so on. Null_type indicates how the data type handles null values and must be enclosed in single quotes, such as ' NULL ', ' notnull ', or ' nonull '.
Example:
Usecust
EXECSP_ADDTYPESSN, ' Varchar ', ' notnull '
Creates a user-defined data type SSN, which is based on a system data type that is variable to 11 characters, and does not allow nulls.
Example:
Usecust
Execsp_addtypebirthday,datetime, ' Null '
Creates a user-defined data type of birthday, which is based on the system data type that is DateTime and allows null.
Example:
Usemaster
Execsp_addtypetelephone, ' varchar ', ' notnull '
Eexcsp_addtypefax, ' 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 '}.
Example:
Usemaster
Execsp_droptype ' SSN '
Note: This 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 also bound on it.
Description of the field type for SQL Server

The following is a description of the field type for SQL SERVER7.0 or later. For a description of the field type for SQL SERVER6.5, refer to the instructions provided by SQL Server.

　　

Field type	Describe
Bit	Integer numbers of 0 or 1
Int	Integer numbers 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
Decimal	The number of fixed and significant digits from -10^38 to 10^38-1
Numeric	Synonyms for decimal
Money	Currency 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, Min. 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. unit of time in minutes
Timestamp	Timestamp, unique number of a database width
uniqueidentifier	Global Unique identifier GUID
Char	Fixed-length non-Unicode character data with a maximum length of 8000
varchar	Variable length non-Unicode character data up to a maximum of 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 with a maximum length of 2^31-1 (2G)
Binary	Fixed-length binary data with a maximum length of 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)

MSSQL field Type--(reprint)