Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY

Question

I am puzzled with a query.

I need find out the LAST row added in a table with a column with datatype Uniqueidentifier column is: aspnet_Applications.ApplicationId Notes: This column Uniqueidentifier it is NOT and IDENTITY Column.

I need also take the last row inserted and update it on a different table aspnet_Users.ApplicationId

I tried to use SCOPE_IDENTITY in MS SQL 2008 but does not work because SCOPE_IDENTITY is working only with IDENTITY column.

Here my code. Any ideas?

    CREATE DATABASE Test;
GO
USE Test;
GO

-- Create entities
CREATE TABLE [dbo].[aspnet_Applications](
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoweredApplicationName] [nvarchar](256) NOT NULL,
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [ApplicationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [LoweredApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [ApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_Applications] ADD  DEFAULT (newid()) FOR [ApplicationId]
GO


CREATE TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (newid()) FOR [UserId]
GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (NULL) FOR [MobileAlias]
GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]
GO

-- Add data
DECLARE @MyIdentity binary(16);
INSERT INTO dbo.aspnet_Applications
(
    ApplicationName,
    LoweredApplicationName,
    Description
)
VALUES
(
    'x',
    'x',
    'Dummy text'
);
SET @MyIdentity = SCOPE_IDENTITY(); -- DOES NOT WORK
PRINT @MyIdentity; -- DOES NOT WORK

INSERT INTO dbo.aspnet_Users
(
    ApplicationId,
    UserName,
    LoweredUserName,
    MobileAlias,
    IsAnonymous,
    LastActivityDate
)
VALUES
(   
    @MyIdentity,
    'Administrator',
    'administrator',
    '',
    0,
    sysutcdatetime()
);

Although (IMO) you don't need unique identifiers (identity column would do the job and give you better performance), even with GUIDs you can have clustered primary keys if you use SET @MyIdentity = NewSequentialID(). You can also use NewSequentialID() as default value for ID columns and get inserted values using OUTPUT statement as proposed by Martin. — Niikola, Aug 11 '10 at 16:14

score 14 · Accepted Answer · answered Aug 11 '10 at 11:21

It's a little more work, but for your inserts, even though you already have a DEFAULT value on the ApplicationID, you could do this:

DECLARE @MyIdentity uniqueidentifier;
SET @MyIdentity = NewID();
INSERT INTO dbo.aspnet_Applications
(
    ApplicationName,
    LoweredApplicationName,
    ApplicationId, 
    Description
)
VALUES
(
    'x',
    'x',
    @MyIdentity,
    'Dummy text'
);

SELECT @MyIdentity

Essentially, you set the GUID beforehand, so you already know what you will be inserting.

score 4 · Answer 2 · edited May 23 '17 at 12:08

You could use the OUTPUT clause to get the inserted value(s) back but LBT's answer is probably more straightforward and efficient.

DECLARE @ids table(id uniqueidentifier) 

INSERT INTO dbo.aspnet_Applications
(
    ApplicationName,
    LoweredApplicationName,
    Description
)
OUTPUT INSERTED.ApplicationId  into @ids
VALUES
(
    'x',
    'x',
    'Dummy text'
)

By the way if it wasn't for the Foreign Key you could use composable DML for this as below.

INSERT
INTO   dbo.aspnet_Users
       (
              ApplicationId  ,
              UserName       ,
              LoweredUserName,
              MobileAlias    ,
              IsAnonymous    ,
              LastActivityDate
       )
SELECT ApplicationId  ,
       'Administrator',
       'administrator',
       ''             ,
       0              ,
       sysutcdatetime()
FROM   ( INSERT
       INTO    dbo.aspnet_Applications
               (
                       ApplicationName       ,
                       LoweredApplicationName,
                       Description
               )
               OUTPUT INSERTED.ApplicationId VALUES
               (
                       'x',
                       'x',
                       'Dummy text'
               )
       ) AS I

Be aware that the OUTPUT clause without INTO is not compatible with triggers. We used this kind of solution and then ran into [this error](https://stackoverflow.com/questions/59662266/dml-statement-cannot-have-any-enabled-triggers-if-the-statement-contains-an-outp) when database replication is turned on. — Kasey Speakman, Feb 25 '21 at 19:15

Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY

2 Answers2

Linked