Bit integer
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 or No, True or False, and on.
Or shut it down.
Int integer 4 bytes
From -2 3 1 (-2 147483648) to 2 3 1 (2 147483.
647).
This data type can be used for almost all numerical data stored in the database.
Smallint integer 2 bytes
The smallint data type can store integers from -2 15 (-32768) to 2 15 (32767).
Tinyint integer 1 byte
The Tinyint data type can store integers from 0 to 255.
Numeric and decimal exact number types
Numerical data with fixed precision, ranging from-1038- 1 to 1038- 1.
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, and the precision is the number of digits stored to the right of the decimal point.
Currency currency type
This data type can store data from-922 billion to 922 billion, accurate to one tenth of the monetary unit.
Small currency currency type
This type of data can be stored in the range of -2 1448.648 to 2 14. 36638.86666666667
Between the data, accurate to one over ten thousand of the monetary unit.
Floating point font
Any number from-1.79E+308 to 1.79E+308.
Real type
A floating-point number between -3.40E+38 and 3.40E+38.
Date time date time type
All date and time data from 1753 65438+ 10/0 to nine thousand nine hundred and ninety-nine 65438+February 3 1 are accurate to one hundredth of a second or 3.33 milliseconds.
Smalldatetime date time type
Date and time are from 1900 1 to June 6, 2079, accurate to one minute.
Cursor special data type
It contains a reference to the cursor. This data type is used for stored procedures and cannot be used when creating tables.
Time stamp special data type
Used to create a unique number in the database.
There can only be one timestamp column in a table. Every time a row is inserted or modified, the value of the timestamp column changes.
Unique identifier special data type
Globally unique identifier, that is, GUID. You can use NEWID.
Function or convert a string to a unique identifier to initialize a column with a unique identifier.
character type
Stores fixed-length nonuniformly encoded data of a specified length. When defining a column as 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.
The maximum column width of this data type is 8000.
Characters.
Varchar character type
Used to store non-uniform coded character data. With char
Different types, this data type is variable in length.
When defining a column as a data type, you should specify the maximum length of the column.
The biggest difference between it and char data type is that the length of storage is not the length of column, but the length of data.
Text character type
Used to store a large number of non-uniformly encoded character data.
This data type can have at most 2 3 1- 1 or 2 billion double-byte characters.
Nchar unified coding character type
Which is used for storing fixed-length uniformly coded character data. Unicode uses a double-byte structure to store each character instead of a single byte (as is the case with ordinary text). It allows a large number of extended characters. This data type can store 4000 characters, which doubles the byte space used.
Unicode character type
nvarchar
The data type is used as uniform coded character data with variable length. This data type can store 4000 characters and double the byte space used.
Unicode character type
The ntext data type is used to store a large number of uniformly encoded character data.
This data type can store 2 30.
-1 or nearly 1 100 million characters, and the byte space used is doubled.
Binary data type
Binary data types are used to store up to 8000.
Fixed length binary data in bytes. This data type should be used when the contents of the input table are close to the same length.
Varbinary binary data type
The Varbinary data type is used to store data up to 8000.
Variable length binary data in bytes. This data type should be used when the content size of the input table is variable.
Image binary data type
Image data type is used to store binary data with variable length, up to 2 31-1or about 2 billion bytes.
-
(1) binary data type
Binary data includes binary, Varbinary and Image.
binary
The data type can be binary or variable length.
Binary[(n)] is n-bit fixed binary data. Where the value range of n is from 1 to.
8000。 Its storage size is n+4 bytes.
Varbinary[(n)] is n-bit variable-length binary data. Where the value range of n is from 1 to.
8000。 Its storage size is n+4 bytes, not n bytes.
The data stored in the Image data type is stored as a bit string, not by SQL Server.
The content of interpretation must be interpreted by the application. For example, applications can use BMP, TIEF, GIF and JPEG formats to store data of image data types.
(2) Character data type
The types of character data include char, Varchar and Text.
Character data is data composed of any combination of letters, symbols and numbers.
Variable length string
Is variable-length character data, and the length is no more than 8KB. Char is fixed-length character data with a maximum length of 8KB. ASCII data exceeding 8KB can be stored using text data types. For example, because
Html documents are all ASCII characters, which are generally more than 8KB in length, so these documents can be stored as text data types in SQL Server.
(3)Unicode data type
Unicode data types include Nchar, Nvarchar and Ntext.
In Microsoft SQL Server, the traditional non-.
Unicode data types allow the use of characters defined by a specific character set. During the installation of SQL Server, you can choose a character set. Use Unicode
Data types, as well as any characters defined by the Unicode standard, can be stored in columns. Unicode format
This standard includes all characters defined in various character sets. Using Unicode data types can overcome twice the size of using non-unicode data types.
In SQL
In the server, Unicode data are Nchar, Nvarchar and Ntext.
Data type storage. Columns stored with this character type can store characters in multiple character sets. When the length of the column changes, you should use the Nvarchar character type, which can store up to 4000 characters.
Characters. When the length of the column is fixed, the Nchar character type should be used. Similarly, you can store up to 4000 characters. When using Ntext data type, this column can store more than 4000.
Characters.
(4) Date and time data type
Date and time data types include date, time and
There are two types of Smalldatetime.
The date and time data type consists of valid dates and times. For example, valid date and time data include "4/0 1/98"
12: 15:00:00:00 PM "and"1:28: 29:15: 01am "
8/ 17/98"。 The former data type is the date before and the time after, and the latter data type is the time before and the date after. In Microsoft SQL.
In the Server, when the date and time data types include Datetime and Smalldatetime, the stored date range is from 1753 1.
It starts on June 30th and ends on February 3rd1999 (each value needs 8 storage bytes). When using the Smalldatetime data type, the date range stored is 1900 1.
It starts from June 1 and ends on February 3, 20791(each value needs 4
Storage bytes).
You can set the date format. The command to format the date is as follows:
Set DateFormat {format | @format
_var|
Where format | @format_var is the order of dates. Valid parameters include MDY, DMY, YMD, YDM, MYD and.
DYM. By default, the date format is MDY.
For example, when setting date format YMD is executed, the date format is year, month and day format; When executing Set
After DateFormat DMY, the date format is day, month and year.
(5) Digital data type
Digital 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. exist
In Micrsoft SQL Server, the data types of integer storage are int, Smallint and Tinyint.
Int data type stores more data than Smallint data type, while Smallint data type stores more data than Tinyint.
The data type stores the range of data.
The range of using Int data to store data is from -2 147 483 648 to 2 147 483 647 (each value needs 4 bytes of storage space).
When using the Smallint data type, the range of stored data is 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 (each value requires 1 byte storage space).
Accurate Xiao Lou data in SQL Server
The data types in are decimal and number. The storage space occupied by this kind of data is determined according to the number of bits after the number of data bits.
In SQL Server
The data types of approximate decimal data are Float and.
True. For example, one third of this score is recorded as. 3333333, which can be accurately expressed when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in this column.
(6) Monetary data indicates positive or negative monetary quantity.
In Microsoft SQL Server, the data types of currency data are Money and Smallmoney.
Currency data type requirements 8
Storage bytes, Smallmoney data type requires 4 storage bytes.
(7) Special data types
Special data types include data types not mentioned before. There are three special data types, namely timestamp, bit sum.
Unique identifier.
Time stamp is used to indicate the sequence of SQL Server activities and is expressed in binary projection format. time stamp
Data has nothing to do with inserting data or date and time.
Bit consists of 1 or 0. When representing true or false, on or off, bits are used.
Data type. For example, a client request asking whether every access can be stored in a column of this data type.
Unique identifier of 16
Hexadecimal number of bytes, representing globally unique. GUID is very useful when the record rows of a table need to be unique. For example, using this data type in the customer identification number column can distinguish different customers.
2. User defined data types
User-defined data types are based on Microsoft SQL Server.
Data types provided in. When several tables must store the same data type, user-defined data types can be used to ensure that these columns have the same data type, length and nullability. For example, you can define a file named.
The data type of postal_code based on Char.
Data type.
When creating a user-defined data type, you must provide three numbers: the name of the data type, the system data type on which the data type is based, 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 procedure 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']
Wherein the type
The name of the user-defined data type. System_data_type is the data type provided by the system, such as Decimal, Int, Char, etc. null_type
Indicates how the data type handles NULL values and must be enclosed in single quotes, such as "null", "NOT NULL" or "null".
Example:
use
customer
Exec sp_addtype ssn，' Varchar( 1 1)'，" Not Null '
Create user-defined data types
Ssn is a character based on system data type, with variable length, 1 1, and cannot be empty.
Example:
Use customers
Exec sp_addtype
Birthday, date and time,' empty'
Create a user-defined data type birthday based on the system data type.
Date and time, allowed to be empty.
Example:
Use master
Exec sp_addtype
Telephone,' varchar(24),' not empty'
Eexc sp_addtype
Fax,' varchar(24)',' Null'
Create two data types, namely telephone and fax.
(2) Delete user-defined data types
User-defined data types can be deleted when they are no longer needed. The command to delete a user-defined data type is sp_droptype {'type'}.
Example:
use
grasp
Exec sp_droptype
' ssn '
Note: User-defined data types cannot be deleted when columns in the table are still using them, or there are default values or rules bound to them.
Field type description of SQL SERVER
The following is a description of the field types of SQL SERVER7.0 version 7.0 or later. For the description of field types in SQL SERVER6.5, please refer to the description provided by SQL SERVER.
Field type description
Bit 0 or an integer of 1
Internationalorganizations (same as international organizations)
Integer from -2 3 1(-2 147 483 648) to 2 3 1(2 147 483 647).
smallint
Integer from-215 (-32,768) to 215 (32,767).
Tinyint is an integer from 0 to 255.
Decimal number with fixed precision and significant digits, ranging from-10 38 to 10 38- 1.
Synonym of numerical decimal
money
Currency data range from-263 (-922,337,203,685,477.5808) to 263-1(922,337,203,685,477.5807), and the smallest monetary unit is ten thousandths.
The monetary data of smallmoney ranges from -2 14748.3648 to 2 14748.3647, and the smallest monetary unit is ten thousandths.
flotage
Numbers with variable precision, ranging from-1.79E+308 to 1.79E+308.
Real number, the accuracy ranges from -3.04E+38 to 3.04E+38.
Datetime is the date and time data from 1 75365438+1October1to nine thousand nine hundred and ninety-nine1231. The minimum time unit is a percentage of three seconds or 3.33 milliseconds.
Smalldatetime is the date and time data from 1900 65438+ 10/to June 6, 2079, and the minimum time unit is minutes.
Timestamp Timestamp, a unique number of database width.
Unique identifier globally unique identifier GUID
Char fixed-length non-unicode character data, the maximum length is 8000.
Varchar variable-length non-unicode character data with a maximum length of 8000.
Text variable-length non-unicode character data, with the maximum length of 2 3 1- 1 (2g).
Enchar
Fixed-length Unicode character data with a maximum length of 8000.
Nvarchar variable-length Unicode character data with a maximum length of 8000.
ntext
Unicode character data with variable length, the maximum length is 2 3 1- 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, the maximum length is 2 3 1- 1 (2g).
-
a little
1 bit, with a value of 0 or 1.
Internationalorganizations (same as international organizations)
integer
4 bytes, the value is-231~ 231-1.
Smallint
2 bytes, the value is-215 ~ 215-1.
Tinyint
1 byte, with a value of 0~255.
decimal system
Digital data, with accuracy of p and width of s.
digital
money
8 bytes, deposit currency type, with values of -2 63 ~ 2 63- 1.
change
4 bytes, the currency type is stored, and the value is-214748.3648 ~+214748.3647 approximate numerical data type.
float
N is between 1~24, with 4 bytes and 7-bit precision.
N= 1~7 is a real number.
N is between 25 and 53,8 bytes with an accuracy of 15 bits.
=8~ 15 is a floating point number.
Date time
8 bytes, describing the date and time of the day, and the precision of the value is 1/300 seconds.
Smalldatetime
4 bytes, describing the date and time of the day, with an accuracy of minutes.
cursor
Reference to cursor
time stamp
8 bytes, the only data stored in the database.
Unique identifier
16 bytes, storing globally unique identifier (GUID).
character
Fixed length of non-unicode string, n= 1~8000.
character
Variable character
Variable length, non-unicode string n= 1~8000
Character change (n)
text
Variable length non-uniunicode data in the server code page. The maximum length is 2 3 1- 1 characters.
Enchar
Unicode string with fixed length n= 1~4000
National character (n),
National character
Nvarchar
Unicode string with fixed length n= 1~4000
national
Character change (n)
Ntext
Unicode data with variable length, the maximum length is 2 30-1characters.
National text
binary system
Fixed-length binary data, n is between 1 and 8000, and the storage space is n+4 bytes.
Variable binary number
Variable length binary data, n= 1~8000.
binary variable
Tmage
Binary data with variable length, the size is 0 ~ 2 3 1- 1.
note:
1)
For numerical data types, decimal places refer to the number of digits stored after the decimal point, while precision refers to all digits that can be stored, including the decimal point.
2) The deposit width of money and small coins is 4.
3) The timestamp column value is automatically updated when each row is updated, and the timestamp column cannot be a keyword or a part of a keyword.
4)
The unique identification data type cannot use arithmetic operators (such as+,-,etc.). ), and this data type can only use equality comparison operation. Unicode is a standard for storing data consistently in all character sets. It uses twice as much storage space as non-unicode data storage.