sql-server-2008 – 快速提升Sql Server 2008 R2

CREATE TABLE [dbo].[SHARE_AD_GROUP](
    [SHARE_AD_GROUP_ID] [int] IDENTITY(1,1) NOT NULL,[SHARE_ID] [int] NOT NULL,[AD_GROUP] [varchar](200) NOT NULL,[SHARE_PERMISSIONS] [varchar](65) NULL,

以下是最好的方法：

1-

ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]


@shareID int,@ownerId varchar(200),@sharePermissions varchar(65)

AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
    if @@ROWCOUNT =0 
    begin
    INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS])
                 VALUES
                       (@shareID,@ownerId,@sharePermissions)
    end

end

2-

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    declare @id int
    SET NOCOUNT ON;
    set @id = (select top 1 SHARE_AD_GROUP_ID from SHARE_AD_GROUP where SHARE_ID = @shareID and [AD_GROUP] = @ownerId)
        if @id is null
            begin
                INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,@sharePermissions)
                 set @id = SCOPE_IDENTITY()
            end
        else
            begin
            UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
            end
End

3-合并==>不知道如何写这个.

你认为最快的方式是什么？

解决方法

ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]
    @shareID int,@sharePermissions varchar(65)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    MERGE [dbo].SHARE_AD_GROUP AS T
    USING
    (
        SELECT @shareID,@sharePermissions
    ) AS S
    (
        SELECT Share_ID,AD_Group,Share_Permissions
    )
    ON (T.SHARE_ID = S.Share_ID and T.AD_GROUP = S.AD_Group)

    WHEN MATCHED THEN
        UPDATE SET SHARE_PERMISSIONS = S.Share_Permissions

    WHEN NOT MATCHED THEN
        INSERT
            (SHARE_ID,AD_GROUP,SHARE_PERMISSIONS)
        VALUES
            (S.Share_ID,S.AD_Group,S.Share_Permissions)
    ;
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!