TechTutorials - Free Computer Tutorials  

SQL 7.0 ANSI-92 Compatibility 

Added: 09/05/2001, Hits: 4,899, Rating: 0, Comments: 0, Votes: 0
Add To Favorites | Comment on this article
By Brian Talbert

SQL 7 is, for the most part, compliant with the ANSI standard, SQL92.  However, there are a few minor options that must be configured to allow for a completely compatible set of behaviors.  This is a rather complex topic and the decision to use any of the options mentioned here should only be made with a complete understanding of how the setting will affect your application. You should consult Books Online for further information regarding these settings as some settings are enabled by default when you connect via ODBC.

ANSI-92 requires that an equal ( = ) or not equal ( <> ) comparison, where one of the elements being compared is NULL, to always evaluate to FALSE. Therefore, a query such as SELECT * FROM MYTABLE WHERE MYCOLUMN = NULL, should return zero rows.  However, SQL 7 will actually return all rows where MYCOLUMN is actually NULL. When SET ANSI_NULLS is turned ON, a comparison to a NULL will always be unknown, as ANSI-92 requires. Refer to the Books Online topic, "SET ANSI_NULLS (T-SQL)" for more information.

This one is a bit more complex. In a nutshell, though, ANSI-92 specifies that when data is inserted into a fixed width binary or character type column, that the data be padded with 0's or spaces, respectively, when the length of the inserted data is less than that of the column width.   Conversely, if values are inserted into
variable-width columns, they should be padded. SQL 7 pretty much does this, however, it also trims data of trailing spaces and zeros when inserted in a variable width column. It also does this trimming on fixed-width columns if the column allows NULLS. When SET ANSI_PADDING is turned ON, SQL 7 will operate as defined by ANSI-92. Refer to the Books Online topic, "SET ANSI_PADDING (T-SQL)" for more information.

When turned ON, SET ANSI_WARNINGS, causes an error to be generated when a query attempts to aggregate values that include NULLS. When ON it also causes divide by zero/arithmetic overflow errors to result in a statement being rolled back, instead of returning a NULL value. One of the effects of this is that when data is inserted into a character or binary column and the length of the data is greater than the width of the column, an arithmetic overflow occurs.  With ANSI_WARNING set to ON, such an action would be canceled. When  OFF, the data is truncated and inserted.  Refer to the Books Online topic, "SET ANSI_WARNINGS (T-SQL)" for more information.

This setting closes any cursors on a commit when set ON. Otherwise, cursors are not closed when a transaction commits.  

Normally, SQL 7 allows you to use either single quotes ( ' ) or double quotes ( " ) to delimit a literal expression. When this setting is turned ON, however, SQL 7 follows ANSI-92 guidelines by requiring literals to be enclosed by single quotes.  Double quotes are then used to enclose identifiers (database object names) and identifiers can then violate the normal rules for identifiers, such as the use of keywords or special symbols. Refer to the Books Online topic, "SET QUOTED_IDENTIFIER (T-SQL)" as well as "Using Identifiers" for more information.

This setting causes new columns created to default to allow nulls, when the null option for the column is not otherwise explicitly stated.  Refer to the Books Online topic "SET ANSI_NULL_DFLT_ON (T-SQL)" for more information.

Turns on all previously mentioned ANSI related settings. Refer to the Books Online topic, "SET ANSI_DEFAULT (T-SQL)" for more information.

Note, it is highly advisable that you read the recommended topics in Books Online.  Many of these settings also exist as properties of the database. It is important to understand how they relate.

Comments (0)

Be the first to comment on this article

Related Items

7 Seconds Resources, Inc.

IT Showcase