What is the fastest way to find duplicate uniqueidentifier in Sql Server?

Question

We use a uniqueidentifier for each entry in a very large database. For business reasons, we need to make sure that uniqueidentifier never used more than once, but for performance reasons we have bigint as the primary key .

What is the fastest way to check for the existence of uniqueidentifer in an Sql server table?

Answer 1

**

<0.05 ms to verify a unique identifier of 100,000,000 rows on one standard S0 Sql Azure instance.

**

DISCLAIMER: The following example may require customization of your business requirements and is currently only performed in the Sql Azure staging environment on an S0 instance (10 DTU) . The purpose is to prove the concept.

(see below for CREATE To patterns)

Method: Create a table specifically designed to hold any uniqueidentifiers existing in your database. This table will be highly optimized for the sole purpose of increasing our uniqueidentifer validator.

The table will have four columns. One for uniqueidentifier , and the other three as single binary digits of calculated columns to store each of the three uniqueidentifier digits.

Then we will create a clustered index in the first three-digit columns, and uniqueidentifer as the last column of the clustered index.

Finally, we will create a stored procedure that takes a uniqueidentifer and displays the first three digits as a binary data type and performs a search using the structure of the data tree B on the disk according to our cluster index.

CREATE To table (with clustered index):

 USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[UniqueIds]( [Guid] [uniqueidentifier] NOT NULL, [Char01] AS (CONVERT([char](1),substring(CONVERT([char](36),[Guid]),(1),(1)))) PERSISTED NOT NULL, [Char02] AS (CONVERT([char](1),substring(CONVERT([char](36),[Guid]),(2),(1)))) PERSISTED NOT NULL, [Char03] AS (CONVERT([char](1),substring(CONVERT([char](36),[Guid]),(3),(1)))) PERSISTED NOT NULL, CONSTRAINT [PK_UniqueIds] PRIMARY KEY CLUSTERED ( [Char01] ASC, [Char02] ASC, [Char03] ASC, [Guid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) GO SET ANSI_PADDING OFF GO 

Stored procedure for verification:

 CREATE PROCEDURE [dbo].[UniqueIds.CountIds] @Guid uniqueidentifier, @IdCount bigint OUTPUT AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Break out uniqueidentifier down into the first three characters for indexed lookup. DECLARE @SubChar char(3) = CONVERT([char](36),@Guid); DECLARE @Char01 char(1) = @SubChar; DECLARE @Char02 char(1) = SUBSTRING(@SubChar,2,1); DECLARE @Char03 char(1) = RIGHT(@SubChar,1); -- Check if GUID already exists (SELECT TOP 1 @IdCount=1 FROM UniqueIds WHERE Char01=@Char01 AND Char02=@Char02 AND Char03=@Char03 AND [Guid]=@Guid); 

Stored Procedure for INSERTS:

 CREATE PROCEDURE [dbo].[UniqueIds.Insert] @Guid uniqueidentifier AS BEGIN TRY BEGIN TRAN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Check if GUID already exists DECLARE @IdCount bigint; EXEC [UniqueIds.CountIds] @Guid=@Guid, @IdCount = @IdCount OUTPUT IF @IdCount IS NULL INSERT INTO UniqueIds ( [Guid] ) VALUES ( @Guid ) ELSE THROW 60000, '[Guid] must be unique. Another unique identifier with the same signature exists.', 1; COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH 

Insert a new uniqueidentifier (example):

 DECLARE @id uniqueidentifier SET @id=NEWID() EXEC [UniqueIds.Insert] @Guid=@id 

Final word on implementation:

Each time you insert a new uniqueidentifier anywhere in your database, just transfer it to the rollback transaction and call the stored procedure to insert uniqueidentifer . This will call our stored validation procedure and, if it fails, it will cause an error. Your rollback ensures that nothing else is saved.

Example:

 CREATE PROCEDURE [dbo].[Bases.Insert] @Guid uniqueidentifier, @AccountId bigint=0, @ModifierId bigint=0, @ScopeIdentity bigint OUTPUT AS **BEGIN TRY BEGIN TRAN** -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON **EXEC [dbo].[UniqueIds.Insert] @Guid = @Guid;** -- Insert the base row INSERT INTO Bases ( [Guid], [State], Utc, AccountId, ModifierId ) VALUES ( @Guid, 0, GETUTCDATE(), @AccountId, @ModifierId ); **COMMIT;** SELECT @ScopeIdentity = CAST(SCOPE_IDENTITY() As bigint); **END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH** 

**

Result: checking the uniqueness of a unique identifier from 100 Million rows successively receive <0.05ms

**