SQL and Composite Keys: Creating and selecting rows whose primary key is a composite key and returning the unique, auto-incrementing ID associated to that primary key

Purpose
The purpose of this tutorial is to show how to create a temporary SQL table with a composite key consisting of three columns that is the primary key, while attaching a separate unique, auto-incrementing ID to that primary key. This page will also show how to perform an insert as well as a select with that composite key.

First, create the temporary SQL Table:

	CREATE TABLE #sTest
	(
	TargetID INT NOT NULL IDENTITY(1, 1),
	ResourceID INT NOT NULL,
	PreviousOwnerID BIGINT NOT NULL,
	NewOwnerID BIGINT NOT NULL,
	InsertedDate DATETIME NOT NULL DEFAULT CAST(GETDATE() AS DATETIME),
	-- Each column forms the primary key index
	CONSTRAINT TertiaryID PRIMARY KEY CLUSTERED (
		ResourceID
		,PreviousOwnerID
		,NewOwnerID
		)
	);

Second, insert a few rows:

	INSERT INTO #sTest (ResourceID, PreviousOwnerID, NewOwnerID) VALUES
	(1,1,1)
	,(1,1,2)
	,(2,1,3)
	,(2,2,3)
	-- ,(1,1,1) -- Insert fails if more than one row has all 3 of the same values in the columns
	;

Third, return the unique ID attached to the last composite key row that was inserted:

	SELECT @@IDENTITY; -- Returns TargetID of last row inserted

Fourth, perform a select:

	SELECT TargetID, InsertedDate FROM #sTest WHERE (ResourceID = 1 AND PreviousOwnerID = 1 AND NewOwnerID = 2);

And last, don't forget to drop the temporary table:

	-- While newer versions of SQL support DROP IF EXISTS syntax, this older method will work as well if backwards-compatibility is a concern
	IF OBJECT_ID('tempdb..#sTest') IS NOT NULL
	BEGIN
		DROP TABLE #sTest;
	END