Use SQL Server to obtain the ID of the inserted record (automatic number)

Recently, I encountered a problem in the development project, that is, after inserting a record, I need to immediately obtain the ID in the database, which is auto-incrementing. How can I do this?There are several methods available in SQL server 2005.
The simplest way to obtain this ID is to select @ indentity after the query.
-- SQL statement to create databases and tables
Copy codeThe Code is as follows:
Create database dbdemo
Go
Use dbdemo
Go
Create table tbldemo
(
Id int primary key identity (1, 1 ),
Name varchar (20)
)
Go

-- Execute the following SQL statement to check the values of the auto-incrementing column corresponding to the inserted record.
Insert into tbldemo values ('test') select @ identity
SQL Server 2000 has three similar functions: SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY. They both return values inserted into the IDENTITY column.

1) IDENT_CURRENT returns the final id value generated for any session and a specific table in any scope. It is not restricted by the scope and session, but by the specified table.
2) @ IDENTITY returns the final id value generated for any table in all scopes of the current session.
3) SCOPE_IDENTITY returns the last generated id value for the current session and any table in the current scope.

SCOPE_IDENTITY and @ IDENTITY return the last id value generated in any table in the current session. However, SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.
For example, there are two tables T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.

Assume that both T1 and T2 have the IDENTITY column. @ IDENTITY and SCOPE_IDENTITY return different values at the end of the INSERT Statement on T1. @ IDENTITY returns the value of the last IDENTITY column inserted to any scope in the current session. This value is the value inserted in T2.