SQL Server Data Types

Bit (or dollar) bit:
The smallest unit for computer recording, storage, expression, and transmission of information. It consists of a single-digit binary value "0" or "1.
Byte:
Byte is the most basic unit for computer recording, storage, expression, and transmission of information. Whether on the network or on the computer's CPU, memory, hard disk, CD, U disk and other storage media, the measured unit is measured in bytes.
One byte is equal to the binary value of the 8-bit length.
In ASCII code, an English letter (case-insensitive) occupies the space of one byte, and a Chinese character occupies the space of two bytes.
Symbol: English Punctuation occupies one byte, and Chinese Punctuation occupies two bytes. For example, the English period "." occupies the size of 1 byte and the Chinese period "." The size of 2 bytes.
ASCII Code :
ASCII code: American Standard Code for information interchange. It has been set as an international standard by the International Organization for Standardization (ISO), known as the ISO 646 standard. It is applicable to all Latin characters, including 7-digit ASCII code and 8-digit ASCII code. ASCII code was proposed in 1968 to standardize data transmission in different computer hardware and software systems. It is used in most computers and all personal computers. The ASCII code is divided into two sets: The standard ASCII code with 128 characters and the expanded and ASCII code with an additional 128 characters.
Because one binary number can represent (2 ^ 0) two states: 0 and 1, and two binary numbers can represent (2 ^ 1) = four states: 00, 01, 10, 11, and so on. The seven-digit binary number can represent 128 States (2 ^ 7). Each State is uniquely encoded as a seven-digit binary code, corresponds to one character (or control code), which can be arranged into a decimal number ranging from 0 ~ 127. Therefore, the 7-bit ASCII code is encoded using the seven-bit binary number, which can represent 128 characters.
0th ~ 32 and 127th (34 in total) are control characters or communication special characters, such as control characters: LF (line feed), Cr (Press ENTER), FF (page feed), del (delete), BEL (zhenling); Communication special characters: Soh (Text header), EOT (Text tail), Ack (confirmation), etc;
33rd ~ 126 (94 in total) is a character, of which 48th ~ Number 57 is 0 ~ 9. 10 Arabic numerals; 65 ~ The 90 is 26 uppercase English letters, 97 ~ There are 26 lower-case English letters, and the remaining are some punctuation marks and operator numbers. A data type is a type of data attribute and a type of information represented by data. Any language has its own data types. SQL Server provides 25 inherent data types.

SQL Server Data Type list
· 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 is represented by a hexadecimal number. Binary, varbinary, and image data types can be used for storage.
· Binary data types with a fixed length (up to 8 K.

Binary [(n)]
A fixed length of n Bytes of binary data. N must be from 1 to 8,000. The storage space is n + 4 bytes.

· Varbinary binary data type with a variable length (up to 8 K.

Varbinary

· Image is used to store binary data with a variable length of more than 8 KB.
Unless the data length exceeds 8 KB, The varbinary type is generally used to store binary data. It is generally used to store Microsoft Word documents, Microsoft Excel workbooks, images containing bitmap, GIF files, and JPEG files.
The data stored in the image data type is stored as a bit string, which is not explained by SQL Server and must be stored by the applicationProgram. 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 consists of letters, symbols, and numbers. Char, varchar, and text data types are used for storage.
· Char character data type with a fixed length (up to 8 KB.

Char [(n)]
Character data with a fixed length of n Bytes and is not Unicode. N must be between 1 and 8,000
Between. The storage size is n Bytes. The synonym for char in the SQL-92 is character.

· Varchar variable length (up to 8 KB) character data type.

Varchar [(n)]
Variable-length and non-UNICODE character data with a length of n Bytes. N must be between 1 and 8,000
Between. The storage size is the actual length of the input data bytes, rather than n Bytes. Data words entered
The length can be zero. The synonym for varchar in the SQL-92 is Char varying or character varying.

· Columns of the text data type can be used to store ASCII characters larger than 8 KB.
For example, because HTML documents are composed of ASCII characters and generally longer than 8 KB, they should be stored in the text column on SQL server before being viewed in a browser.

3. Unicode Data Type
The Unicode data type allows a column to store any character defined by the Unicode standard, including all characters defined by different character sets. Unicode data is stored using the nchar, varchar, and ntext Data Types in SQL Server.
· Unicode data type with a fixed nchar length (up to 4000 Unicode characters.

Nchar (N)
Unicode data with a fixed length of n characters. The value of N must be between 1 and 4,000. Storage size: n characters
Double the number of segments. The synonyms of nchar in the SQL-92 are national char and national character.

· Nvarchar (varchar) is a variable-length Unicode data type.

Nvarchar (N)
Unicode data with a variable length of n characters. The value of N must be between 1 and 4,000.
The storage size of bytes is twice the number of input characters. The length of the input data can be zero. Nvarchar
The synonyms in the SQL-92 are national char varying and National Character varying.

· Ntext is used when any item in the column contains more than 4000 Unicode characters.

4. Date and Time Data Types
Date and Time data are composed of valid date or time. For example, the valid date and time data include "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01 am 8/17/98 ". In Microsoft SQL Server 2000, date and time data are stored in the datetime and smalldatetime data types.
· Datetime
Data Type storage starts from January 1.
Each value requires 8 bytes of storage space.

· Smalldatetime
The data type storage period ranges from January 1, January 1-20, 1900 to January 1, June 6.
Each value requires a storage space of 4 bytes.

· Set the date
The command 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.

5. Digital Data Type
Numeric data only contains numbers. Numeric data includes positive, negative, decimal, fractional, and integer.
· Integer Data
Integer Data is composed of negative integers or positive integers, such as-15, 0, 5, and 2509. In Microsoft SQL Server
In 2000, integer data is stored in bigint, Int, smallint, and tinyint data types. Bigint Data Type
Type can store a wider range of numbers than int data types. Int data type is more storage range than smallint Data Type
The value range of smallint is larger than that of tinyint.

Bigint storage range from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807)
Number. The storage size is 8 bytes.

The Int storage range is-2,147,483,648 to 2,147,483,647 (each value requires 4 bytes of storage space ).

The smallint storage range is only-32,768 to 32,767 (each value requires 2 bytes of storage space ).

Tinyint can only store numbers ranging from 0 to 255 (each value requires a storage space of 1 byte ).

· Decimal data
Decimal data contains data stored on the minimum valid number. In SQL Server, decimal or numeric data types are used to store fractional data. The number of bytes required to store a decimal or numeric value depends on the total number of the data and the number of decimal places on the right of the decimal point. For example, storing a value of 19283.29383 requires more bytes than storing 1.1. In SQL Server, the numeric data type is equivalent to the decimal data type.

· Approximate digital data
Approximate numeric (floating point) data includes the maximum precision reserved by the binary counting system. In SQL Server, approximate numeric data is stored in float and real data types. For example, if the decimal number is 1/3 (Cyclic decimal number), The number cannot be accurately expressed as an approximate decimal number. Therefore, the values obtained from SQL Server may not accurately represent the original data stored in the column. For example, floating point numbers ending with. 3,. 6, and. 7 are approximate values of numbers.

6. Currency Data Type
Currency data indicates a positive or negative currency value. In Microsoft SQL Server 2000, the money and smallmoney data types are used to store currency data. The precision of currency data storage is four decimal places.
· The Money storage range is-922,337,203,685,477.5808 to + 922,337,203,685,477.5807
(8 bytes of storage space required ).

· The smallmoney storage range is-214,748.3648 to 214,748.3647 (4 bytes of storage space required ).

· If the value exceeds the preceding range, you can use the decimal data type instead.

7. Special Data Types
Special Data includes data that cannot be expressed using the binary, character, Unicode, date and time, number, and currency data types described earlier.

Microsoft SQL Server 2000 contains four special data types:
· Timestamp
It is used to represent the activity sequence of SQL Server on one row, expressed in an ascending number in binary format. When the rows in the table change, update the time sequence using the time sequence value of the current database obtained from the @ dbts function. Timestamp data is irrelevant to the date and time when data is inserted or modified. To automatically record the time when data changes in the table, use the datetime or smalldatetime data type to record events or triggers. Rowversion in SQL Server is a synonym for timestamp.

· Bit
BIT data types can only include 0 or 1. BIT data types can be used to represent true, false, yes, or no. For example, questions about whether the customer is visiting for the first time can be stored in the bit column.

· Uniqueidentifier
A 16-bit hexadecimal number indicates a globally unique identifier (guid ). You can use guid to uniquely identify a row in multiple rows. For example, you can use the unique _ identifier data type to define a customer ID code column to edit the company's total customer list from multiple countries/regions.

· SQL _ Variant
A data type that stores various data types supported by SQL Server (except for text, ntext, timestamp, and SQL _variant.

· Table
A special data type that stores post-processing result sets. The table data type can only be used to define local variables of the table type or return values of user-defined functions.

· User-Defined
User-Defined data types are allowed. For example, product_code can be designed to be in the format of two uppercase letters based on the char data type followed by a five-digit supplier number.

--------------------------------------------------------------------------------

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.

[(N)]
N Bytes of variable-length binary data. N must be from 1 to 8,000. The size of the bucket is the length of the actual input data + 4
Not n Bytes. The input data length may be 0 bytes. Varbinary's
The synonym is binary varying.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/baoxuetianxia/archive/2009/11/22/4851072.aspx