???? ID: 139444 - ????? ???????: 17 ??????? 2011 - ??????: 3.0

SQL ????? ??? ??? ?????? ?? ????????? ????????? ?? ??????? ?? ??? ???? ????

???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
??? ?? ??????? ???? | ??? ?? ??????? ????

??????

? ????????? ?????????, ?? ???????? ???-????? ???????? Microsoft SQL Server ???????? ??? ??? ???? ???? ?????? Brevity, ?? ??? ?? ????? ???-??? ?????? ???????? ????? ?? ??? "?????" ?? "PK" ?? ??? ??? ?? ???? ???, ????? ?? ????? "???????? ?????." denote ????? ????????? PKs ???? ?? ??????? ?? ??? ??????? ???, ?? ?? ??????? ?????? ??? ?????? ???? ?? ???? ?????? SQL ????? ?? ???? ???????, ???????????, ????? ??????, ???????? KEY ?????? ?? triggers ???? enforcing ?? ??? ?? ??????? ????

??, despite ???????? ???????????? ?? ??????? ???????? ????? ????????? ?? ???? ??, ?? ??? ??? ?? ?? ?? ?????? ?? eliminated. ?? ???? ?? ?? ?? ?? ????? ?? ??? ????????? PKs SQL Server ???? ???-??????? ???? ????? ??, ?? ???? ???? ?? ?? PK uniqueness ???? ???? ???? ?? ??? ??? ???? ???? ?? ????? ?? ???? ?? ?? ???? ???? ?? ???????? ?????? ???? ??????? enforcing ??????? ??????? ??????, ?? ?????? ?? ???

????? ????????? PKs ?? ????? ???????????, ??? ????????? ???????? ?? ???? ?????? ???? ?? ????? ?? ?????? ???? ??? ?? noticed ??? ??? ?? ????? ??:
????????? ????? ?? ????? 1505, ???? 16, ?????? 1 ????? ????? ??????????? ???? ???
??? ?? SQL Server 2000 ?? SQL Server 2005 ????? ?? ??? ???, ?? ???? ????? ?????? ????? ??????? ?? ???? ??:
????? 1505, ???? 16, ?????? 1 ????? ????? INDEX ?? ?? ?? ??????? ???????? ??? ?? ??? ??? ????????? ????? ???? ' %. * ls '?? ??? ???????????' %. * ls'? ????????? ????? ??? ?? % ls ???
?? ???? ?? ?????? ?????, ?? ???? ?????? ?? ????????? ???????? ????? ?? ??????? ?? ??? ???? ?? ????? ???? ??? ?? ??? ??, ???? ????? closely ??????? ????????? ?? ?? ??????????? ?? ????? ?? ??? ?? ???? ?? ?? ??? ????????? ?? ???????

???? ???????

?? ?????? ?? ??? ?? ???? ?? ????????? PK ??? ?? ??? ????? ?????? ?? ?????? ??? ???????? ????? ?? (col1, col2) ?? ?? ?????? ?? ???? ??? ????? ??????????? ?? ???????? KEY ???????? ????????? PKs ?? ??? ?? ????????? ?? ???? ?? ????????? ?? ????? ???? ?? ????????? ??????? ?? ??? ???? illustrates ???
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
???-?? ????????? ??? ????????? ???????? ????? ????? ?? ????? ???? ?? ??? ???? ??? ??:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
?? ?????? ??? ????????? PK ????? ?? ???????? ??? ???? ?? ??? ?? ?????? ???? ?????? ?? ?????? ??? ????? ????? ??? ????? PK ??? ?? ??? ????????? ?? ?????? ???

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
col1col2
112


??? ???? ??? ??? ?? ????????? PK ??? ??? ???, ??????? ????????? ?? ?? ?? ???-??? ??? ?? ???? ?? ???????? ??? ?? ????? ?? ???? ?????? ?? ???::
set rowcount 1
delete from t1
where col1=1 and col2=1
Rowcount ??? ?? ??? ?? ????? ??? ?? ??? ????????? ?? ?????? 1-n ???? ?????? ?? ?????? ???, ??? 2 ????????? ???? rowcount 1 ?? ??? ??? ??????? ???? BY ?????? ?????? ?? col1/col2 ????? ?? ?? ???? ??? ???? BY ?????? ?? ?? ???? ????????? ???? ??, "??? rowcount" ?????? ???? ???????? ?? ????????? ?? ??? ?? ??? ????? ?? ???? ?? ??? ??? ????? ???? ??, rowcount ????? PK ??? ?? ????????? ?? ?????? n-1 ?? ??? ??? ???

????????? ?? ????? ?? ???? ???? ????? ???? ???? ?????? ????????? ??? ?? unlikely, ???? ?? ?? ????????? PK ??? ???, ??? ?????? ?? ??? ??? ??? ????? ???? ??? ?? ?? ?? ?????? ?? ?????? ???????? ????? ?? ??? ??? ??????? ??????? ??????? ?? ??? ???? ?? ???? ?????? ??? ?? ?? ???? ??? (?? ?????????) ????, ???????? ???? ????? ?????????? ?? ??? ?????? ??? whatever ????? ????????? ????? ?? ???? ?? ???? ?? ???? ??? ???? ?? ?????? ??? ????? ???? ?? ??? ????? ???? ????? ??? ?? ???? ????????? ???? ????? ?? ???? ??? ???? ?? ???? study ?? ???? ?????? ?? ??? ???????? ???

??? ??? ?????? ??? ????????? PK ????? ?? ?? distinct ??? ??, ?? ?????? ????????? ??? ?? ??????? ?? ??? ???? ???? ?? ???? ??? ?? ?????? ??? ????? ????????? ????? ???? ?? ????:
  1. ????, ??????? ???? BY ?????? ?? ????????? PK ??? ??? ????? ??? ????? ??? ?? ????????? ???? ?? ???, ?? ???????? ??? ???? ?? ??? ????????? ?? ?????? ??????
  2. ???? ???????? ?????? ??? ????????? ????? ??? ?? ??? ????? ?????? ?? ???::
    SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
  3. ????????? ??? ????????? eliminating ???????? ?????? ??? ????????? ????????? ?? ??? ????? ?????? ?? ???::
    SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
  4. ?? ????? ??, holddups ?????? ????? PKs ???? ?????, ??? ??, ?? ???? ?? ?????? ??? t1 ????????? PKs ??? (?? ??? ??? SSN ?????? ??? ???) ????? ????????? ?? ??? ??? ???????? ????? holddups ??? ???????? ??, ?? ?? ?? ???? ??? ???? ????????? ???????? ??? ????? ????????? ?? ???? ????? ???, ?? ?????? ???? ????? ?? ?? ????????? reconcile ??? ?? ????????? ????? ??? ?? ??? ???? ????? ??? ?????? ?? ??? ??????:
    SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2
    ???????? ?????? ?? ??? 1 ?? ?????? ???? ???? ?????? ??? ???, ??? 5 ??? ???? ???? ?? ??? ??? ?????? ??? ????, ?? ???????? ?? ????????? ??? ????? ????????? ???, ?? ?????? ?? ??? ???-?? ????????? ?? ???? ?? ???????? ??? ?? ?? ?? ??? ?????? discarding, ?? ??? ??? ????? ????? ??? ?? ?????? ?? ??? ????? ????? ?? entail ?????? ???????? ??? ????????? PK ??? holddups ?????? ?? ??? ?? ????? ??? ?? ?? ???
  5. ??? ?????? ?? ????????? ????????? ?? ?????? ?????? ?? ???::
    DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
  6. ??? ?????? ??? ???????? ????????? ?? ????? ?????? ?? ???::
    INSERT t1 SELECT * FROM holddups
???? ???? ???? ??:
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
????? ?? ???? ????
??????: 
kbinfo kbusage kbmt KB139444 KbMthi
???? ?????? ???????????? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:139444  (http://support.microsoft.com/kb/139444/en-us/ )
????? ?? ???? ????