IDENTITY, SEQUENCE and GUID

Generating identifiers does not create a business sequence.

UnderstandStudy an examplePractise

Understand the concept

IDENTITY generates numbers for a table; use a primary-key or unique constraint to enforce uniqueness. Deletions, rollbacks and restarts can leave gaps. Do not expect an identity value to provide gapless accounting document numbers.

SEQUENCE is independent of a table and may serve multiple objects; it also does not guarantee gapless numbering. GUIDs can simplify distributed identity generation, but their size and randomness affect indexes. Internal keys, document numbers and ordering are different requirements.

WHEN TO USE IT

Use it when choosing identifiers and designing distributed data.

COMMON PITFALL

Treating IDENTITY as a gapless business counter.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT NEWID() AS NewGuid;

Generates a uniqueidentifier, not a sequential document number.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Distinguish a GUID type from an order key type.

Show the solution and explanation

Worked solution

SELECT SQL_VARIANT_PROPERTY(NEWID(), 'BaseType') AS GuidType;

Returns uniqueidentifier; the lab OrderId is int.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.