SQL Server is based off Sybase. They both use Transact SQL. However I've found some differences in behavior (at least in the version of Sybase and driver I'm using).
You cannot do a count in a select in sybase without a group by and get accurate results.
COUNT without GROUP BY:
Select count(some_field) from whatever_table will return every row in the table. That select statement works in SQL Server without the group by. In Sybase you'll need to add group by like this:
select count(some_field) from whatever_table group by (some_field)
COLUMNS and GROUP BY:
If you don't have all the columns you are selecting in the group by you may get incorrect results but no error. In SQL this will give you an error like this:
Column 'some_field' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
In Sybase something like this will just give you inaccurate results:
select some_field, other_field
from whatever_table
group by other_field
EMPTY STRING, LTRIM, LEN
- insert
If you try to insert an empty string using an insert statement with Sybase it inserts a 1 character space instead of an empty string. Length will equal 1.
insert into some_table (id, some_field) values(23, '')
select len(some_field) from some_table where id = 23 (this will give you length of 1)
In SQL Server the above statements would 1.) insert a true empty string and 2.) return a length of 0.
- ltrim and length
In Sybase ltrim(some_field) above would give you a null and len(ltrim(some_field)) will give you null - no matter how many spaces you have in some_field.
In SQL Server the same scenario would give you an empty string after trim and 0 for the length.
