在Sql Server中查找重复的uniqueidentifier的最快方法是什么？ [英] What is the fastest way to look for duplicate uniqueidentifier in Sql Server?

问题描述

对于大型数据库中的每条记录，我们都使用 uniqueidentifier 。出于业务原因，我们需要确保 uniqueidentifier 不会被使用超过一次，但是出于性能原因，我们有一个 bigint 作为主键。

We use uniqueidentifier for every record within a very large database. For business reasons we need to ensure that the uniqueidentifier is never used more than once, but for performance reasons we have a bigint as the primary key.

测试在SQL服务器表中的 uniqueidentifer ？

推荐答案

**

**

免责声明：以下方法可能需要调整您的业务需求，并且目前仅在 Sql Azure临时环境上运行一个S0实例（10个DTU）。目标是概念验证。

DISCLAIMER: The following aproach may require tweaking to your business requirements, and currently has only been run in a Sql Azure staging environment on an S0 instance (10 DTU's). The goal being for proof of concept.

（请参见下文中的样板创建至脚本）

方法：
创建一个表专门用于存储任何<$数据库中存在的c $ c> uniqueidentifiers 。该表将经过大量优化，仅出于增加我们的 uniqueidentifer 验证器的目的。

Method: Create a table specifically for storing any uniqueidentifiers that exist within your database. This table will be heavily optimised for the single purpose of augmenting our uniqueidentifer validator.

该表将具有四列。一个用于 uniqueidentifier ，另三个用于单个数字计算列，用于存储 uniqueidentifier 。

The table will have four columns. One for the uniqueidentifier, and the other three as single digit binary computed columns for storing each of the three digits of the uniqueidentifier.

然后，我们将在前三位数字列和 uniqueidentifer 作为聚簇索引的最后一列。

We'll then create a clustered index on the first three digit columns, and the uniqueidentifer as the last column of the clustered index.

我们最后将创建一个存储过程，该存储过程将使用 uniqueidentifer 并将前三位数字分解为 binary 数据类型并执行查找，并利用B $的B树结构

We'll lastly create a stored procedure that'll take a uniqueidentifer and break out the first three digits as binary datatype and perform the lookup, taking advantage of the B-tree structure of the data on the disk as per our 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 validation:

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);

INSERTS的存储过程：

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

插入新的 uniqueidentifier （示例）：

Inserting a new uniqueidentifier (example):

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

关于实施的最终字词：

每次在数据库中的任何位置插入新的 uniqueidentifier 时，只需将其包装在回滚事务中并为插入 uniqueidentifer 。这将调用我们的验证存储过程，如果验证失败，则会引发错误。您的回滚将确保没有其他任何持久性。

Final word on implementation:

Every time you insert a new uniqueidentifier anywhere in your database simply wrap it in a rollback transaction and call the stored procedure for inserting a uniqueidentifer. That will call our validation stored procedure and if it fails it'll throw an error. Your rollback will make sure nothing else is persisted.

示例：

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**

**

结果：验证100个
百万行中的uniqueidentifier的唯一性始终< 0.05ms

Result: validating the uniqueness of a uniqueidentifier from 100 MILLION rows consistently takes < 0.05ms

**

这篇关于在Sql Server中查找重复的uniqueidentifier的最快方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！