create proc mytrigproc 
as
  select @@trancount "@@trancount in proc"
  create table tempdb..xxx (xxx_column1 int)
  select * from tempdb..xxx
go

create table mytab (a int)
go

create trigger mytrigger on mytab for insert
as
  while @@trancount > 0 commit
  exec mytrigproc
go

1> insert mytab values(1)
2> go
 @@trancount in proc
 -------------------
                   0

(1 row affected)

 xxx_column1
 -----------

(0 rows affected)

• First, the trigger commits up until the outermost transaction. Yes, you can do this in a trigger! (though it's not a good idea)

• Then the trigger executes the stored proc. ASE actually checks whether a trigger contains a create table or select...into statement, and raises an error message during create trigger. But when you put the table creation in a stored proc, ASE won't notice.

• Obviously, when inserting another row, the table already exists (but that's not the point here)

1> create table mytable (a int)
2> go
1> select * from mytable
2> go
 a
 -----------

(0 rows affected)

1> select count(*) from syscolumns 
2> where id = object_id("mytable")
3> go

 -----------
           2

(1 row affected)

1> create table mytable2 (a char(5), SYB_IDENTITY_COL int)
2> go
Msg 7368, Level 15, State 2
Server 'SYBASE', Line 1
Can not create column by name 'SYB_IDENTITY_COL'. This 
name has special meaning when 'auto_identity' option is ON.

1> create table mytable2 (a char(5), b int)
2> go
1> sp_rename 'mytable2.b', SYB_IDENTITY_COL
2> go
Column name has been changed.
(return status = 0)

1> select * from mytable2
2> go
 a
 -----

(0 rows affected)

1> insert mytable2 values ("Row 1", 1)
2> insert mytable2 values ("Row 2", 2)
3> insert mytable2 values ("Row 3", 3)
4> go
(1 row affected)
(1 row affected)
(1 row affected)

1> select * from mytable2
2> go
 a
 -----
 Row 1
 Row 2
 Row 3

(3 rows affected)

1> sp_rename 'mytable2.SYB_IDENTITY_COL', b
2> go
Column name has been changed.
(return status = 0)

1> select * from mytable2
2> go
 a     b
 ----- -----------
 Row 1           1
 Row 2           2
 Row 3           3

(3 rows affected)

• sp_reportstats reports the amount of CPU and I/O spent by each login. sp_clearstats resets the counters for these statistics.
  These two procedures are very old -- they were already there when I first worked with ASE in 1989. They are very much remnants of the mainframe era, where individual CPU seconds were charged to your project budget. My guess is that these stored procedures are hardly ever used by anyone today (in fact, did anyone ever use these procs at all?).

• sp_flushstats has absolutely nothing to do with the two procedures mentioned above. sp_flushstats was introduced in 11.9, and explicitly flushes the in-memory statistics for a specific table to systabstats (these statistics are things like the rowcount, cluster ratio information, etc.; not the histogram & density information generated by update statistics). This flushing is normally done automatically by the housekeeper task, but in case the housekeeper is disabled, you can force the data to be flushed.

• sp_modifystats allows you to manipulate the density statistics in sysstatistics, which were generated by update statistics.
  Using this procedure makes sense only when you have a very good understanding of the ins and outs of the ASE query optimizer. Typically, you'll be the kind of person who has read everything Eric Miner has written.
  This procedure was introduced in later versions of 11.9, and new functionality was added in 12.5.

• sp_object_stats is in a completely different area. It will tell you on which tables the most lock contention occurs. This procedure is extremely useful when diagnosing concurrency problems.
  Unlike the 4 procedure above, which you may never use, sp_object_stats is a must-know for anyone who's interested in ASE performance. This procedure was introduced 11.9.

select @X - ((@X/100)*100)

1> declare @X numeric(10)
2> select @X = 123
3> select @X - ((@X/100)*100)
4> go

 ------------------------------------
                        0.00000000000

1> declare @X int
2> select @X = 123
3> select @X - ((@X/100)*100)
4> go

 -----------
          23

1> declare @X numeric(10)
2> select @X = 123
3> select (@X/100)*100, @X/100
4> go

------------------ -----------------
   123.00000000000     1.23000000000

1> declare @X numeric(10)
2> select @X = 123
3> select @X - (floor(@X/100)*100)
4> go

 -------------------------
                        23

select stdev(age) from students
-- stdev() is an aggregate to calculate standard deviation

begin tran
lock table MyTable in exclusive mode
-- explicitly locks a table