DBMS/DataWindow Column Dependencies

  SQL Server Select
Equivalent Oracle Select
Equivalent SQL AnyWhere Select
Equivalent MS Access Select

Column
Size
1 select space( 200 )
select lpad( ' ', 200 ) from dual
select space( 200 )
select space( 200 ) from test

200
200
200
255
2 select ltrim( space( 200 ) )
select ltrim( lpad( ' ', 200 ) ) from dual
select ltrim( space( 200 ) )
select ltrim( space( 200 ) ) from test

200
0
200
255
3 select space( :n )
select lpad( ' ', :n ) from dual
select space( :n )
select space( :n ) from test

0
0
32767
255
4 select space( :n + 200 )
select lpad( ' ', :n + 200 ) from dual
select space( :n + 200 )
select space( :n + 200 ) from test

255
200
32767
255
5 select ltrim( space( :n + 200 ) )
select ltrim( lpad( ' ', :n + 200) ) from dual
select ltrim( space( :n + 200 ) )
select ltrim( space( :n + 200 ) ) from test

255
0
32767
255

Conclusion:

SQL Server (Sybase and Microsoft): Guess method; reports largest possible size, or 0 when unable to guess
Oracle: Calculate method; reports smallest possible size
Sybase SQL AnyWhere: Guess method; reports largest possible size
MS Access: Don't bother to calculate or guess; always reports largest possible size for the column's data type


Notes:
Tests were compiled using the following software:
1.  Sybase SQL Server 11.0.2
2.  MS SQL Server 6.5
3.  Oracle 8.00.3
4.  Sybase SQL AnyWhere 5.0 via ODBC driver 5.05
5.  MS Access 7.0 via ODBC driver 3.51(Test table was created for this test, since MS Access requires FROM part in SELECT statement)