Comparing ANSI-92 SQL, T-SQL, and Jet SQL

Jet SQL and T-SQL don't include many of the approximately 200 keywords incorporated in the ANSI standard for SQL-92. Most of the common SQL keywords missing from Jet's implementation are provided by the expressions you create with operators, built-in functions, or user-defined functions you write in Access's VBA flavor. Understanding the similarities and differences between Jet and ANSI-92 SQL is important when you upsize Jet applications to ADP. This knowledge is even more important if you take the "planned migration" route, described near the beginning of the chapter, to upsizing your database front ends.

ANSI-92 SQL Reserved Words in Jet SQL

Access doesn't support all the ANSI SQL keywords with identical reserved words in the Jet SQL language, but each update to Jet converges on the SQL-92 standard, as does each successive version of SQL Server. In this chapter, keywords are defined as the commands and functions that make up the vocabulary of the ANSI SQL language. Jet SQL commands and functions are referred to here as reserved words to distinguish them from ANSI SQL.

The tables in the following two sections also are intended to acquaint readers who are familiar with ANSI or similar implementations of SQL in other RDBMSs or database front-end applications with the Access implementation of Jet SQL and SQL Server 2000's version of T-SQL. If you haven't used any version of SQL, the tables demonstrate that SQL is a relatively sparse language, having far fewer keywords compared to programming languages such as VBA, and that Jet SQL is even more sparse. Jet SQL has few reserved words to learn. T-SQL implements most ANSI-92 keywords and has many useful additions to ANSI-92 SQL.

Jet SQL Reserved Words Corresponding to ANSI SQL and T-SQL Keywords

Access supports the ANSI SQL and T-SQL keywords listed in Table 22.2 as identical reserved words in Jet SQL. Don't use these reserved words as the names of tables, fields, or variables. The reserved words in Table 22.2 appear in all capital letters in the SQL statements Access creates for you when you design a query or when you add a graph to a form or report. Reserved words marked with an asterisk were introduced by Access 2000 and are accessible only from VBA code and, if you're using ActiveX Data Objects (ADO), require a reference to the Microsoft ADO Ext. 2.x for DDL and Security library (Msadox.dll).

Table 22.2. Reserved Words Common to ANSI-92 SQL, Jet SQL, and T-SQL

ADD

ALIAS

ALL

ALTER

ANY

AS

ASC

AVG

BEGIN*

BETWEEN

BY

CHECK*

COLUMN*

COMMIT*

CONSTRAINT

COUNT

CREATE

DELETE

DESC

DISALLOW*

DISTINCT

DROP

EXISTS

FOREIGN

FROM

HAVING

IN

INDEX

INNER

INSERT

INTO

IS

JOIN

KEY

LEFT

LIKE

MAX

MIN

NOT

NULL

ON

OR

ORDER

OUTER

PARAMETERS

PRIMARY*

PROCEDURE

REFERENCES

RIGHT

ROLLBACK*

SELECT

SET

SOME*

TRANSACTION*

UNION

UNIQUE

UPDATE

VALUE

VALUES

VIEW

WHERE

  

The keywords that relate to data types, CHAR[ACTER], FLOAT, INT[EGER], and REAL, aren't included in Table 22.2 because Jet SQL uses a different reserved word to specify these SQL data types (refer to Table 22.4 later in this chapter). The comparison operators (=, <, <=, >, and =>) are common to both ANSI SQL and Jet SQL. Access and T-SQL substitute the <> operator for ANSI SQL's not-equal (!=) operator.

As in ANSI SQL, the IN reserved word in Jet SQL can be used as an operator to specify a list of values to match in a WHERE clause or the result set of a subquery.

Jet Functions and Operators Used in Place of ANSI SQL Keywords

Table 22.3 shows reserved words in Jet SQL that correspond to ANSI SQL keywords but are operators or functions used in Jet SQL expressions. Jet doesn't use ANSI SQL syntax for aggregate functions; for example, you can't use the SUM(DISTINCT field_name) or AVG(DISTINCT field_name) syntax of ANSI SQL. Jet distinguishes between its use of the Sum() aggregate function and the SQL implementation, SUM(), by initial letter capitalization. Expressions that use operators such as And and Or are enclosed in parentheses in Jet SQL statements; Jet SQL uses uppercase AND and OR when criteria are added to more than one column.

Table 22.3. Jet SQL Reserved Words That Substitute for ANSI SQL Keywords

Jet SQL

ANSI SQL

Jet SQL

ANSI SQL

And

AND

Max()

MAX()

Avg()

AVG()

Min()

MIN()

Between

BETWEEN

Not

NOT

Count()

COUNT()

Null

NULL

Is

IS

Or

OR

In

IN

Sum()

SUM()

Like

LIKE

  

The Wizard upsizes Jet SQL Like expressions for DateTime values to the ANSI SQL LIKE operator, as mentioned near the beginning of this chapter. For example WHERE DateColumn Like "*/*/1997" upsizes to WHERE DateColumn LIKE '%/%/1997'. (The next section discusses differences between Jet and ANSI SQL wildcard characters.) T-SQL statements containing LIKE constraints for columns of the datetime datatype fail to return rows. If you attempt to edit the LIKE expression, you receive a "Your entry cannot be converted to a valid date time value" message. This use of LIKE is valid in T-SQL; T-SQL statements containing LIKE datetime constraints work fine in the SQL Query Analyzer or from OSQL. The problem with LIKE datetime expressions in the project designer appears to stem from the SQL Server OLE DB provider (SQLOLEDB).

The Jet IsNull() function that returns True ( 1) or False (0), depending on whether IsNull()'s argument has a Null value, has no equivalent in ANSI SQL and isn't a substitute for IS NULL or IS NOT NULL qualifiers in WHERE clauses.

Jet SQL doesn't support distinct aggregate function references, such as AVG(DISTINCT field_name); the default DISTINCTROW qualifier added to the SELECT statement by Jet serves this purpose.

Jet SQL Reserved Words, Operators, and Functions Not in ANSI SQL

Jet SQL contains a number of reserved words that aren't ANSI SQL keywords (see Table 22.4). Most of these reserved words define Jet data types; some reserved words have equivalents in ANSI SQL and T-SQL, and others don't. You use Jet DDL reserved words to establish or modify the properties of tables and columns. Jet SQL's DISTINCTROW modifier is described in the following section. Jet uses PIVOT and TRANSFORM to create the crosstab queries that are unique to Jet databases.

Table 22.4. Jet SQL Reserved Words Not in ANSI SQL

Jet SQL

ANSI SQL

Category

Purpose

BINARY

No equivalent

DDL

Not an official Jet field data type

BOOLEAN

No equivalent

DDL

Jet Yes/No field data type

BYTE

No equivalent

DDL

Byte field data type, 1 byte integer

CURRENCY

No equivalent

DDL

Jet Currency field data type

DATETIME

No equivalent

DDL

Jet Date/Time field data type

DISTINCTROW

No equivalent

DQL

Updatable Jet Recordset objects

DOUBLE

REAL

DDL

High-precision decimal numbers

LONG

INT[EGER]

DDL

Long Integer field data type

LONGBINARY

No equivalent

DDL

OLE Object field data type

LONGTEXT

VARCHAR

DDL

Memo field data type

OWNERACCESS

No equivalent

DQL

Run with owner's privileges parameters

PIVOT

No equivalent

DQL

Used in crosstab queries

SHORT

SMALLINT

DDL

Integer field data type, 2 bytes

SINGLE

No equivalent

DDL

Single-precision real number

TEXT (n)

CHAR[ACTER]

DDL

Text field data type

TRANSFORM

No equivalent

DQL

Creates crosstab query

&

||(two pipe symbols)

DQL

String concatenation

? (LIKE wildcard)

_(wildcard)

DQL

Single with LIKE character

* (LIKE wildcard)

% (wildcard)

DQL

Zero or more characters

# (LIKE wildcard)

No equivalent

DQL

Single digit, 0 9

# (date specifier)

No equivalent

DQL

Encloses date/time values

<> (not equal)

!=

DQL

Jet uses ! as an object name separator

Jet provides four statistical aggregate functions that aren't incorporated in ANSI SQL. These functions are listed in Table 22.5.

Table 22.5. Aggregate SQL Functions in Jet SQL But Not ANSI SQL

Jet Function

Category

Purpose

StdDev()

DQL

Standard deviation of a population sample

StdDevP()

DQL

Standard deviation of a population

Var()

DQL

Statistical variation of a population sample

VarP()

DQL

Statistical variation of a population

Jet's DISTINCTROW and ANSI SQL's DISTINCT Keywords

Jet SQL's DISTINCTROW reserved word that follows the SQL SELECT keyword causes Jet to eliminate duplicated rows from the query's result. The effect of DISTINCTROW is especially dramatic in queries used to display records in tables that have indirect relationships. As mentioned earlier in the chapter, you're likely to encounter DISTINCTROW in Jet queries created prior to Access 97.

DISTINCTROW is related to, but not the same as, the DISTINCT keyword in ANSI SQL. Both words eliminate duplicate rows of data in query result tables, but they differ in execution. DISTINCT in ANSI SQL eliminates duplicate rows based only on the values of the data contained in the rows of the query, from left to right. You cannot update values from multiple-table queries that include the keyword DISTINCT.

DISTINCTROW eliminates duplicate rows based on the content of the underlying table, regardless of whether additional field(s) that distinguish records in the table are included. DISTINCTROW allows values in special kinds of multiple-table Recordset objects to be updated.

Note

You can use the Unique Table property value of a form's record source to make most one-to-many queries updatable. Specify the name of the table on the many side of the relationship as the value of the Unique Table property.


To distinguish between these two keywords, assume that you have a table with a LastName field and a FirstName field and only 10 records, each with the LastName value, Smith. Each record has a different FirstName value. You create a query that includes the LastName field, but not the FirstName field. DISTINCTROW returns all 10 Smith records because the FirstName values differ in the table. DISTINCT returns one record because the FirstName field that distinguishes the records in the table is absent in the query result table.

Versions before Access 97 included the default reserved word DISTINCTROW unless you purposely replaced it with the DISTINCT keyword by using the Query Properties dialog's Unique Values Only option. The Jet Query Properties dialog sets the default value of the Unique Values (DISTINCT) and Unique Rows (DISTINCTROW) properties to No. Don't specify Unique Rows in Jet queries you intend to upsize to ADP.

Jet and Corresponding SQL Server Data Types

SQL Server has more data types than those specified by the ANSI-92 standard (refer to Table 22.2). For example, ANSI-92 doesn't require conforming RDBMSs to support Unicode characters. Jet 4.0 and SQL Server both support Unicode data types; SQL Server identifies Unicode data type by an n (for National Character) prefix.

The Upsizing Wizard automatically converts Jet data types to corresponding SQL Server 2000 data types. Table 22.6 lists Jet data types, the SQL Server data type to which the Wizard converts the Jet data type, and SQL Server data types that are related to the upsized data type. Parentheses enclose SQL Server property or extended property values that must be set to emulate Jet data types.

Table 22.6. Jet 4.0 Data Types, Directly Corresponding, and Related SQL Server 2000 Data Types

Jet Data Type

Upsizes To

Related Data Types

Yes/No

bit

 

Number (Byte)

tinyint

 

Number (Integer)

smallint

 

Number (Long Integer)

int

 

Number (Single)

real

 

Number (Double)

float

 

Number (Decimal)

decimal

numeric

Number (Replication ID) (GUID)

uniqueidentifier

 

AutoNumber

int (Identity)

 

Currency

money

smallmoney

Date/Time

datetime

smalldatetime

Text(n)

nvarchar(n)

varchar(n)

Hyperlink

ntext (Hyperlink)

Memo

ntext

text

OLE Object

image

 

Jet doesn't have data types that correspond to SQL Server's bigint, char, nchar, sql_variant, user-defined, varchar, and varbinary data types.

VBA Functions That Upsize to SQL Server Functions

T-SQL has many functions that correspond to VBA functions that you commonly use in Jet queries. Table 22.7 lists the VBA functions that the Upsizing Wizard converts to their T-SQL counterparts. The table doesn't include the SQL functions listed earlier in Tables 22.2 and 22.3. You can safely use these VBA functions in Jet queries that you plant to upsize to SQL Server.

Table 22.7. VBA Functions That the Wizard Automatically Converts to T-SQL Functions

Ccur

Hour

Right$

Cdbl

Lcase$

Right

Chr$

Lcase

Rtrim$

Chr

Left

Second

Cint

Len

Space$

Clng

Ltrim$

Str$

Csng

Mid$

Ucase$

Cstr

Mid

Ucase

Cvdate

Minute

Weekday

Day

Month

Year

VBA Functions That You Must Manually Convert to Related SQL Server Functions

The Upsizing Wizard doesn't automatically convert the VBA functions listed in Table 22.8 into the corresponding T-SQL functions that perform similar or identical operations. In most cases, the reason the Wizard doesn't perform the translation is minor syntax differences. Eliminate, if possible, or minimize use of these functions in Jet queries you plant to upsize.

Table 22.8. VBA Functions That the Wizard Automatically Does Not Convert to T-SQL Functions

VBA Function

T-SQL Function

VBA Function

T-SQL Function

Asc

ASCII

Lower

LOWER

Date

GETDATE

Now

GETDATE

DateAdd

DATEADD

Space

SPACE

DateDiff

DATEDIFF

Str

STR

DatePart

DATEPART

String

REPLICATE

Format

DATENAME

StrReverse

REVERSE

Instr

CHARINDEX

  

The Format function is one of the most commonly used VBA functions in Jet queries, and DATENAME only handles one of many possible Format expressions.

For examples of the use of T-SQL DATEPART , DATENAME , DATEDIFF , and GETDATE functions, see "The Better Approach for Dynamic and Static Data Stored Procedures," p. 923.


Tip

SQL Server 2000 Books Online has the standard syntax for T-SQL functions and examples of their use. Click the Index tab and type the function name in the text box to open the Transact-SQL Reference topic for the function. The "Date and Time Functions" topic has links to all functions that accept or return date or time values. If you haven't downloaded the updated SQL Server 2000 Books Online, you can find it at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net