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.
Comments closed