資料類型對應和分散式查詢
OLE DB 提供者將根據稱為 DBTYPE 的 OLE DB 類型識別碼來公開其資料的資料類型。在 OLE DB 資料類型與 SQL Server 系統資料類型之間,資料類型是透過對應資料來轉換:
從 OLE DB 資料類型到 SQL Server 系統資料類型。此轉換會在 SQL Server 從 OLE DB 資料來源讀取資料時發生 (在 SELECT 陳述式中,或在 UPDATE、INSERT 或 DELETE 陳述式的讀取端)。
從 SQL Server 系統資料類型到 OLE DB 資料類型。此轉換會在 SQL Server 將資料寫入 (大多在 INSERT 或 UPDATE 陳述式中) OLE DB 資料來源時發生,且所修改的資料表為遠端資料表。
從 OLE DB 提供者到 SQL Server 的資料類型對應
從 OLE DB 提供者到 SQL Server 的資料類型對應定義了所允許的比較與運算式,以及牽涉到遠端資料的有效明確轉換。此對應顯示於下表中。
運算式中遠端資料表資料行的類型有效性可由下列規則摘要說明:在相同的內容中,若相對應的對應 SQL Server 資料類型在「資料類型對應」資料表中有效,則 Transact-SQL 運算式中的遠端資料行值為有效。
例如,請看運算式:local_columnOPERATOR remote_column。在此運算式中,local_column 是一個本機資料表資料行,而 remote_column 則是遠端資料表資料行。如果 OPERATOR 對於本機資料行的資料類型,以及 remote_column 的 DBTYPE 對應的資料類型而言是有效運算子,則此運算式有效。
同樣地,如果 remote_column 的 DBTYPE 對應到 SQL Server 系統資料類型 data_type_2,且允許 data_type_2 明確轉換成 data_type_1 的話,則允許 CAST(remote_column AS data_type_1)。例如,提供者端的 DBTYPE_DATE 資料類型資料行可轉換成 SQL Server 中的 datetime 資料行。不過,DBTYPE_DATE 資料並無法直接轉換成 varchar。
下表顯示資料類型對應資料表。您可以使用 DBTYPE 指標及其資料行的 DBCOLUMNFLAGS 值,找到對應的 SQL Server 資料類型。
DBTYPE |
DBCOLUMNFLAGS |
SQL Server 資料類型 |
---|---|---|
DBTYPE_I1 |
numeric(3, 0)1 |
|
DBTYPE_I2 |
smallint |
|
DBTYPE_I4 |
int |
|
DBTYPE_I8 |
bigint |
|
DBTYPE_UI1 |
tinyint |
|
DBTYPE_UI1 |
numeric(5,0) |
|
DBTYPE_UI1 |
numeric(10,0) |
|
DBTYPE_UI1 |
numeric(20,0) |
|
DBTYPE_R4 |
float |
|
DBTYPE_R8 |
real |
|
DBTYPE_NUMERIC |
numeric |
|
DBTYPE_DECIMAL |
decimal |
|
DBTYPE_CY |
money |
|
DBTYPE_BSTR |
DBCOLUMNFLAGS_ISLONG = true |
ntext |
DBTYPE_BSTR |
DBCOLUMNFLAGS_ISFIXEDLENGTH = true |
nchar |
DBTYPE_BSTR |
DBCOLUMNFLAGS_ISFIXEDLENGTH = false |
nvarchar |
DBTYPE_IDISPATCH |
Error |
|
DBTYPE_ERROR |
Error |
|
DBTYPE_BOOL |
bit |
|
DBTYPE_VARIANT |
nvarchar(4000) |
|
DBTYPE_IUNKNOWN |
Error |
|
DBTYPE_GUID |
uniqueidentifier |
|
DBTYPE_BYTES |
DBCOLUMNFLAGS_ISLONG = true,或最大資料行大小 > 8,000 位元組。 |
image |
DBTYPE_BYTES |
DBCOLUMNFLAGS_ISLONG = true,且資料行大小的長度不限。 |
varbinary(max) |
DBTYPE_BYTES |
DBCOLUMNFLAGS_ISROWVER = true、DBCOLUMNFLAGS_ISFIXEDLENGTH = true,且資料行大小 = 8 |
timestamp |
DBTYPE_BYTES |
DBCOLUMNFLAGS_ISFIXEDLENGTH = true |
binary |
DBTYPE_BYTES |
DBCOLUMNFLAGS_ISFIXEDLENGTH = false |
varbinary |
DBTYPE_STR |
DBCOLUMNFLAGS_ISFIXEDLENGTH = true |
char |
DBTYPE_ STR |
DBCOLUMNFLAGS_ISFIXEDLENGTH = false |
varchar |
DBTYPE_STR |
DBCOLUMNFLAGS_ISLONG = true,或最大資料行大小 > 8,000 個字元。 |
text |
DBTYPE_STR |
DBCOLUMNFLAGS_ISLONG = true,且資料行大小的長度不限。 |
varchar(max) |
DBTYPE_WSTR |
DBCOLUMNFLAGS_ISFIXED |
nchar |
DBTYPE_WSTR |
DBCOLUMNFLAGS_ISFIXEDLENGTH = false |
nvarchar |
DBTYPE_WSTR |
DBCOLUMNFLAGS_ISLONG = true,或最大資料行大小 > 4,000 個字元。 |
ntext |
DBTYPE_WSTR |
DBCOLUMNFLAGS_ISLONG = true,且資料行大小的長度不限。 |
nvarchar(max) |
DBTYPE_UDT |
Equivalent SQL Server 使用者定義型別 (如果有註冊的話)。 |
|
DBTYPE_DATE |
date, datetime, datetime2, datetimeoffset |
|
DBTYPE_DBDATE |
date, datetime, datetime2, datetimeoffset (相容性層級小於 9.0)。 |
|
DBTYPE_DBTIME |
time, datetime, datetime2, datetimeoffset |
|
DBTYPE_DBTIME_EX |
time, datetime2, datetimeoffset |
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset, datetime |
|
---|---|---|
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset |
|
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset |
|
DBTYPE_ARRAY |
Error |
|
DBTYPE_BYREF |
忽略 |
|
DBTYPE_VECTOR |
Error |
|
DBTYPE_RESERVED |
Error |
|
DBTYPE_XML |
xml (只限於傳遞查詢)。 |
1numeric(p,s) 表示 SQL Server 資料類型 numeric,有效位數是 p,小數位數是 s。
[!附註]
若資料必須轉換成所顯示之預設值以外的 SQL Server 資料類型,則需要明確轉換 (使用 CAST 或 CONVERT 函數)。如需詳細資訊,請參閱<CAST 和 CONVERT (Transact-SQL)>。
DBTYPE 指標和 DBCOLUMNFLAGS 值之資訊,可透過 COLUMNS 結構描述資料列集或透過 IColumnsInfo 介面從提供者取得。若為 COLUMNS 結構描述資料列集,DATA_TYPE 和 COLUMN_FLAGS 資料行代表 DBTYPE 和 DBCOLUMNFLAGS 值。若為 IColumnsInfo::GetColumnInfo 介面,DBCOLUMNINFO 結構的 wType 和 dwFlags 成員代表這些值。
從 SQL Server 到 OLE DB 提供者的資料類型對應
使用上表所顯示的對應,可將 SQL Server 系統資料類型對應到 OLE DB 類型。當下列任一條件存在時,允許從 SQL Server 類型 S1 對應到特定的 OLE DB 類型 T:
相對應的對應可以在資料類型對應資料表中找到。
資料類型 S1 到另一個 SQL Server 資料類型 S2 的隱含轉換是允許的,而且在資料類型對應資料表中,有定義 S2 到 T 的對應。
從遠端伺服器接收的 SQL Server 2008 日期和時間日期的對應
下表顯示從 OLE DB 資料來源傳送的資料與 SQL Server 2008 執行個體的 date 和 time 資料類型對應。此轉換會在 SQL Server 2008 從 OLE DB 資料來源讀取資料時發生 (在 SELECT 陳述式中,或在 UPDATE、INSERT 或 DELETE 陳述式的讀取端)。如果遠端資料行屬於 date、time、dateime2 或 datetimeoffset 資料類型,則當資料庫相容性層級為 100 或更高時,將會傳回該類型。如果相容性層級低於此數字,SQL Server 2008 會隱含轉換成 datetime。
OLE DB 類型 |
如果遠端伺服器是 SQL Server 2008 傳回: |
如果遠端伺服器是 SQL Server 2005 或 SQL Server 2000 則傳回: |
如果遠端伺服器不是 SQL Server,而且本機 SQL Server 2008 資料庫相容性層級是 90 則傳回: |
如果遠端伺服器不是 SQL Server,而且本機 SQL Server 2008 資料庫相容性層級是 100 則傳回: |
---|---|---|---|---|
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime (遠端資料行可以是 datetime 或 smalldatetime) |
datetime (遠端資料行可以是 datetime 或 smalldatetime) |
datetime |
datetime2(7) |
DBTYPE_DBTIMESTAMP,將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime2 |
不適用 |
不適用 |
不適用 |
DBTYPE_DBDATE |
date |
不適用 |
datetime |
date |
DBTYPE_DBTIME |
time(0) |
不適用 |
datetime |
time(0) |
DBTYPE_DBTIME2 |
time(n) |
不適用 |
不適用 |
不適用 |
DBTYPE_DBTIMESTAMPOFFSET |
datetimeoffset |
不適用 |
不適用 |
不適用 |
傳送給遠端伺服器的 SQL Server 2008 日期和時間日期的對應
下表顯示從 SQL Server 2008 執行個體傳送的資料與 OLE DB 資料目的地的 date 和 time 資料類型對應。此轉換會在 SQL Server 2008 將資料寫入 OLE DB 資料來源 (大多在 INSERT 或 UPDATE 陳述式中),且所修改的資料表為遠端資料表時發生。
SQL Server 2008 資料類型 |
如果遠端伺服器是 SQL Server 2008 繫結: |
如果遠端伺服器是 SQL Server 2005 或 SQL Server 2000 則繫結: |
如果遠端伺服器不是 SQL Server 則繫結: |
---|---|---|---|
datetime, smalldatetime |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime2(n) |
DBTYPE_DBTIMESTAMP,將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
date |
DBTYPE_DBDATE |
DBTYPE_DBDATE |
DBTYPE_DBDATE |
time(0) |
DBTYPE_DBTIME |
DBTYPE_DBTIME |
DBTYPE_DBTIME |
time(n) (0 < n <= 7) |
DBTYPE_DBTIMESTAMP (有暫止日期部分) 將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP,不將 dwFlags 設定為 DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP (有暫止日期部分) |
datetimeoffset |
DBTYPE_DBTIMESTAMPOFFSET |
不適用 |
不適用 |
使用日期和時間資料類型執行 SQL Server 2008 遠端查詢
SQL Server 2008 執行的查詢會參考具有 time、date、datetime2 或 datetimoffset 資料類型的遠端物件 (在本機或遠端伺服器上)。此決策取決於遠端伺服器的版本或提供者及參考的類型。所考量的參考類型為遠端資料行或常數、變數或本機資料行。
非 SQL Server 提供者
當非 SQL Server 提供者的遠端伺服器上有類似類型存在時,對於 time、date 和 datetime2 資料類型的支援是部分的。這些提供者沒有一個方式可宣告對於這些類型的支援。
下表顯示查詢是在遠端還是本機伺服器上執行。第一欄顯示本機伺服器上的資料類型。第二欄顯示 SQL Server 2008 的本機執行個體用於遠端伺服器的對應 OLE DB 資料類型。最後三欄顯示此查詢是在遠端還是本機伺服器上執行。執行位置取決於遠端伺服器的版本或類型。
本機伺服器資料類型 |
本機 SQL Server 2008 使用的遠端伺服器 OLE DB 資料類型 |
SQL Server 2008 或更新版本的遠端伺服器 |
SQL Server 2005 遠端伺服器 |
非 MSSQL 遠端伺服器 |
---|---|---|---|---|
datetime |
DBTYPE_DBTIMESTAMP |
遠端 |
遠端 |
遠端 |
smalldatetime |
DBTYPE_DBTIMESTAMP |
遠端 |
遠端 |
遠端 |
datetime2 |
DBTYPE_DBTIMESTAMP |
遠端 |
本機 |
遠端 |
datetimeoffset |
DBTYPE_DBTIMESTAMPOFFSET |
遠端 |
本機 |
本機 |
date |
DBTYPE_DBDATE |
遠端 |
本機 |
遠端 |
time(>0) |
DBTYPE_DBTIME2 |
遠端 |
本機 |
本機 |
time(0) |
DBTYPE_DBTIME2 |
遠端 |
本機 |
遠端 |