Create FUNCTION Generate ID In SQL

Creating a stored function in SQL Server to generate id from sequence

1. Create Script Function

DELIMITER $$

-- Functions

CREATE FUNCTION getID() 

RETURNS VARCHAR(20)

BEGIN

    DECLARE guid VARCHAR(20);

select 

  case 

    when max(column_id) is null then 'KHS0000001' 

    when max(column_id) is not null 

    then concat('KHS', lpad(cast((cast(substr(max(column_id), 4) as unsigned) + 1) as char), '7', '0')) 

        end 

into guid 

from tabel_name;

RETURN (guid);

END$$

DELIMITER ;

2. Insert ID

nsert into tabel_name (column_id) VALUES(getID());

This article explores the Identity function in SQL Server with examples and differences between these functions.

Overview of IDENTITY columns

In SQL Server, we create an identity column to auto-generate incremental values. It generates values based on predefined seed (Initial value) and step (increment) value. For example, suppose we have an Employee table and we want to generate EmployeeID automatically. We have a starting employee ID 100 and further want to increment each new EmpID by one. In this case, we need to define the following values.

• Seed: 100
• Step: 1

USE SQLSHACKDEMO; GO CREATE TABLE EmployeeData ([id]   [TINYINT] IDENTITY(100, 1) PRIMARY KEY NOT NULL, [Name] [NVARCHAR](20) NULL ) ON [PRIMARY]; GO

Let’s insert a few records in this table and view the records.

You can see the first employee gets an ID value 100 and each new records ID value gets an increment of one.

We have a few useful Identity functions in SQL Server to work with the IDENTITY columns in a table. Let’s explore the following IDENTITY functions with examples.

• SQL @@IDENTITY Function
• SQL SCOPE_IDENTITY() Function
• SQL IDENT_CURRENT Function
• SQL IDENTITY Function

SQL @@IDENTITY Function

We use system function @@IDENTITY to return the maximum used IDENTITY value in a table for the IDENTITY column under the current session. Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session. We cannot use it on a remote or linked server.

Let’s understand it with the following example.

• Step 1: We have a current maximum identity value 110 in EmployeeData table
• Step 2: In the current session, we insert a record in the EmployeeData table. It increments the identity value by one
• Step 3: We can verify that the maximum used identity value is 111
• Step4: We use the SELECT @@IDENTITY function in the current session to get the identity value generated in this session

Let’s insert multiple records in this table and execute the SELECT @@IDENTITY.

If we insert multiple records in a session, SELECT @@Identity returns the maximum IDENTITY value generated in this session. In the following example, we insert three records, and it increments IDENTITY values by three from the current identity value 111.

If the statement did not generate any identity values, it returns NULL values in the output.

SQL SCOPE_IDENTITY() function

We use SCOPE_IDENTITY() function to return the last IDENTITY value in a table under the current scope. A scope can be a module, trigger, function or a stored procedure. We can consider SQL SCOPE_IDENTITY() function similar to the @@IDENTITY function, but it is limited to a specific scope. It returns the NULL value if this function is involved before an insert statement generates value under the same scope.

In the following example, we see that both the @@IDENTITY and SCOPE_IDENTITY() return the same value in the current session and similar scope.

Let’s understand the difference between SCOPE_IDENTITY() and @@IDENTITY with another example.

Let’s consider we have two tables EmployeeData and Departments table. We create an INSERT trigger on the EmployeeData table. Once we insert any row on EmployeeData, it calls to defined trigger for inserting a row in Departments.

Drop table EmployeeData Drop table Departments CREATE TABLE EmployeeData ([id]   [TINYINT] IDENTITY(100, 1) PRIMARY KEY NOT NULL, [Name] [NVARCHAR](20) NULL ) GO CREATE TABLE Departments (DepartmentID   INT IDENTITY(100, 5) PRIMARY KEY, Departmentname VARCHAR(20) NULL ); Go

In the following query, we create a trigger to insert default value ‘IT’ in the departments table for every insert in the EmployeeData table.

CREATE TRIGGER T_INSERT_DEPARTMENT   ON EmployeeData   FOR INSERT AS      BEGIN      INSERT Departments  VALUES ('IT')      END;

Let’s insert value in the Employee data table and view the output of both @@IDENTITY and SCOPE_IDENTITY() functions.

In the current session, we inserted data into the EmployeeData table. It generates an identity in this table. The identity seed value is 1 for the EmployeeData table.

Once we insert value in the EmployeeData table, it fires a trigger to insert value in the Departments table. The identity seed value is 100 for the Departments table.

• We get the output 100 for the SELECT @@IDENTITY function
• SCOPE_IDENTITY function returns identity value under the current scope only. It gives output 1 for this function

SQL IDENT_CURRENT() function

We use the IDENT_CURRENT function to return the last IDENTITY value generated for a specified table under any connection. It does not consider the scope of the SQL query that generates identity value. We need to specify the table for which we want to check the identity value.

In the following screenshot, we can see that we insert data in Session id 64 and it generates identity value 2 in the EmployeeData table.

We can check in another connection window about the current identity value for the EmployeeData table and get the same output as identity value 2.

SELECT IDENT_CURRENT('EmployeeData') AS IdentityValue

SQL IDENTITY Function

In my previous article, we explored SQL SELECT INTO Statement, to create a new table and inserted data into it from the existing table. We can use the SQL IDENTITY function to insert identity values in the table created by SQL SELECT INTO statement.

By default, if a source table contains an IDENTITY column, then the table created using a SELECT INTO statement inherits it. Consider a scenario in which you want to create a table using the SELECT INTO statement from the output of a view or join from multiple tables. In this case, you want to create an IDENTITY column in a new table as well.

• Note: SQL IDENTITY function is different from the IDENTITY property we use while creating any table.

We need to specify a data type for a column to use SQL Identity function. We also need to specify SEED and Step values to define an identity configuration.

We cannot use SQL IDENTITY Function in a Select statement. We get the following error message.

Msg 177, Level 15, State 1, Line 2 The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Let’s create a table using the SQL SELECT INTO statement with the following query.

SELECT TOP (10) IDENTITY( INT, 100, 2) AS NEW_ID,                 [PersonType],                 [NameStyle],                 [Title],                 [FirstName],                 [MiddleName],                 [LastName],                 [Suffix] INTO TEMPTABLE FROM [AdventureWorks2017].[Person].[Person];

Once the statement executes, check the table properties using sp_help command.

sp_help 'TEMPTABLE'

You can see the IDENTITY column in the TEMPTABLE properties as per the specified conditions.

Let’s look at another example. Execute the following command.

SELECT IDENTITY( INT, 100, 2) AS NEW_ID,        ID,        Name INTO temp2 FROM employeedata;

We already have an IDENTITY column in the EmployeeData table. The new table temp2 also inherits the IDENTITY column. We cannot have multiple IDENTITY columns in a table. Due to this, we get the following error message.

Msg 8108, Level 16, State 1, Line 1 Cannot add identity column, using the SELECT INTO statement, to table ‘temp2’, which already has column ‘ID’ that inherits the identity property.

Summary

1. SQL IDENTITY Functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY returns similar output for an IDENTITY columns in current session
2. SCOPE_IDENTITY returns values under the current scope only. We can define scope as a module, Stored procedure, trigger or a function
3. IDENT_CURRENT function returns identity value for a specified table regarding a connection that modified the value
4. We can use the SQL IDENTITY function to define IDENTITY in a table created using SQL SELECT INTO statement

Conclusion

had In this article, we explored the Identity functions in SQL Server. We should be familiar with the functions to use them in writing t-SQL queries. If you have comments or questions, feel free to leave them in the comments below.