Corresponding use of ACCESS data type and MS SQL data type: Text NVARCHAR (N) Remark NTEXT Digital (Long Integer) INT Digital (Integer) Smallint Number (Single Joint) Real Digital (Double Accuracy) Float Digital (bytes) Tinyint Money Money Date SmallDateTime Boolean BitAccess allows nine data types: http://publishblog.blogchina.com/blog/tb.b? DiaryId = 3915846
In the design view of the table, each field has a design type, Access allows nine data types: text, note, value, date / time, currency, automatic number, yes / no, OLE object, hyperlink, query wizard.
Text: This type allows up to 255 characters or numbers. The ACCESS default size is 50 characters, and the system only saves characters in the field without saving the empty characters in the unused location in the text field. You can set the "Field Size" property to control the maximum character length of the entered. Remarks: This type is used to save longer text and numbers that allow fields to store content with up to 64,000 characters. But Access cannot sort or index the note field, but you can sort and index the text field. Although you can search for text in the Remarks field, it is better to search for fast as you have an indexed text field. Number: This type of field type can be used to store digital data for arithmetic computing, and the user can set "Field Size" attribute to define a specific number type, and any font specified as a digital data type can be set to "byte", "Integer", "Long Integer", "Single Joint Number", "Double Accuracy", "Synchronous Replication ID", "Decan" five types. It usually defaults to "double precision" in Access. Date / Time: This type is used to store dates, time, or date time, each date / time field requires 8 bytes to store space. Currency: This type is a special type of digital data type, equivalent to a digital field type with a double precision attribute. When entering data to the currency field, you don't have to type the comma in the RMB symbol and thousands. Access will automatically display the renminbi symbols and commas and add two decimal to currency fields. When the decimal part is more than two digits, Access will go all over the data. Accuracy is a decimal point 15 digits and the 4 digits of the right square. Automatic number: This type is more special. When adding new records to the table, Access will automatically insert a unique order or random number, which specifies a certain value in the Auto Number field. Once the auto number is specified, it will be permanently connected to the record. If you delete a record containing the Auto Number field, Access does not re-numbered the table automatic number field. When a certain record is added, Access no longer uses the value of the automatic number field that has been deleted, but re-assapts the incremental rules. YES / NO: This field is a field set up to only two different optional values in a field. By the format feature of the / no data type, the user can choose to select / no fields. OLE object: This field means that the field allows "link" or "embedded" OLE object separately. When adding data to the OLE object field, you can link or embed an OLE object in an Access table refers to an object created by other OLE protocol programs, such as Word documents, Excel spreadsheets, images, sounds, or other binary data. The OLE object field is up to 1GB, which is mainly limited by disk space. Hyper link: This field is primarily used to save the hyperlink, including the text as a hyperlink address or a combination of characters and numbers stored in text form. When you click a hyperlink, the web browser or Access will reach the specified target based on the hyperlink address. The hyperlink can contain up to three parts: First, the text displayed in the field or control; the second is the path to the file or page; the third is the address in the file or page. The easiest way to insert a hyperlink address in this field or control is to click the "Hyper Link" command in the Insert menu. Check the Wizard: This field type provides the user with a list of field content, which can be selected as the content of the listed field as the content of the field in the list. SQL data type detailed: http://publishblog.blogchina.com/blog/tb.b? DiaryId = 2217273
(1) Type of binary data
Binary data includes binary, varbinary, and image binary data types or binary, or variable 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) Character data type
Types of character data include char, varchar, and text character data, which are combined by 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
The Unicode data type includes nchar, nvarchar, and ntext in Microsoft SQL Server, traditional non-Unicode data types allow the character 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 and time data types consisting 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 date format command as follows: set dateformat {format | @format _var | where 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 date of the year; after the SET DATEFORMAT DMY is executed, the date format is the annual form of the day (5) Digital data type.
Digital data only contains numbers. Digital data types include positive and negative numbers, decimal (floating point), and integer integers consisting of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In MicrSoft SQL Server, the integer stored data type 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 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 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: 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, does not allow empty. Example: USE CUST EXEC SP_ADDTYPE BIRTHDAY, DATECE, 'NULL' Create a user-defined data type Birthday, which is based on DateTime, allowed empty. Example: Use master exec sp_addtype telephone, 'varchar (24),' not null 'eexc sp_addtype Fax, 'varchar (24)', 'null' Creates two data types, TELEPHONE and FAX
(2) Delete user-defined data types
Delete when the user-defined data type is not required. 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 column in the table is also using the user-defined data type, or when it is binding to the default or rule thereon.
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 image to become a long binary data, the maximum length is 2 ^ 31-1 (2G)
