Access 2013 ODBC リンク テーブル と SQL Server - 4

演算 フィールドについて少し寄り道をして。

SELECT Table_1.ID, Table_1.F_Num, Year([F_Date]) AS F_Year, Month([F_Date]) AS F_Month
FROM Table_1;

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" 
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num" ,"F_Date"  FROM "dbo"."Table_1"

演算は ACE もしくは Access で実施となる。

だけど、フィルタリングの条件となる場合、SQL Serverで演算される。なので、取得できるレコードはフィルタされたもの。

SELECT Table_1.ID, Table_1.F_Num, Year([F_Date]) AS F_Year, Month([F_Date]) AS F_Month
FROM Table_1
WHERE Year([F_Date])=2014;

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" WHERE ({fn year("F_Date" )}= 2014 ) 
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num" ,"F_Date"  FROM "dbo"."Table_1" WHERE ({fn year("F_Date" )}= 2014 ) 

使用される関数は、"ODBC スカラー関数"など参照すればよい。といっても、関数の使用を選べるわけではないのでさほど重要なことではない。
Access 独自の関数などの場合は、フィルタに必要なフィールドと主キーのみを取得しフィルタリングした結果、主キーを用いて該当レコードを取得することになる。スナップショットの場合は全レコード取得になる。

SELECT Table_1.ID, Table_1.F_Num, Format([Table_1].[F_Date],'yyyy-mm') AS F_Format
FROM Table_1
WHERE Format([Table_1].[F_Date],'yyyy-mm')="2014-06";

SQLExecDirect: SELECT "F_Date" ,"dbo"."Table_1"."ID" FROM "dbo"."Table_1" 
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num" ,"F_Date"  FROM "dbo"."Table_1"

では、ODBC データソースであるSQL Server側でフィルタされるであろう条件とそうでないものが含まれるクエリの場合どうなるか。

SELECT Table_1.ID, Table_1.F_Num, Format([Table_1].[F_Date],'yyyy-mm') AS F_Format
FROM Table_1
WHERE Table_1.F_Text="A" AND Format([Table_1].[F_Date],'yyyy-mm')="2014-06";

SQLExecDirect: SELECT "F_Date" ,"F_Text" ,"dbo"."Table_1"."ID" FROM "dbo"."Table_1" WHERE ("F_Text" = 'A' ) 
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num" ,"F_Date" ,"F_Text"  FROM "dbo"."Table_1" WHERE ("F_Text" = 'A' )

フィルタが可能なものだけ実施されて、残ったフィルタ条件は、ACE、もしくは Accessでフィルタリングが実施する動作を実行計画で見ることができる。

--- temp query ---

- Inputs to Query -
ODBC table 'Table_1'
- End inputs to Query -

01) Restrict rows of table Table_1
      by scanning
      testing expression "Format([Table_1].[F_Date],'yyyy-mm')="2014-06""