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

ID: Q136967


The information in this article applies to:
  • Microsoft SQL Server version 6.0


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 : winnt:6.0
Platform : winnt
Issue type :


Last Reviewed: December 14, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.