The ANSI SQL standard has evolved over the years and provides standards for commands for data definition, data access, and data manipulation. Each new iteration of the ANSI SQL standard incorporates more features and capabilities than the previous standards. The most recent version of the ANSI SQL standard is SQL-99. ANSI SQL-92 was the last major ANSI SQL standard released. Three levels of compliance are defined for the ANSI SQL standards: entry level, intermediate, and full.

SQL Server 2000 is entry-level compliant with the ANSI SQL-92 standard. What this means is that SQL Server provides all of the core features defined in the ANSI SQL-92 standard to achieve any level of compliance. In addition, SQL Server already provides some features defined in the ANSI-99 standard, such as user-defined roles.

Like any vendor that develops a product that conforms to a standard, Microsoft incorporates additional features into SQL Server to extend the capabilities of the SQL language. Examples of such features include the following:

  • Operating-system?dependent statements, such as defining physical database storage.

  • Legacy syntax and commands?Even if the current version supports the ANSI way of expressing a command, backward compatibility is still necessary.

  • Extensions to the ANSI standards?All vendors strive to implement competitive features. Microsoft is no exception.

Some of the extensions that Microsoft provides in T-SQL to provide it with a competitive advantage include the following:

  • Server-resident programs such as user-defined functions, stored procedures, and triggers

  • Control of flow statements

  • Additional datatypes, including user-defined datatypes

  • Various types of built-in integrity, such as rules, defaults, and triggers

  • Additional built-in functions

Some of the commands in SQL Server 2000 existed before a SQL standard existed for them, or the SQL standard has been modified over the years and has redefined the way certain commands are supposed to function. For some of these commands, SQL Server supports both the old style syntax and behavior while also supporting the new ANSI style syntax and behavior. Providing this sort of backward compatibility helps to ease the process of upgrading database and applications from previous versions of SQL Server. However, in certain instances, the default behavior of some commands is now the ANSI behavior?to have the T-SQL command mimic the old-style behavior might require setting a session level option.

For example, in versions of SQL Server prior to 7.0, when you concatenated a NULL value with a character string, the query returned the character string. The ANSI standard states it should return a NULL, so the default behavior in SQL Server 7.0 was modified to return NULL. Needless to say, this wreaked havoc with SQL code that was expecting the old behavior. To get 7.0 or SQL Server 2000 to mimic the old behavior, you need to set the database compatibility level to version 6.5 with the sp_dbcmptlevel dbname, 65 statement, or turn the new behavior off with the SET CONCAT_NULL_YIELDS_NULL OFF command.

Although the various SET options and sp_dbcmptlevel help ease the migration process from earlier versions of SQL Server, it is strongly encouraged that you become familiar with and begin using the newer ANSI standard features in SQL Server. This is a good idea for two primary reasons:

  • It allows your application code to take advantage of new capabilities available only with some of the ANSI SQL commands.

  • Support for the older style syntax and behavior might no longer be supported in future releases of SQL Server.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features