Scope_identity() for GUIDs?

Unveiling the 'OUTPUT magic'

How to stash multiple GUIDs

When you're inserting numerous items, the OUTPUT clause becomes a lifesaver. It captures all the unique identifiers into a table variable or a temporary table:

DECLARE @InsertedGuids TABLE (Id UNIQUEIDENTIFIER);

INSERT INTO YourTable (Col1, Col2)
OUTPUT INSERTED.Id INTO @InsertedGuids
VALUES 
('Value1', 'Value2'),  -- hey, I'm batch number one! 🎉
('Value3', 'Value4');  -- and I'm number two, nice to meet you! 🤝


Now, @InsertedGuids holds and keeps warm all new GUIDs for any operations that you plan next.

Sequence, all in order!

To be sure in generating Sequential GUIDs, which perfectly fit for fast primary keys, make amazing friends with the NewSequentialId() function:

ALTER TABLE YourTable ADD DEFAULT NewSequentialID() FOR IdColumn  -- I prefer my GUIDs sorted. 🏆


This code ensures IdColumn defaults to employers' best friend — sequential GUIDs.

C# to the rescue!

If you feast with .Net, the ExecuteScalar method allows to fetch directly the GUID primary key right after the insertion, no extra SQL required:

Guid newGuid = (Guid)command.ExecuteScalar();  // Good job C#, you're the man of the hour! 🦾


Keep an index eye

Remember GUIDs as primary keys have a substantial effect on your table's indexing. If they are not in line (NEWSEQUENTIALID()), they might drift towards index fragmentation.

Trigger it away!

Consistency is a virtue. Consider a trigger for your peace of mind. It manages to insert and capture GUIDs simplifying the retrieval process across multiple inserts.

Diving into details

Aligning data types

If you use the OUTPUT INTO clause, ensure your column types in the table variable or temporary table match those in the target table. Misalignment can lead to a SQL tantrum!

Cleanup operation

Using a temporary table? Don't forget to DROP TABLE once you've got your last inserted GUID nicely tucked in your pocket:

DROP TABLE IF EXISTS #TempGuids;  -- Bye bye table, you've been very helpful. 👋


Measuring up: NEWID() vs. NEWSEQUENTIALID()

NEWID() creates non-sequential, random GUIDs. They're unique alright, but may introduce performance issues due to index fragmentation. Consider NEWSEQUENTIALID() for smooth index behavior.

Playing smart with uniqueidentifier

While uniqueidentifier types give you global uniqueness, they are larger (16 bytes) and slower to compare than integers. Weigh up the trade-offs for your application.

OUTPUT nuances

To know all nuances of the OUTPUT clause, dive into the trusty T-SQL documentation. Equip yourself to implement solutions that fit your scenarios like a tailored suit.