DAP Relational Database Server II: Difference between revisions
From OPeNDAP Documentation
⧼opendap2-jumptonavigation⧽
Line 20: | Line 20: | ||
{| border="1" cellspacing="0" | {| border="1" cellspacing="0" | ||
|+ [http://msdn.microsoft.com/en-us/library/ms187752.aspx Transact-SQL (Microsoft)] | |+ [http://msdn.microsoft.com/en-us/library/ms187752.aspx Transact-SQL (Microsoft)] | ||
!Type !! | !Type !! Description !! Storage Bytes !! DAP equiv. | ||
|- | |- | ||
|bigint || || 8 bytes || none | |bigint ||Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). || 8 bytes || none | ||
|- | |- | ||
|int || || 4 bytes || | |int ||Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). || 4 bytes || | ||
|- | |- | ||
|numeric || || || | |numeric || Functionally equivalent to decimal. || || | ||
|- | |- | ||
|decimal || || || | |decimal || Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. || || | ||
|- | |- | ||
| bit || || || | | bit || Integer data with either a 1 or 0 value || || | ||
|- | |- | ||
|smallint || || 2 bytes || | |smallint || Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). || 2 bytes || | ||
|- | |- | ||
|tinyint || || 1 bytes|| | |tinyint || Integer data from 0 through 255 || 1 bytes|| | ||
|- | |- | ||
| smallmoney || || || | | smallmoney || Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. || || | ||
|- | |- | ||
| money || || | | money || Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. || || | ||
|- | |- | ||
| float || || 4 or 8 bytes || | | float || Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308. || 4 or 8 bytes || | ||
|- | |- | ||
| real || || 4 bytes || | | real || Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38. || 4 bytes || | ||
|- | |- | ||
| date || || || | | date || || || | ||
Line 50: | Line 50: | ||
| datetime2 || || || | | datetime2 || || || | ||
|- | |- | ||
| smalldatetime || || || | | smalldatetime || Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. || || | ||
|- | |- | ||
| datetime || || || | | datetime || Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. || || | ||
|- | |- | ||
| time || || || | | time || || || | ||
|- | |- | ||
| char || || || | | char || Fixed-length non-Unicode character data with a maximum length of 8,000 characters. || || | ||
|- | |- | ||
| varchar || || || | | varchar || Variable-length non-Unicode data with a maximum of 8,000 characters. || || | ||
|- | |- | ||
| next || || || | | next || Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters || || | ||
|- | |- | ||
| nchar || || || | | nchar || Fixed-length Unicode data with a maximum length of 4,000 characters || || | ||
|- | |- | ||
| nvarchar || || || | | nvarchar || Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names. || || | ||
|- | |- | ||
| ntext || || || | | ntext || Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. || || | ||
|- | |- | ||
| binary || || || | | binary || Fixed-length binary data with a maximum length of 8,000 bytes. || || | ||
|- | |- | ||
| varbinary || || || | | varbinary || Variable-length binary data with a maximum length of 8,000 bytes. || || | ||
|- | |- | ||
| image || || || | | image || Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. || || | ||
|- | |- | ||
| cursor || || || | | cursor || A reference to a cursor.|| || | ||
|- | |- | ||
| timestamp || || || | | timestamp || A database-wide unique number that gets updated every time a row gets updated. || || | ||
|- | |- | ||
| hierarchyid || || || | | hierarchyid || || || | ||
|- | |- | ||
| uniquieidentifier || || || | | uniquieidentifier || A globally unique identifier (GUID). || || | ||
|- | |- | ||
| sql_variant || || || | | sql_variant || || || |
Revision as of 21:37, 21 April 2009
Introduction
Previous Design/Implementation
Data model representation
Atomic (Simple) Types
Table Test 1
Type | Description | Storage Bytes | DAP equiv. |
---|---|---|---|
bigint | Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). | 8 bytes | none |
int | Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). | 4 bytes | |
numeric | Functionally equivalent to decimal. | ||
decimal | Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. | ||
bit | Integer data with either a 1 or 0 value | ||
smallint | Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). | 2 bytes | |
tinyint | Integer data from 0 through 255 | 1 bytes | |
smallmoney | Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. | ||
money | Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. | ||
float | Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308. | 4 or 8 bytes | |
real | Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38. | 4 bytes | |
date | |||
datetimeoffset | |||
datetime2 | |||
smalldatetime | Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. | ||
datetime | Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. | ||
time | |||
char | Fixed-length non-Unicode character data with a maximum length of 8,000 characters. | ||
varchar | Variable-length non-Unicode data with a maximum of 8,000 characters. | ||
next | Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters | ||
nchar | Fixed-length Unicode data with a maximum length of 4,000 characters | ||
nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names. | ||
ntext | Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. | ||
binary | Fixed-length binary data with a maximum length of 8,000 bytes. | ||
varbinary | Variable-length binary data with a maximum length of 8,000 bytes. | ||
image | Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. | ||
cursor | A reference to a cursor. | ||
timestamp | A database-wide unique number that gets updated every time a row gets updated. | ||
hierarchyid | |||
uniquieidentifier | A globally unique identifier (GUID). | ||
sql_variant | |||
xml | |||
table |
PostgreSQL Data Types
Boolean and Binary Types
Data Type | Description | Standardization | Logical DAP data type association. |
---|---|---|---|
boolean, bool | A single true or false value. | SQL99 | Boolean |
bit(n) | An n -length bit string (exactly n binary bits). | SQL92 | None |
bit varying(n), varbit(n) | A variable n -length bit string (up to n binary bits) | SQL92 | None |
Character Types
Data Type | Description | Storage | Standardization | Logical DAP data type association. |
---|---|---|---|---|
character (n ), char(n ) | A fixed n -length character string. | (4+n) bytes | SQL89 | String |
character varying(n), varchar(n) | A variable length character string of up to n characters. | Up to (4+n) bytes | SQL92 | String |
text | A variable length character string, of unlimited length. | Variable | PostgreSQL-specific | String |
Numeric Types
Data Type | Description | Storage | Standardization | Logical DAP data type association. |
---|---|---|---|---|
smallint, int2 | A signed 2-byte integer | 2 bytes | SQL89 | Int16 |
integer, int, int4 | A signed, fixed-precision 4-byte number. | 4 bytes | SQL92 | Int32 |
bigint, int8 | A signed 8-byte integer, up to 18 digits in length. | 8 bytes | PostgreSQL-specific | None (Need Int64) |
real, float4 | A 4-byte floating point number. | 4 bytes | SQL89 | Float32 |
double precision, float8, float | An 8-byte floating point number | 8 bytes | SQL89 | Float64 |
numeric(p,s), decimal(p,s) | An exact numeric type with arbitrary precision p, and scale s. | Variable | SQL99 | None |
money | A fixed precision, U.S.-style currency. | 4 bytes | PostgreSQL-specific, deprecated. | None |
serial | An auto-incrementing 4-byte integer. | 4 bytes | PostgreSQL-specific. | None |
Date and Time Types
Data Type | Description | Storage | Standardization | Logical DAP data type association. |
---|---|---|---|---|
date | A calendar date (day, month, year). | 4 bytes | SQL92 | None |
time | The time of day. | 4 bytes | SQL92 | None |
time with time zone | The time of day, including time zone information. | 4 bytes | SQL92 | None |
timestamp (includes time zone) | Both date and time. | 8 bytes | SQL92 | None |
interval | An arbitrary specified length of time | 12 bytes | SQL92 | None |
Geometric Types
Data Type | Description | Storage | Standardization | Logical DAP data type association. |
---|---|---|---|---|
box | A rectangular box in a 2D plane. | 32 bytes | PostgreSQL-specific | None |
line | An infinite line in a 2D plane. | ?? bytes | PostgreSQL-specific | None |
lineseg | A finite line segment in a 2D plane. | 32 bytes | PostgreSQL-specific | None |
circle | A circle with center and radius. | 24 bytes | PostgreSQL-specific | None |
path | Open and closed geometric paths in a two-dimensional plane . | 4+32*n bytes | PostgreSQL-specific | None |
point | geometric point in a 2D plane | 16 bytes | PostgreSQL-specific | None |
polygon | A closed geometric path in a 2D plane | 4+32*n bytes | PostgreSQL-specific | None |
Network Types
Data Type | Description | Standardization | Logical DAP data type association. |
---|---|---|---|
cdir | An IP network specification | PostgreSQL-specific | None |
inet | A network IP address, with optional subnet bits. | PostgreSQL-specific | None |
macaddr | A MAC address (e.g., an Ethernet card's hardware address). | PostgreSQL-specific | None |
System Types
Data Type | Description | Standardization | Logical DAP data type association. |
---|---|---|---|
oid | An object (row) identifier. | PostgreSQL-specific | None |
xid | A transaction identifier | PostgreSQL-specific | None |
Template
Types
Data Type | Description | Standardization | Logical DAP data type association. |
---|---|---|---|