T-SQL and ANSI/ISO SQL
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:
Some of the extensions that Microsoft provides in T-SQL to provide it with a competitive advantage include the following:
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: