I've been exploring my options for an app that's currently running on MS SQLServer 2000. I loaded up Sybase Adaptive Server 12.5 and proceeded to compile the Stored Procedures. Wrote a Python script to auto-convert some of the inconsistencies between T-SQL. I've successfully created the database and compiled all the procs, but I did have to play some tricks in the Python script to get around some issues. The following is mostly me thinking out loud.

1). UPDATE/DELETE with Self-Join: Dealing mostly with tree structured data, I have to do a lot of self joins. In MS, I can specify which self the UPDATE/DELETE operation is modifying by using the alias instead of the table name:

UPDATE child\nSET afield = parent.afield\nFROM\n   atable child,\n   atable parent\nWHERE (child.ParentID = parent.SelfID)

Sybase doesn't support the use of alias in this fashion. I can write the query as:

UPDATE atable\nSET afield = parent.afield\nFROM\n   atable child,\n   atable parent\nWHERE (child.ParentID = parent.SelfID)

but I'm not sure how Sybase goes about resolving which of the two selfs is being modified. Perhaps the new ANSI syntax would help along these lines?

2). REPLACE: MS has a string replace function that I use in a dozen or so scripts. The form is:

SELECT REPLACE("DEF", "123", "DEFABCDEF")

which would yield a result of "123ABC123". I don't see a similar function in Sybase. I suppose I could use the pattern and substring function in a cursor to achieve the same results. But I'd rather not have to code a loop.

3). Maximum Table Size: Sybase has an upper limit of 1936 bytes per row in table definitions. MS had the same limit in v6.5, up it to 4k in v7.0 and 8k in v2000. Unfortunately, I've got some tables that bump into the 8k head room. I suppose I could divide the table into 2 or more tables within the stored procedures and do a dual read & write in the access to the data. Wondering, though, whether Sybase has a way to expand the maximum row size? Also wondering whether the query results of a stored procedure have the same limitation of 1936 bytes?

4). Maximum of 16 SubQueries: As of v7.0, MS supports 32 SubQueries. I have a couple of queries that do look up chores for the reporting that are using 20 subqueries. Can code around the issue using temporary tables and splitting the results as needed.

5). TABLE Variables: MS introduced the idea of table variables in v2000. These vars act very similar to Temporary Tables, but without the transaction overhead. The python script fixed these by simply converting them into the equivalent temporary tables. The only thing that is missing is that the temp tables are not explicitly closed (as table vars do not support that operation). I'm assuming, though, that temp tables are implicitly closed when the stored procedure completes.

6). CURSOR syntax: The syntax of the cursor statements are slightly different between the two databases. They are, however, equivalent in statement order and structure. The python script easily handles the conversion.

7). IDENTITY type: Sybase requires that the type for an identity column be NUMERIC. I had the columns as type INT. Simple conversion of type was all that was required.

8). TINYINT DEFAULT(0): Not sure why Sybase doesn't allow defaults on 1byte ints. Not really a problem for the conversion, as I only use it in one place and can easily code around it. But I'm not sure I understand the logic or maybe there's a way to achieve the effect that I'm missing.

9). User Defined Functions: I can't see where Sybase currently supports function definition (either a straight up function that returns a single value or parameterized view). I only use a handful of functions and they are fairly simple, so it's not a big problem for the conversion.

I can't Sybase to accept the alias in the UPDATE clause, either as a standalone (like MS) or the TableName AlisName form.

I usually don't like the ANSI join syntax, as it always seems more cumbersome - especially when joining multiple tables (not to mention I've never figured out a decent way to do the indentation). In this case, though, the ANSI syntax should clarify which self is being updated:

UPDATE atable\nSET afield = parent.afield\nFROM \n   atable child\n   INNER JOIN atable parent\n      ON child.ParentID = parent.SelfID

I do use nested set trees in the application. But I also keep parent/child pointers in the table, as some things run faster on one or the other - depending on what you are trying to poll.

Nested sets are definitely the shizz. Admin provided me with some sample T-SQL code last year that helped me get over the performance hump in the application. Probably wouldn't have quite my previous job without 'em. :-)

Working outdoors all day putting up the new shed and the brain went to mush. :-)