Day: September 11, 2017

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.