SQLÖÐtruncate tableºÍdeleteµÄÇø±ð
TRUNCATE TABLE ÔÚ¹¦ÄÜÉÏÓë²»´ø Where ×Ó¾äµÄ Delete
Óï¾äÏàͬ£º¶þÕß¾ùɾ³ý±íÖеÄÈ«²¿ÐС£µ« TRUNCATE TABLE ±È Delete Ëٶȿ죬ÇÒʹÓõÄϵͳºÍÊÂÎñÈÕÖ¾×ÊÔ´ÉÙ¡£
Delete
Óï¾äÿ´Îɾ³ýÒ»ÐУ¬²¢ÔÚÊÂÎñÈÕÖ¾ÖÐΪËùɾ³ýµÄÿÐмǼһÏî¡£TRUNCATE TABLE ͨ¹ýÊÍ·Å´æ´¢±íÊý¾ÝËùÓõÄÊý¾ÝÒ³À´É¾³ýÊý¾Ý£¬²¢ÇÒÖ»ÔÚÊÂÎñÈÕÖ¾ÖмǼҳµÄÊÍ·Å¡£
TRUNCATE TABLE
ɾ³ý±íÖеÄËùÓÐÐУ¬µ«±í½á¹¹¼°ÆäÁС¢Ô¼Êø¡¢Ë÷ÒýµÈ±£³Ö²»±ä¡£ÐÂÐбêʶËùÓõļÆÊýÖµÖØÖÃΪ¸ÃÁеÄÖÖ×Ó¡£Èç¹ûÏë±£Áô±êʶ¼ÆÊýÖµ£¬Çë¸ÄÓà Delete¡£
¶ÔÓÚÓÉ FOREIGN KEY Ô¼ÊøÒýÓÃµÄ±í£¬²»ÄÜʹÓà TRUNCATE TABLE£¬¶øӦʹÓò»´ø Where ×Ó¾äµÄ Delete Óï¾ä¡£ÓÉÓÚ
TRUNCATE TABLE ²»¼Ç¼ÔÚÈÕÖ¾ÖУ¬ËùÒÔËü²»Äܼ¤»î´¥·¢Æ÷¡£
TRUNCATE TABLE ²»ÄÜÓÃÓÚ²ÎÓëÁËË÷ÒýÊÓͼµÄ±í¡£
truncate,delete,dropµÄÒìͬµã£º
×¢Òâ:ÕâÀï˵µÄdeleteÊÇÖ¸²»´øwhere×Ó¾äµÄdeleteÓï¾ä
Ïàͬµã:truncateºÍ²»´øwhere×Ó¾äµÄdelete, ÒÔ¼°drop¶¼»áɾ³ý±íÄÚµÄÊý¾Ý
²»Í¬µã:
1.truncateºÍ deleteֻɾ³ýÊý¾Ý²»É¾³ý±íµÄ½á¹¹(¶¨Òå)
dropÓï¾ä½«É¾³ý±íµÄ½á¹¹±»ÒÀÀµµÄÔ¼Êø(constrain),´¥·¢Æ÷(trigger),Ë÷Òý(index);
ÒÀÀµÓڸñíµÄ´æ´¢¹ý³Ì/º¯Êý½«±£Áô,µ«ÊDZäΪinvalid״̬.
2.deleteÓï¾äÊÇdml,Õâ¸ö²Ù×÷»á·Åµ½rollback
segementÖÐ,ÊÂÎñÌá½»Ö®ºó²ÅÉúЧ;Èç¹ûÓÐÏàÓ¦µÄtrigger,Ö´ÐеÄʱºò½«±»´¥·¢.
truncate,dropÊÇddl,
²Ù×÷Á¢¼´ÉúЧ,ÔÊý¾Ý²»·Åµ½rollback segmentÖÐ,²»Äܻعö. ²Ù×÷²»´¥·¢trigger.
3.deleteÓï¾ä²»Ó°Ïì±íËùÕ¼ÓõÄextent, ¸ßË®Ïß(high w2atermark)±£³ÖÔλÖò»¶¯
ÏÔÈ»dropÓï¾ä½«±íËùÕ¼ÓõĿռäÈ«²¿ÊÍ·Å
truncate Óï¾äȱʡÇé¿öϽ«¿Õ¼äÊͷŵ½ minextents¸ö extent,³ý·ÇʹÓÃreuse
storage; truncate»á½«¸ßË®Ï߸´Î»(»Øµ½×ʼ).
4.ËÙ¶È,Ò»°ãÀ´Ëµ: drop> truncate >
delete
5.°²È«ÐÔ:СÐÄʹÓÃdrop ºÍtruncate,ÓÈÆäûÓб¸·ÝµÄʱºò.·ñÔò¿Þ¶¼À´²»¼°
ʹÓÃÉÏ£º
Ïëɾ³ý²¿·ÖÊý¾ÝÐÐÓÃdelete,×¢Òâ´øÉÏwhere×Ó¾ä. »Ø¹ö¶ÎÒª×ã¹»´ó.
Ïëɾ³ý±í,µ±È»ÓÃdrop
Ïë±£Áô±í¶ø½«ËùÓÐÊý¾Ýɾ³ý. Èç¹ûºÍÊÂÎñÎÞ¹Ø,ÓÃtruncate¼´¿É. Èç¹ûºÍÊÂÎñÓйØ,»òÕßÏë´¥·¢trigger,»¹ÊÇÓÃdelete.
Èç¹ûÊÇÕûÀí±íÄÚ²¿µÄËéƬ,¿ÉÒÔÓÃtruncate¸úÉÏreuse stroage,ÔÙÖØе¼Èë/²åÈëÊý¾Ý
Ïà¹ØÎĵµ£º
select f.AGENTID as 'ID',
f.AGENTNAME as 'ÐÕÃû',
f.COMNAME as '¹«Ë¾¼ò³Æ',
c.REGISTDATE as '×¢²áʱ¼ä',
......
ÉêÃ÷¡£ÎÄÕ½ö´ú±í¸öÈ˹۵㣬ÓëËùÔÚ¹«Ë¾ÎÞÈκÎÁªÏµ¡£
1. ¸ÅÊö
ÍøÒ³¹ÒÂíÕâ¸ö»°ÌâÏëÀ´´ó¼Ò²¢²»Ä°Éú¡£ÎªÊ²Ã´ÓÐÕâô¶àµÄÍøÒ³ÉÏ´æÔÚ×ÅľÂíÈ¥¹¥»÷ÆÕͨÓû§£¿²»¿É·ñÈÏ£¬Ï൱һ²¿·ÖÍøÒ³Ô±¾¾ÍÊǶñÒâµÄ£ºÍøÒ³µÄ×÷Õß¹ÊÒâÔÚÉÏÃæ·ÅÉÏľÂí£¬È»ºóͨ¹ý¸÷ÖÖÊÖ¶ÎÒýÓÕÓû§È¥ä¯ÀÀ¡£µ«ÊǾø´ó¶àÊý±»¹ÒÂíµÄÍøÒ³Ô±¾ÊÇÕý³ ......
×î½ü×öÏîÄ¿£¬ÐèÒªÔ¶³Ì·ÃÎÊÊý¾Ý¿â£¬×Ô¼ºÔÚ²éÁËЩ×ÊÁÏ£¬×ܽáÁËһϣ¬Ï£Íû¶Ô´ó¼ÒÓаïÖú£º
×÷Õߣºshinehoo
Ò»¡¢ÅäÖÃSQL Server 2005
1£©°²×°SQL Server 2005¿ª·¢°æ£»
2£©¿ªÊ¼->³ÌÐò->Microsoft SQL Server 2005->SQL
Server 2005ÍâΧӦÓÃÅäÖÃÆ÷£¬ÔÚ´ò¿ªµÄ½çÃæµ¥»÷“·þÎñµÄÁ¬½ÓµÄÍâΧӦÓÃÅäÖÃÆ÷”£¬ÔÚ´ò ......
Àý×Ó£ºÕÒ³öÿ¸ö¸èÊÖÏúÁ¿×î¸ßµÄ3Ê׸è
create table singer_info
(
id int primary key identity(1,1),
name varchar(20),
song varchar(20),
amount int
)
insert into singer_info values('jay','aaa',1000)
insert into singer_info values('jay','bbb',2000)
insert into singer_info values('jay','cc ......
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØʵÏÖSQL SERVER¡¢ACCESS¡¢EXC ......