How to retrieve a particular identifier when multiple tables have identifier columns

What is an Identity Column? An identity column is a method to build primary keys. Like an auto-number column or sequence column in other databases, the identity column in SQL Server generates consecutive integers as new rows are inserted into the database. Advantages of using Identity column as primary keys are:

• Integers are easier to manually recognize and edit than GUIDs.
• Integers are small and fast.
• An Identity column used as a primary key with a clustered index (a common, but poor practice) may be extremely fast when retrieving a single row with a single user. However, that configuration will cause lock-contention hot spots on the database.

Identity column values are created by SQL Server as the row is being inserted. Note that for the Identity column the NOT NULL option is essential.

How to Create an Identity Column for a Table:

Select the identity column in the table view and expand Identity specification in the Column Property window and then set the property Is Identity to Yes.

Type a value in the Identity increment cell. This value is the increment that is added to the Identity Seed for each subsequent row. The default value is 1.

Type value in Identity Seed cell. This value is assigned to the first row in the Table. Default value is 1.

Identity Column does not guarantee that the value used as an identifier will be unique throughout the database across tables. Note that the data type to be selected for Identity columns are int, smallint, tinyint, decimal or numeric. Below is the code for creating unique identifiers.

CREATE TABLE mySchema.Customer (
CustID INT  IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(30) UNIQUE NOT NULL,
City VARCHAR(50) NOT NULL
)
ON [Primary]

We need to retrieve an identifier of a row of data in a table to insert the same value in that table or another table to maintain relationships.

Normally, we use the function @@IDENTITY which returns the last identity value that was inserted in the database. But, what we need is the identity value of a particular table in which the last record was inserted. We use UDFs to accomplish this task. A typical UDF code snippet is given below:

CREATE   FUNCTION GetComIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('com_msg'))
END

CREATE TABLE admin.com_msg (
    [id_msg] [int] IDENTITY (1, 1) NOT NULL ,
    [idtop_msg] [int] NOT NULL ,
    [idmsg_msg] [int] NULL ,
    [id_init_msg] [int] NOT NULL CONSTRAINT  [DF__com_m__id_in__4257997F] DEFAULT ([admin].[GetComIdentity]()),
    [brief_msg] [varchar] (200) NULL,
     PRIMARY KEY  CLUSTERED
    (
        [id_msg]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO