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

???? ?????? ???? ??????
???? ID: 139444 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

??????

? ????????? ?????????, ?? ???????? ???-????? ???????? 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
    					

???

???? ID: 139444 - ????? ???????: 17 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • 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

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com