SQL SP未一致地生成唯一的newid()

Question

我用我创建的存储过程遇到了问题。我大约6个月前才开始使用sql编程，所以我的SP可能看起来很马虎，所以提前向您道歉。这就是我遇到的问题:我在一个表中选择顶部的项目，创建一个等于newid()的变量，并将该项目插入到3个不同的表中，然后删除顶部的项目，并循环，直到所有项目都被插入/删除。有时，似乎在生成newid()时，它使用的是上次运行存储过程时生成的It。所以我得到这个错误："23000 (2601):MicrosoftSQL ServerCannot在对象'dbo.lab_nor‘中使用唯一索引’ak_lab_nor1‘插入重复的键行“。我输出生成的新is，并检查它是否已经存在于lab_nor表中，它确实存在，并且它是我之前插入的项。这就像newid()在循环期间没有更新一样。下面是存储过程。

@EnterpriseID char (5),
@PracticeID char (4),
@EncounterID uniqueidentifier,
@PersonID uniqueidentifier,
@CurrentProvider varchar (50),
@CurrentProviderID uniqueidentifier,
@LocationID uniqueidentifier,
@CurrentUser varchar (50),
@CurrentUserID int,
@CurrentTime varchar (8)

as

/**********************************
1. Declare Variables
**********************************/

DECLARE @EncounterDate DATETIME,
@MaxRows VARCHAR (100)
SET @EncounterDate = (SELECT enc_timestamp FROM patient_encounter WHERE enc_id = @EncounterID)
SET @MaxRows = (SELECT count(*) FROM rpa_lab_fav_ext2_ WHERE enc_id = @EncounterID)


/**********************************
2. Begin Loop
**********************************/

WHILE @MaxRows > 0

BEGIN

/**********************************
3. Insert top row from rpa_lab_fav_ext2_ into order_
**********************************/

UPDATE TOP(1) rpa_lab_fav_ext2_
    SET txt_order_module_ufo_num = last_generated + 1
        FROM system_counters WITH (UPDLOCK)
            WHERE counter_type = 'lab order'
            AND rpa_lab_fav_ext2_.txt_order_module_ufo_num is null
            AND rpa_lab_fav_ext2_.txt_lab = 'InHouseLab';

        UPDATE system_counters 
            SET last_generated = last_generated + 1
                WHERE counter_type = 'lab order'

        SELECT @MaxRows = count(*) 
            FROM rpa_lab_fav_ext2_     

/**********************************
4. Create unique order id
**********************************/

DECLARE @order_id UNIQUEIDENTIFIER
SET @order_id = NEWID()

--Insert Into RPA_Test_01_ (enterprise_id, practice_id, person_id, created_by, create_timestamp, 
--          modified_by, modify_timestamp, enc_id, txt_test001)

--Select @EnterpriseID, @PracticeID, @PersonID, @CurrentUserID, getdate(), 
--      @CurrentUserID, getdate(), @EncounterID, @order_id

-- select txt_test001 from rpa_test_01_ where enc_id = '9E14B10A-3D76-4EA7-B2E5-C5CB552F3455'
-- select * from lab_nor where order_num = 'CE70A9C4-21F7-42C5-8D44-97BF0EA5AA46'

/**********************************
5. Insert top row from rpa_lab_fav_ext2_ into order_
**********************************/

INSERT INTO order_ (orderedReason, actClass, actCode, actMood, actStatus, actText, actTextDisplay, 
        actDiagnosis, actDiagnosisCode, apptTimeFrame, apptTimeFrameDisp, apptDate, 
        actComments, 
        actRecurTimeInterv, 
        documented_by, encounterDate, ordered, orderedBy, orderedByKey, orderedDate, orderedTime, 
        enterprise_id, practice_id, person_id, encounterID, created_by, create_timestamp, modified_by, 
        modify_timestamp, seq_no, order_module_ufo_num, 
        order_module_order_num, order_module_lab_id)

SELECT TOP(1) txt_lab, 'LAB', txt_lab_code, 'ORD', 'ordered', txt_lab_description, txt_lab_description, 
        txt_assessment, txt_assessment_code, txt_perform_timeframe, txt_perform_timeframe, txt_appt_date, 
        (CASE WHEN txt_cc_provider = null THEN '' ELSE 'CC Results to: ' + txt_cc_provider + ', ' + txt_cc_address + ', ' + txt_cc_city + ', ' + txt_cc_state + ', ' + txt_cc_phone + '.' end), 
        (CASE WHEN txt_interval_ = null THEN '' ELSE 'Interval: ' + txt_interval_ + ', Start: ' + txt_interval_start_date + ', Stop: ' + txt_interval_stop_date end),
        @CurrentUser, @EncounterDate, '1', @CurrentProvider, @CurrentProviderID, getdate(), @CurrentTime,  
        @EnterpriseID, @PracticeID, @PersonID, @EncounterID, @CurrentUserID, getdate(), @CurrentUserID, 
        getdate(), newid(), (CASE WHEN txt_order_module_ufo_num is null THEN null else 'PRO' + txt_order_module_ufo_num end), 
        (CASE WHEN txt_lab = 'InHouseLab' THEN @order_id else null end), (CASE WHEN txt_lab = 'InHouseLab' THEN '2340' else '60' end)

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY rpa_lab_fav_ext2_.txt_lab_description asc

/**********************************
6. Insert top row from rpa_lab_fav_ext2_ into lab_nor
**********************************/

IF (select top(1) txt_lab from rpa_lab_fav_ext2_ where enc_id = @EncounterID ORDER BY txt_lab_description) = 'InHouseLab'

BEGIN

INSERT INTO lab_nor (Enterprise_ID, Practice_ID, Enc_ID, Order_num, person_id, Ordering_Provider, 
        test_location, test_status, ngn_status, test_desc, delete_ind, order_control, order_priority, 
        time_entered, spec_action_code, billing_type, ufo_num, lab_id, enc_timestamp, created_by, 
        create_timestamp, modified_by, modify_timestamp, generated_by, order_type, recur_mode, ng_order_ind, 
        documents_ind, signoff_comments_ind, ordered_elsewhere_ind)

SELECT TOP(1) @EnterpriseID, @PracticeID, @EncounterID, @order_id, @PersonID, @CurrentProviderID,
        @LocationID, 'Pending', 'Ordered', txt_lab_description, 'N', 'NW', 'R',
        getdate(), 'N', 'T', 'PRO' + txt_order_module_ufo_num, '2340', @EncounterDate, @CurrentUserID,
        getdate(), @CurrentUserID, getdate(), 'RPA Lab Master', 'L', 'N', 'N',
        'N', 'N', 'N'

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY txt_lab_description asc

END


/**********************************
7. Insert top row from rpa_lab_fav_ext2_ into lab_order_tests
**********************************/

IF (select top(1) txt_lab from rpa_lab_fav_ext2_ where enc_id = @EncounterID ORDER BY txt_lab_description) = 'InHouseLab'

BEGIN

INSERT INTO lab_order_tests (order_test_id, order_num, test_code_id, test_code_text, test_code_system, 
        created_by, create_timestamp, modified_by, modify_timestamp)

SELECT top(1) newid(), @order_id, txt_lab_code, txt_lab_description, 'I',
        @CurrentUserID, GETDATE(), @CurrentUserID, GETDATE()

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY txt_lab_description asc

END


/**********************************
8. Delete top row from rpa_lab_fav_ext2_
**********************************/

--DELETE TOP (1) 
--FROM rpa_lab_fav_ext2_ 
--WHERE txt_lab_description IN
--  (SELECT TOP 1 txt_lab_description
--      FROM rpa_lab_fav_ext2_ 
--      WHERE enc_id = @EncounterID
--      ORDER BY txt_lab_description asc)

Declare @Delete varchar (20)
Set @Delete = (select top(1) txt_lab_code from rpa_lab_fav_ext2_ where enc_id = @EncounterID order by txt_lab_description asc)

DELETE rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
and txt_lab_code = @Delete

/**********************************
9. Find row count
**********************************/

SET @MaxRows = (SELECT count(*) FROM rpa_lab_fav_ext2_ WHERE enc_id = @EncounterID)

/**********************************
10. If max rows is 0, end loop, else continue
**********************************/

IF @MaxRows = 0
    BREAK
ELSE
    CONTINUE

END

Answer 1

这很奇怪，因为微软的this文章说NEWID()应该总是生成一个唯一的id，因为它使用MAC地址和CPU时间。如果它可以被复制，它可能是一个bug。