Parent-Child Relationships And Native Compilation

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.

Related Posts

T-SQL Tuesday 117 Roundup

Steve Jones has the roundup for this month’s T-SQL Tuesday: The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic. Either everyone is on vacation […]

Read More

Pitfalls with Window Functions

Itzik Ben-Gan takes us through two issues you might run into when using window functions: There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930