@@IDENTITY vs SCOPE_IDENTITY()

by Andrei Hetel 30. November 2008 16:16

SCOPE_IDENTITY was introduced by Microsoft in SQL Server 2000. This article will try to explain the difference between @@IDENTITY and SCOPE_IDENTITY() using a very simple example rather than complicated definitions.

So, let's create 2 tables:

 

CREATE TABLE Agenda
(
  AgendaId INT IDENTITY(1,1) NOT NULL,
  [Name] VARCHAR(30),
  Surname VARCHAR(30)
)

 

Please note that identity start at 1.

 

CREATE TABLE DbLog
(
  DblogId INT IDENTITY(10,1) NOT NULL,
  LogText TEXT
)

 

Please note that identity start at 10.
Next, let's create a trigger for insert into Agenda table. It will simply log the entry into DbLog table.

 

CREATE TRIGGER dbo.AgendaLog ON Agenda AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON
  DECLARE @Name VARCHAR(30)
  DECLARE @Surname VARCHAR(30)

  SELECT @Name=[Name], @Surname=Surname FROM inserted
  INSERT INTO DbLog (LogText)
  VALUES ('Agenda item: Name=' + ISNULL(@Name, '') + ' Surname=' + ISNULL(@Surname, ''))
END
GO

 

And finally, a simple stored procedure that will insert a record into Agenda table:

 

CREATE PROCEDURE dbo.NewAgendaEntry
  (@Name VARCHAR(30), @Surname VARCHAR(30))
AS
BEGIN
   SET NOCOUNT ON;

  INSERT INTO Agenda (Name, Surname)
  VALUES (@Name, @Surname)

  PRINT 'IDENTITY VALUE=' + CAST(@@IDENTITY AS VARCHAR)
  PRINT 'SCOPE_IDENTITY VALUE=' + CAST(SCOPE_IDENTITY() AS VARCHAR)

END
GO

 

Let's run the stored procedure:

EXEC NewAgendaEntry 'John', 'Doe'

 

Result: IDENTITY VALUE=10; SCOPE_IDENTITY VALUE=1
Difference can be explained like this:
@@IDENTITY - returns the most recently created identity (in our case for DbLog table)
SCOPE_IDENTITY() - will return the last identity value that you explicitly created
Recommended is to always use SCOPE_IDENTITY() to return the identity of the recently added row.

 

Category: SQL Server, IDENTITY, SCOPE_IDENTITY()
Tags: , ,
Comments (2)
E-mail Kick it! DZone it! del.icio.us Permalink Post RSS

Web services - returning custom objects

by Andrei Hetel 13. November 2008 14:12

This article is focused on web services, more exactly about the way you can return a custom object into your client application (in my case a windows mobile application). The easiest way is to use a simple example. On web service project let's create a simple class with only 2 properties CastleId, CastleName:

 

<Serializable()> _
Public Class CCastle

  Private _CastleId As Int64
  Private _CastleName As String

  Public Sub New()
  End Sub
  Public Sub New(ByVal newCastleId As Int64, ByVal newCastleName As String)
    _CastleId = newCastleId
    _CastleName = newCastleName
  End Sub

  Public Property CastleId() As Int64
    Get
      Return _CastleId
    End Get
    Set(ByVal value As Int64)
      _CastleId = value
    End Set
  End Property
  Public Property CastleName() As String
    Get
      Return _CastleName.Trim
    End Get
    Set(ByVal value As String)
      _CastleName = value
    End Set
  End Property

End Class

 

Please note that the class is marked as serializable and the properties are public read/write, to be able to use it into the client application created later.

Now, let's create a collection class, also serializable:

 

<Serializable()> _
Public Class CCastleList
  Private _CastleList As List(Of CCastle)

  Public Property CastleList() As List(Of CCastle)
    Get
      Return _CastleList
    End Get
    Set(ByVal value As List(Of CCastle))
      _CastleList = value
    End Set
  End Property
End Class

 

Finally, web method:

 

<WebMethod()> _
Public Function CastleList() As CCastleList

  Dim cl As New CCastleList
  Dim myList As New List(Of CCastle)

  myList.Add(New CCastle(1, "Castle1"))
  myList.Add(New CCastle(2, "Castle2"))
  cl.CastleList = myList
  Return cl

End Function

 

That's all on the web service side. Now, let's create a client application, reference the web service and call the web method. Like this:

 

Public Sub LoadCastleList()

  Dim ws As localhost.YsaMain
  Dim cl As localhost.CCastleList
  Dim castle As localhost.CCastle

  Try
    ws = New localhost.YsaMain
    cl = ws.CastleList()

    For Each castle In cl.CastleList
      debug.writeline (castle.CastleId & " " & castle.CastleName)
    Next castle

  Catch ex As Exception
  End Try
End Sub

 

Category: ASP.NET, .NET, VB.NET
Tags: ,
Comments (0)
E-mail Kick it! DZone it! del.icio.us Permalink Post RSS

.NET Code Profiler

by Andrei Hetel 6. November 2008 04:28

Have you ever dream to see how well your .NET code is performing? Yes, I do. Today I find it here: .NET Profiler - looks pretty well and on top of this it's free. Check video demonstration available on their site and review your code. Good luck!

Category: .NET
Tags: ,
Comments (0)
E-mail Kick it! DZone it! del.icio.us Permalink Post RSS