» Documentation » SQL Reference

Scope Functions

AUTOIDENTITY

AUTOIDENTITY() 

Returns next identity value. Function only called by statement to insert the new identity value for column(s) that declared the function as default value in CREATE TABLE command.

CREATE TABLE test_ident
(
id bigint not null default( AutoIdentity()),
name varchar(100)
);

go;

insert into test_ident(name) values('test1');
insert into test_ident(name) values('test2');
insert into test_ident(name) values('test2');

select * from test_ident; 

LAST_ROW

LAST_ROW( columnName) 

If previous command updated or inserted a row, we can read the value of the column of the last affected row. Otherwise, function will return NULL.

use test;

declare @id bigint;

--create table with 2 identity columns
create table product(
id1 uniqueidentifier not null primary key,
id2 bigint not null default(AutoIdentity()),
name varchar);

--create table to store products transaction
create table [log]  (
id1 bigint,
id2 bigint,
[time] datetime default(getdate()));

go; --execute create tables

--insert into products, and to log with the identity value of the products row
Insert into product(name) values('myproduct');
set @id = scope_identity();
Insert into [log](id1,id2) values(@id,@id+1);

select last_row(id1), last_row(id2) 

SCOPE_IDENTITY

SCOPE_IDENTITY() 

Returns the last identity value inserted into an incremental column in the same SQL batch scope.

use test;

declare @id bigint;

--create table with 2 identity columns
create table product  (
id1 uniqueidentifier not null primary key,
id2 bigint not null default(AutoIdentity()),
name varchar);

--create table to store products transaction
create table [log]  (
id1 bigint,
id2 bigint,
[time] datetime default(getdate()));

go; --execute create tables

--insert into products, and to log with the identity value of the products row
Insert into product(name) values('myproduct');
set @id = scope_identity();
Insert into [log](id1,id2) values(@id,@id+1);
--return inserted rows
select * from [log] 

@@ROWCOUNT

Returns a 64 bit integer representing the number of rows affected by the last SQL statement. Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the // or by