Equivalenze fra i tipi di Access e quelli di SQL Server

Equivalenze fra i tipi di Access e quelli di SQL Server
a cura di Oscar Zanin (requisiti: nessuno)

Microsoft Access data type SQL Server data type

Yes/No A field data type that you use for fields that will contain only one of two values, such as Yes or No and True or False. Null values are not allowed bit In an Access project, a data type that stores either a 1 or 0 value. Integer values other than 1 or 0 are accepted, but they are always interpreted as 1

Number (Byte) In a Microsoft Access database, a field data type designed for numerical data that will be used in mathematical calculations. Use the Currency data type, however, to display or calculate currency values. tinyint In an Access project, a data type of 1 byte (8 bits) that stores whole numbers in the range of 0 through 255

Number (Integer) smallint In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767)

Number (Long Integer) int int data type: In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

Number (Single) real In an Access project, an approximate numeric data type with seven-digit precision. It can hold positive values from approximately 1.18E - 38 through 3.40E + 38, negative values from approximately -1.18E - 38 through -3.40E + 38, or zero

(no equivalent) bigint In an Access project, a data type of 8 bytes (64 bits) that stores whole numbers in the range of -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)

Number (Double) float In an Access project, an approximate numeric data type with 15-digit precision. It can hold positive values from approximately 2.23E - 308 through 1.79E + 308, negative values from approximately -2.23E - 308 through -1.79E + 308, or zero

Currency In a Microsoft Access database, a data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important money In an Access project, a data type that stores monetary values in the range -922,337,203,685,477.5707 through 922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit

smallmoney In an Access project, a data type that stores monetary values from -214,748.3648 to 214,748.3647, with accuracy to a ten-thousandth of a monetary unit. When smallmoney values are displayed, they are rounded up to two decimal places

Decimal/numeric (Access database): An exact numeric data type that holds values from -10^28 - 1 through 10^28 - 1. You can specify the scale (maximum number of digits) and precision (maximum total number of digits to the right of the decimal point) decimal (Access project): An exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point)

numeric In an Access project, an exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point)

Date/Time An Access database data type that is used to hold date and time information datetime In an Access project, a date and time data type that ranges from January 1, 1753, to December 31, 9999, to an accuracy of three-hundredths of a second, or 3.33 milliseconds

smalldatetime In an Access project, a date and time data type that is less precise than the datetime data type. Data values range from January 1, 1900, through June 6, 2079, to an accuracy of one minute

AutoNumber (Increment) In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID int (with the Identity property defined) In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

Text (n) In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less varchar(n) In an Access project, a variable-length data type with a maximum of 8,000 ANSI characters

nvarchar(n) In an Access project, a variable-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters

Memo In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters text In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) characters; default length is 16

OLE Object A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database image In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) bytes of binary data. It is used to store Binary Large Objects (BLOBs), such as pictures, documents, sounds, and compiled code

Replication ID (GUID) also called globally unique identifier GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs uniqueidentifier uniqueidentifier data type: In an Access project, a 16-byte globally unique identifier (GUID). (SQL Server 7.0 or later)

Hyperlink A data type for an Access database field that stores hyperlink addresses. An address can have up to four parts and is written using the following format: displaytext#address#subaddress# char (With the Hyperlink property set to Yes) char data type: In an Access project, a fixed-length data type with a maximum of 8,000 ANSI characters

nchar (With the Hyperlink property set to Yes) nchar data type: In an Access project, a fixed-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters

varchar, nvarchar (With the Hyperlink property set to Yes) see over

(no equivalent) varbinary In an Access project, a variable-length data type with a maximum of 8,000 bytes of binary data

(no equivalent) smallint In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767)

(no equivalent) timestamp In an Access project, a data type that is automatically updated every time a row is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) or varbinary(8), indicating the sequence of data modifications

(no equivalent) char,nchar see over

(no equivalent) sql_variant In an Access project, a data type that stores values of several data types, except for text, ntext, image, timestamp, and sql_variant types. It is used in a column, parameter, variable, or return value of a user-defined function

(no equivalent) user-defined In a Microsoft SQL Server database, a definition of the type of data a column can contain. It is defined by the user with existing system data types. Rules and defaults can only be bound to user-defined data types

Note: In an Access project or SQL Server database, the "n" prefix stands for "national" and means that the data type is unicode-enabled. In an Access database, all text columns are unicode-enabled by default.

Microsoft Access data type		SQL Server data type
Yes/No	A field data type that you use for fields that will contain only one of two values, such as Yes or No and True or False. Null values are not allowed	bit	In an Access project, a data type that stores either a 1 or 0 value. Integer values other than 1 or 0 are accepted, but they are always interpreted as 1
Number (Byte)	In a Microsoft Access database, a field data type designed for numerical data that will be used in mathematical calculations. Use the Currency data type, however, to display or calculate currency values.	tinyint	In an Access project, a data type of 1 byte (8 bits) that stores whole numbers in the range of 0 through 255
Number (Integer)		smallint	In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767)
Number (Long Integer)		int	int data type: In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)
Number (Single)		real	In an Access project, an approximate numeric data type with seven-digit precision. It can hold positive values from approximately 1.18E - 38 through 3.40E + 38, negative values from approximately -1.18E - 38 through -3.40E + 38, or zero
(no equivalent)		bigint	In an Access project, a data type of 8 bytes (64 bits) that stores whole numbers in the range of -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)
Number (Double)		float	In an Access project, an approximate numeric data type with 15-digit precision. It can hold positive values from approximately 2.23E - 308 through 1.79E + 308, negative values from approximately -2.23E - 308 through -1.79E + 308, or zero
Currency	In a Microsoft Access database, a data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important	money	In an Access project, a data type that stores monetary values in the range -922,337,203,685,477.5707 through 922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit
		smallmoney	In an Access project, a data type that stores monetary values from -214,748.3648 to 214,748.3647, with accuracy to a ten-thousandth of a monetary unit. When smallmoney values are displayed, they are rounded up to two decimal places
Decimal/numeric	(Access database): An exact numeric data type that holds values from -10^28 - 1 through 10^28 - 1. You can specify the scale (maximum number of digits) and precision (maximum total number of digits to the right of the decimal point)	decimal	(Access project): An exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point)
		numeric	In an Access project, an exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point)
Date/Time	An Access database data type that is used to hold date and time information	datetime	In an Access project, a date and time data type that ranges from January 1, 1753, to December 31, 9999, to an accuracy of three-hundredths of a second, or 3.33 milliseconds
		smalldatetime	In an Access project, a date and time data type that is less precise than the datetime data type. Data values range from January 1, 1900, through June 6, 2079, to an accuracy of one minute
AutoNumber (Increment)	In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID	int (with the Identity property defined)	In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)
Text (n)	In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less	varchar(n)	In an Access project, a variable-length data type with a maximum of 8,000 ANSI characters
		nvarchar(n)	In an Access project, a variable-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters
Memo	In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters	text	In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) characters; default length is 16
OLE Object	A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database	image	In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) bytes of binary data. It is used to store Binary Large Objects (BLOBs), such as pictures, documents, sounds, and compiled code
Replication ID (GUID) also called globally unique identifier	GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs	uniqueidentifier	uniqueidentifier data type: In an Access project, a 16-byte globally unique identifier (GUID). (SQL Server 7.0 or later)
Hyperlink	A data type for an Access database field that stores hyperlink addresses. An address can have up to four parts and is written using the following format: displaytext#address#subaddress#	char (With the Hyperlink property set to Yes)	char data type: In an Access project, a fixed-length data type with a maximum of 8,000 ANSI characters
		nchar (With the Hyperlink property set to Yes)	nchar data type: In an Access project, a fixed-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters
		varchar, nvarchar (With the Hyperlink property set to Yes)	see over
(no equivalent)		varbinary	In an Access project, a variable-length data type with a maximum of 8,000 bytes of binary data
(no equivalent)		smallint	In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767)
(no equivalent)		timestamp	In an Access project, a data type that is automatically updated every time a row is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) or varbinary(8), indicating the sequence of data modifications
(no equivalent)		char,nchar	see over
(no equivalent)		sql_variant	In an Access project, a data type that stores values of several data types, except for text, ntext, image, timestamp, and sql_variant types. It is used in a column, parameter, variable, or return value of a user-defined function
(no equivalent)		user-defined	In a Microsoft SQL Server database, a definition of the type of data a column can contain. It is defined by the user with existing system data types. Rules and defaults can only be bound to user-defined data types