Question: I'm using this query:

```
INSERT INTO [LepidoliteDB].[dbo].[EGelLayerWeighingData] ([EGelWeighingDataID], [Amount], [ContainerID], [Portions],
[PortionNumber], [Canceled], [LayerID], [WeighingDone], [InsertDone],
[MeasuresDone], [StartDateAndTime], [EndDateAndTime]) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

SELECT SCOPE_IDENTITY()

```

But `SCOPE_IDENTITY()` always returns 1.

What am I missing?

Answer: 
Best way to write it is ..

```
RETURN  SCOPE_IDENTITY()  

```

You can also use `@@IDENTITY` and `IDENT_CURRENT` for this 

SCOPE\_IDENTITY, IDENT\_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT\_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT\_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT\_CURRENT (Transact-SQL).

SCOPE\_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE\_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Therefore RETURN SCOPE\_IDENTITY() is the best one.
Late to the thread, but if someone is using Dapper ORM and they are doing an UPDATE or INSERT a single row and want to know the primary key of that updated record:

```
var connection = new SqlConnection("ConnectionString");

string sql=@"INSERT INTO [dbo].[Student] ([Name],[Subject])
VALUES (@Name,@Subject)
SET @StudentId=CAST(SCOPE_IDENTITY() as int)";

```

For the above SQL string, if you execute the SQL connection like:

```
Result=connection.Execute(sql,new {Name,Subject});

```

The value of **Result** will be the number of rows affected, in this case only one since we have inserted only one row.
Instead do this:

```
Result=connection.Query<int>(sql,new {Name,Subject}).Single();

```

Now **Result** will have the primary key value of the newly inserted/updated column.

Remember, this answer is only relevant to single row insertion/update