Is It Safe?

That's a simple question made memorable by the 1976 movie Marathon Man:

Christian Szell: Is it safe?... Is it safe?
Babe Levy: You're talking to me?
Christian Szell: Is it safe?
Babe Levy: Is what safe?
Christian Szell: Is it safe?
Babe Levy: I don't know what you mean. I can't tell you something's safe or not, unless I know specifically what you're talking about.
Christian Szell: Is it safe?
Babe Levy: Tell me what the "it" refers to.
Christian Szell: Is it safe?
Babe Levy: Yes, it's safe, it's very safe, it's so safe you wouldn't believe it.
Christian Szell: Is it safe?
Babe Levy: No. It's not safe, it's... very dangerous, be careful.

In my case, the question was this:

Is it safe to call GET_IDENTITY ( 'table-name', 0 )?

The SQL Anywhere function call GET_IDENTITY ( 't', 1 ) pre-allocates the next value that would normally be assigned by an INSERT statement to the DEFAULT AUTOINCREMENT column in t, and returns that value to the caller. This is very useful if you need to know what a new AUTOINCREMENT primary key is going to be, before you INSERT the row.

You can pass GET_IDENTITY other numbers, like 2, 3, ..., to have it pre-allocate multiple values and return you the first value.

The Help doesn't talk about passing it zero, but that's what I wanted it to do: Just tell me what the next value is going to be, but don't pre-allocate it... let the next INSERT use it.

Actually, what I really wanted was the last value assigned, which I could get by subtracting:

GET_IDENTITY ( 'table-name', 0 ) - 1

In other words, give me the current AUTOINCREMENT value, the one that was last assigned to some particular table. This is different from @@IDENTITY in two ways:

• @@IDENTITY returns the last AUTOINCREMENT value assigned to any table, whereas GET_IDENTITY() lets you specify which table.

• @@IDENTITY only returns values assigned by the current connection, whereas GET_IDENTITY() doesn't care what connection made the assignment. In other words, @@IDENTITY remembers the last value assigned by the current connection, whereas GET_IDENTITY() will return values assigned by other connections.

That last point is one you should consider carefully. If it's important to you, you may be better off calling GET_IDENTITY ( 't', 1 ) before doing the INSERT, because the value that is pre-allocated by GET_IDENTITY ( 't', 1 ) is protected from work done by other connections, and you are safe to specify it in the INSERT.

However, you may be looking for a faster alternative to SELECT MAX ( t.c ), which may be slow because:

• there's no index on the DEFAULT AUTOINCREMENT column, or

• there's an index but it's not useful because the DEFAULT AUTOINCREMENT column is not the first column.

It turns out that yes, it is safe to call GET_IDENTITY with zero in the second argument. Here's some code that shows how GET_IDENTITY ( 'table-name', 0 ) - 1 returns the same value as SELECT MAX ( t.c ):

CREATE TABLE t ( c INTEGER DEFAULT AUTOINCREMENT );

SELECT GET_IDENTITY ( 't', 0 ) - 1, 
       MAX ( t.c ) 
  FROM t;

INSERT t VALUES ( DEFAULT );

SELECT GET_IDENTITY ( 't', 0 ) - 1, 
       MAX ( t.c ) 
  FROM t;

INSERT t VALUES ( DEFAULT );

SELECT GET_IDENTITY ( 't', 0 ) - 1, 
       MAX ( t.c ) 
  FROM t;

Here are the three result sets; note that the first SELECT returns NULLs because there are no rows in t yet:

GET_IDENTITY('t',0)-1,MAX(t.c)
(NULL),(NULL)

GET_IDENTITY('t',0)-1,MAX(t.c)
1,1

GET_IDENTITY('t',0)-1,MAX(t.c)
2,2