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

• 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
   
   

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.