51windows.net>
Technical documentation>
Database>
Microsoft SQL Server> SQL Server Data Type
SQL Server data type
1. SQL Server data type
The data category is an attribute of the data, indicating the type of information indicated by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and all kinds of various types and names of the defined data types are more or less. SQL Server provides 25 data types:
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
The Binary data type can be only a fixed length (Binary) or a length of length.
Binary [(n)] is the N-bit fixed binary data. Wherein N value ranges from 1 to 8000. The size of its storage is N 4 bytes.
Varbinary [(N)] is binary data for n-bit length. Wherein N value ranges from 1 to 8000. The size of the storage is N 4 bytes, not n bytes.
The data stored in the image data type is stored in a bit string, not interpreted by SQL Server, must be explained by the application. For example, the application can store data in the Image data type using BMP, TIEF, GIF, and JPEG format.
(2) The type of character data type character data includes
Char, varchar and text
Character data is data made from any letters, symbols, and numbers.
VARCHAR is a bell-length character data, which does not exceed 8KB. Char is a fixed length character data with a maximum length of up to 8KB. More than 8KB ASCII data can be stored using the Text data type. For example, because the HTML document is all ASCII characters, and in general, the length is more than 8KB, so these documents can be stored in the SQL Server in the TEXT data type.
(3) Unicode data type Unicode data types include
Nchar, nvarchar and ntext
In Microsoft SQL Server, traditional non-Unicode data types allow using characters defined by a particular character set. Allow a character set during the SQL Server installation. Use the Unicode data type, you can store any characters defined by the Unicode standard. In the Unicode standard, all characters defined in various character sets are included. Using the Unicode data type, the victory is twice as large as the non-Unicode data type.
In SQL Server, UNICODE data is stored in Nchar, NVARCHAR, and NTEXT data types. Columns using this type of character type can store characters in multiple characters. When the length of the column changes, the NVARCHAR character type should be used, and up to 4000 characters can be stored. When the length of the column is fixed, the NCHAR character type should be used, and the same, at this time, up to 4000 characters can be stored. When using the NTEXT data type, the column can store more than 4000 characters.
(4) Date and time data type date and time data types include
DateTime and SmallDatetime two types
Date and time data type consist of valid date and time. For example, effective date and time data include "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 data is the time before, the date is behind. In Microsoft SQL Server, the date and time data type includes both types of DateTime and SmallDateTime, the stored date range begins on January 1, 1753, ending from December 31, 9999 (8 each value requires 8 Storage bytes). When using the SmallDateTime data type, the stored date range begins on January 1, 1900, and ends on December 31, 2079 (each value requires 4 storage bytes).
The date of the date can be set. Set the order in the date format as follows:
Set DateFormat {Format | @format _var |
Among them, Format | @format_var is the order of the date. Effective parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after the SET DATEFORMAT YMD is executed, the date is formatted in the form of an annual month; after the SET DATEFORMAT DMY is executed, the date format is a year in the day.
(5) Digital data type digital data only contains numbers. Digital data types include positive and negative numbers, decimal (floating point) and integers
Integers consisting of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In MicrSoft SQL Server, the data type of integer storage is
INT, Smallint and Tinyint. The INT data type storage data is greater than the scope of the Smallint data type stored data, and the scope of the SmallINT type storage data is greater than the range of Tinyint data types. The range of INT data dogs stored data is from -2 147 483 648 to 2 147 483 647 (4 bytes of storage space each value). When using the Smallint data type, storage data ranges from -32 768 to 32 767 (2 bytes storage space each value). When using the Tinyint data type, the storage data ranges from 0 to 255 (one byte storage space for each value).
Accurate smuggling data in SQL Server is Decimal and NuMeric. The storage space occupied by this data is determined based on the number of bits after the data of the data.
In SQL Server, the data type of approximate decimal data is Float and Real. For example, one-third of this score is recorded. 333333, when using approximate data types, it can be accurately expressed. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.
(6) The currency data represents the number of positive or negative currencies. In Microsoft SQL Server, the data type of currency data is
Money and Smallmoney
The Money data type requires 8 stored bytes, and the SmallMoney data type requires 4 stored bytes.
(7) Special data type Special data types include data types that have not been entered in front. There are three types of special data types, namely
TimeStamp, Bit and UniqueIdentifier.
TimeSTAMP is used to represent the order of SQL Server activities, expressed in the format of binary projection. TimeStamp data does not matter to insert data or date and time. Bit consists of 1 or 0. When you represent true or false, ON or OFF, use the BIT data type. For example, asking if it is a client request to be accessed, it can be stored in the column of this data type.
UniqueIdentifier consists of 16-bytes of hexadecimal numbers, indicating a global unique. The GUID is very useful when the recording line of the table requires the only time. For example, using this data type in the customer identification number can distinguish between different customers.
2. User-defined data type User-defined data types are based on data types provided in Microsoft SQL Server. When the same data type must be stored in several tables, and to ensure that these columns have the same data type, length, and blanket, the user-defined data type can be used. For example, a data type called Postal_Code can be defined, which is based on a CHAR data type.
When creating a user-defined data type, three numbers must be provided: the name of the data type, the system data type and the data type of data type.
(1) Creating a user-defined data type Creating a user-defined data type You can use the Transact-SQL statement. System stored procedures sp_addtype can create user-defined data types. Its grammar 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 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 you must use single quotes, such as 'null', 'not null', or 'Nonull'.
example:
Uses USE CUST
EXEC SP_ADDTYPE SSN, 'VARCHAR (11)', "Not null"
Creating a user-defined data type SSN, whose system data type is characterized by a character that becomes 11, and is not allowed.
example:
Uses USE CUST
EXEC SP_ADDTYPE BIRTHDAY, datetime, 'null'
Create a user-defined data type Birthday, whose system data type is DateTime, allowed 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) Deleting a user-defined data type When the user-defined data type is not required, it can be deleted. Deleting a user-defined data type command is sp_droptype {'type'}.
example:
Use master
EXEC SP_DROPTYPE 'SSN'
Note: This user-defined data type cannot be deleted when the columns in the table are also using user-defined data types, or when they are also bound to have default or rules.
SQL Server field type description
The following is a field type of SQL Server 7.0 or higher. Field Type Description of SQL Server6.5 Please refer to the instructions provided by SQL Server.
Field Type Description Bit 0 or 1 Integer Digital INT from -2 ^ 31 (-2, 147, 483, 648) to 2 ^ 31 (2, 147, 483, 647) integer digital Smallint from -2 ^ 15 (-32, 768) to 2 ^ 15 (32, 767) Integer Digital Tinyint from 0 to 255 Digital Decimal from -10 ^ 38 to 10 ^ 38-1 Numeric Numeric Decimal synonym Money from -2 ^ 63 (-922, 337, 203, 685, 477.5808) to 2 ^ 63-1 (922, 337, 203, 685, 477.5807), the minimum currency unit, thousands of tens of SMallMoney from -214, 748.3648 to 214, 748.3647, minimum currency unit, 10,000 tenth float from -1.79e 308 to 1.79e 308 variable accuracy Digital REAL from -3.04E 38 to 3.04E 38 variable precision Digital DateTime from January 1, 1753 to 31 and time data of 31, 9999, minimum time unit is 3 modium or 3.33 milliseconds SmallDatetime From January 1, 1900, the date and time data of June 6, 2079, the minimum time unit is a minute TimeStamp timestamp, a database width unique representifier global unique identifier Guid Char fixed length non-Unicode character type Data, the maximum length of 8000 VARCHAR is extremely non-Unicode characteristic data, the maximum length of 8000 text is long-length non-Unicode type data, the maximum length of 2 ^ 31-1 (2G) Nchar fixed length Unicode character data, The maximum length of 8000 nVARCHAR becomes the characteristic data of Unicode, the maximum length of 8000 nText becomes the characteristic data of Unicode, the maximum length is 2 ^ 31-1 (2G) binary fixed length binary data, the maximum length is 8000 varbinary Binary data, the maximum length is 8000 images becomes long binary data, the maximum length is 2 ^ 31-1 (2G)
