INF: Effective Use of Trace Flag 204 for ANSI Non-standard Ext

ID: Q136967

The information in this article applies to:

SUMMARY

Microsoft SQL Server version 6.0 provides a trace flag 204 for portability of version 4.2x SQL scripts. This trace flag is intended to aid customers in the short term as they modify transact-SQL code to be more ANSI compliant.

MORE INFORMATION

SQL Server version 4.2x facilitates the following as non-ANSI extensions:

1. Additional column references in the SELECT list other than the ones 

   in the GROUP BY clause are permitted.

   For example:

      use pubs
      go
      select pub_id, pub_name, count(pub_id)
      from publishers
      group by pub_name
      go

   The above non-ANSI query is not permitted in version 6.0 because
   the SELECT list has certain entries that are non-aggregates and are not
   present in the GROUP BY list. Trace flag 204 will allow the above query
   to run as it did in SQL Server 4.2x.
2. Trailing blanks are not significant and will not affect queries that 
   include LIKE in the WHERE clause.

   For example:

      use pubs
      go
      drop table table1
      go
      create table table1( col1 text )
      go
      insert into table1 values ("John   ")
      go
      insert into table1 values ("John")
      go
      select * from table1 where col1 like 'John %'
      /* Note the blank space in the literal above*/ 
      go
NOTES:

1. The above script executed on SQL 4.2x will return both rows.

2. The above script executed on SQL 6.0 with the trace flag switched 

   off will return only the first row.
3. The above script executed on SQL 6.0 with the trace flag switched 
   on will (as in SQL 4.2x) return both rows.
4. This will affect only the text data type and not character and 
   variable character datatypes. For example, the behavior of LIKE in a
   where clause with char or varchar is not affected by this trace flag.
5. Turn on trace flag 204 (during server startup, use the -T204 option), or 
   individual users can enable this functionality by using DBCC TRACEON
   (204) to allow NON-ANSI behavior at the connection level.
Additional query words: sql6 
Keywords          : kbusage SSrvProg SSrvTran_SQL 
Version           : 6.0
Platform          : WINDOWS

Last Reviewed: May 2, 1997