Ned Otter looks at different ways to insert data with parent-child relationships using natively compiled, memory-optimized procedures:
This blog post demonstrates various approaches when using native compilation to insert rows into parent/child tables.
First, let’s create tables named Parent and Child, and relate them with a FOREIGN KEY constraint. Note that the Parent table uses the IDENTITY property for the PRIMARY KEY column.
Ned’s first example uses @@IDENTITY and shows that this doesn’t work. But there’s an easy version which is superior to @@IDENTITY and is supported: SCOPE_IDENTITY(). Even outside of memory-optimized tables, you want to use SCOPE_IDENTITY() over @@IDENTITY anyhow because of the risk of triggers changing the “current” identity value. Here’s a quick example I ginned up using SCOPE_IDENTITY:
CREATE TABLE dbo.Parent
(
Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED,
SomeChar CHAR(1) NOT NULL
)
WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO
CREATE TABLE dbo.Child
(
Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED,
ParentId INT NOT NULL,
SomeChildChar CHAR(1) NOT NULL
)
WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO
CREATE PROCEDURE dbo.TestParentChild
(
@SomeChar CHAR(1),
@SomeChildChar CHAR(1)
)
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
INSERT INTO dbo.Parent
(
SomeChar
)
VALUES
(
@SomeChar
);
INSERT INTO dbo.Child
(
ParentId,
SomeChildChar
)
VALUES
(
SCOPE_IDENTITY(),
@SomeChildChar
);
SELECT
p.Id,
p.SomeChar
FROM dbo.Parent p;
SELECT
c.Id,
c.ParentId,
c.SomeChildChar
FROM dbo.Child c;
END;
GO
EXEC dbo.TestParentChild
@SomeChar = 'A',
@SomeChildChar = 'B';
EXEC dbo.TestParentChild
@SomeChar = 'Z',
@SomeChildChar = 'Y';
EXEC dbo.TestParentChild
@SomeChar = 'W',
@SomeChildChar = 'L';
The results are what you’d expect.