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:
- 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.
- 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:
- The above script executed on SQL 4.2x will return both rows.
- The above script executed on SQL 6.0 with the trace flag switched
off will return only the first row.
- The above script executed on SQL 6.0 with the trace flag switched
on will (as in SQL 4.2x) return both rows.
- 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.
- 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 :