Global Temporary Tables in ASE 16

[Former Member]

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.

[Former Member]

Agree with what you've said.

Users are not able to create index on the global temporary tables. It looks like they can but they have no effect. Only the create table statement is used for the underlying temp table.

So

create global temporary table xxx ( i int primary key )

will create an index that can be used.

We're only aiming to use where we would have used @table variables and more usefully in C# Connection Pools.

Note: I renamed a global temporary table and soon afterwards the server crashed - not sure if the 2 things are connected - so will try this again.

[Former Member]

Just a word of warning.

Renaming a "global temporary table" will result in errors.

It looks like it works

     Object name has been changed.

but sp_help on the new object name doesn't work

     Mismatch found between the name and id descriptor hash table for table 'QQQ',

     objid = 1235076705. Descriptor hashed by name = 0x0 and hashed by id = 0x70a18af

We had a server crash which looks like it could be down to flushing the stats after doing this.

I can't find a way to see what "global temp tables" exist.

     sysobjects has a type "U"

     and sp_help shows "user table" (obviously)

This isn't great for those of us with automated processes - housekeeping, releases, etc.

Note, you can create

     global temporary table

buy just doing

     create table ##TempTable (.... )

If you log out and back in then the table is still visible - but obviously without any data.

[Former Member]

Confirmed : sp_rename on a global temporary table will crash the server

[Former Member]

I'm wrong about it no allowing indexes on global temp tables.

You can create indexes if you create them in the table definition

create global temporary table xxx ( i int primary key )

showplan shows :

       |   |  Forward Scan.

       |   |  Positioning by key.

       |   |  Keys are:

       |   |    i ASC

[former_member89972]

It may be useful for "bcp in" into a table in temp database where you have "bulk copy" database option turned on.  So each session is gauranteed a uniform table definition but the data is for that session only thus minimizing the house keeping chores for an application.

Also can be used as a common scratch pad at table level. I.e. you and me can see the same table definition but the our data is private to each indivdual session.

Avinash

[Former Member]

1) We have thought about using it as a BCP staging table but you obviously need to make sure you use the same session for BCP as the remaining work - so you can't use the command line bcp.

If you're using a ADO ConnectionPool - I don't think this will work.

2) Agree on the scratch pad - we sometime use lists with a spid on them. This makes them much easier.

3) We're also thinking about using using them as interfaces between procs - this saves having to declare temp table outside the procs

     create table #xxxx

     (

     )

     create proc ppppp

     as