IDENTITY() function in SQL

Question

I saw an SP where the identity function is called in the following manner:

select id = identity(10) ..... into ..... from ......

Can any one please let me know if I can pass only 1 or 2 parameters instead of the three parameters (the SEED, INCREMENT, datatype)?

Also if a column in a table is already defined as the identity doesnt it mean that it will automatically generate a unique sequence number? Then what exactly is the need for this identity() function?

Answer 1

Sybase 12.0.1 documentation here says:

IDENTITY function [Miscellaneous]

Generates integer values, starting at 1, for each successive row in a query. Its implementation is identical to that of the NUMBER function.

Syntax

IDENTITY( expression ) Parameters

• expression An expression. The expression is parsed, but is ignored during the execution of the function.

Returns

INT

Remarks

The description of the IDENTITY function is the same as the description of the NUMBER function.

See also

•NUMBER function [Miscellaneous]

Standards and compatibility

• SQL/2008 Vendor extension.

Example

The following statement returns a sequentially-numbered list of employees.

SELECT IDENTITY( 10 ), Surname FROM Employees;

Sybase, unlike MS SQL Server, only handles one argument. The seed and increment are always 1.

If there are any identity columns in the data being selected then their values will be copied over. The IDENTITY function allows you to create a new identity column in the output table.