Global Temporary Tables in ASE 16

Just found a really useful feature in ASE 16 (but oddly I can only find 3 lines of documentation in http://help.sap.com/Download/Multimedia/zip-ase1602/SAP_ASE_Whats_New_en.pdf)

Here's what I've found.

Essentially, you can create a definition of a temp table. The actual underlying temp table will get create as you insert into it and will be local to the session.

eg create global temporary table x (i int)

insert into x values ( 10 )

From this session, "select * from x" shows

i

-----------

10

but from other sessions, x will be empty.

Looking at the query plan gives

| | |SCAN Operator (VA = 0)

| | | FROM TABLE

| | | ##x

| | | Table Scan.

| | | Forward Scan.

Here's sp_space for 2 sessions both having inserted into table "x"

##x990371100127 1 128 KB 16 KB 0 KB 112 KB

##x990004000109 2 128 KB 16 KB 0 KB 112 KB

Am sure there are a few cases where this is useful but I think the main one is for communication between Client code and DB.

Using the ADO Client code, the connection pool is awkward when using temp tables. You never know if there are temp tables left lying around (Sybase or ADO won't clear them up), so we have to check if the temp table exists first and then create it if it doesn't and truncate it if it did exist.

Using global temp tables, we know the table always exists, so we can just truncate table (we can't assume its empty as its the connection pool) and insert into it.

[We looked at Table Variables to do this, but with a thousand rows in a table variable, the performance nose-dived]

Caveat: You can "create" indexes on the "global temp table" but they have no effect. If you try and force the index, it will say it doesn't exist.