SQL Server 2005&2008±¸·Ý»Ö¸´×ܽá
Ò»¡¢Êý¾Ý¿â´æ´¢¸ÅÊö
1¡¢Êý¾ÝÎļþÀàÐÍ
· Primary data files:ÿ¸öÊý¾Ý¿â¶¼ÓÐÒ»¸öµ¥¶ÀµÄÖ÷ÒªÊý¾ÝÎļþ£¬Ä¬ÈÏÒÔ.mdfÀ©Õ¹Ãû¡£Ö÷ÒªÊý¾ÝÎļþ²»½ö°üº¬Êý¾ÝÐÅÏ¢£¬»¹°üº¬Óë¸ÃÊý¾Ý¿â½á¹¹Ïà¹ØµÄÐÅÏ¢¡£´´½¨Êý¾Ý¿âʱ£¬Êý¾Ý¿â½á¹¹Ïà¹ØÐÅÏ¢²»½ö´æÔÚÓÚmasterÊý¾Ý¿âÖУ¬Í¬Ê±»¹°üº¬ÔÚprimary data fileÉÏ.
· Secondary data filesÒ»¸öÊý¾Ý¿â¿ÉÒÔÓÐÒ»¸ö»òÕ߶à¸ö¶þ¼¶Êý¾ÝÎļþ£¬Ä¬ÈÏÒÔ.ndfΪÀ©Õ¹Ãû¡£Ò»°ãÀ´Ëµ¶þ¼¶Êý¾ÝÎļþ²¢²»ÊDZØÐëµÄ£¬ÒòΪ¶þ¼¶Êý¾ÝÎļþ²»°üº¬ÎļþλÖõÈÐÅÏ¢¡£
· Transaction logs: Êý¾Ý¿â±ØÐëÖÁÉÙÓÐÒ»¸öÊÂÎñÈÕÖ¾Îļþ£¬Ä¬ÈÏÒÔ.ldfΪÀ©Õ¹Ãû¡£ÈÕÖ¾ÊÇÕû¸öÊý¾Ý¿âµÄÃüÂö£¬ÊÂÎñÈÕÖ¾²»¿É¶ÁµÄ»°£¬½«ÎÞ·¨¶ÔÊý¾Ý¿â½øÐÐÈκβÙ×÷¡£
µ±ÄãÔÚÊý¾Ý¿âÉϽøÐÐÊý¾Ý²Ù×÷ʱ£¬Êý¾Ý²¢²»ÊÇÖ±½ÓдÈëÊý¾ÝÎļþ£¬¶øÊÇÏȽ«Ïà¹Ø²Ù×÷ÐÅϢдÈëÊÂÎñÈÕÖ¾Îļþ¡£µ±Ò»¸öÊÂÎñ½áÊøʱ£¬¸ÃÊÂÎñ±»±ê¼ÇΪÒÑÌá½»£¬µ«ÕâÒ²²¢²»Òâζ×ÅÊý¾Ý´ÓÈÕÖ¾ÎļþдÈëÁËÊý¾ÝÎļþÖС£Ò»¸ö±ê¼ÇΪÒÑÌá½»µÄÊÂÎñ½ö½öÒâζ×ÅËùÓÐÓë¸ÃÊÂÎñÏà¹ØµÄÔªËØÒѾ³É¹¦Íê³É¡£The buffer cache may be updated, but not necessarily the data file.
¼ì²éµã(checkpoint)»áÖÜÆÚÐԵķ¢Éú¡£¼ì²éµã·¢Éúʱ£¬ÊÇÈ·ÈÏËùÓÐÒÑÌá½»µÄÊÂÎñ£¬²»¹ÜÊÇÔÚbuffer cache»òÕßÊÂÎñÈÕÖ¾ÖУ¬¶¼±»Ð´ÈëÏà¹ØµÄÊý¾ÝÎļþÖС£¼ì²éµã(checkpoint)¿ÉÒÔͨ¹ýÒÔÏ·½Ê½À´´¥·¢£º
1¡¢ ÏÔʾµ÷ÓÃcheckpointÃüÁ
2¡¢ Recover Interval ʵÀýÉèÖõÄÖÜÆÚ´ïµ½£¨ÓÃÀ´±êʶ¶à¾Ã·¢ÉúÒ»´Îcheckpoint£©
3¡¢ ×öÁËÊý¾Ý¿â±¸·Ý£¨ÔÚ¼òµ¥Ä£Ê½Ï£©£»
4¡¢ Êý¾Ý¿âÎļþ½á¹¹±»¸Ä±ä£¨ÔÚ¼òµ¥Ä£Ê½Ï£©£»
5¡¢ Êý¾Ý¿âÒýÇæ±»½áÊø¡£
Ò»°ãÀ´Ëµ£¬Ð´Êý¾ÝµÄ¹ý³ÌÊÇÓÉϵͳ×Ô¶¯Íê³ÉµÄ£¬ÈçÏÂͼËùʾ£¬µ«Êý¾Ý²¢²»ÊÇÖ±½ÓдÈë.mdf»òÕß.ndfÎļþÖУ¬¶øÊÇÏÈдÈëÊÂÎñÈÕÖ¾ÖС£
1. Óû§Ö´ÐÐinsert, update, deleteµÈÓï¾ä£»
2. Êý¾ÝÁ¢¼´±»Ð´ÈëÄÚ²¿ÈÕÖ¾»º´æÖÐ(internal log cache)
3. ÈÕÖ¾»º´æ»á¸üÐÂÎïÀíÊÂÎñÈÕÖ¾Îļþ£¬Í¬Ê±½«ÔÚbuffer cacheÉÏÖ´ÐÐÏà¹Ø±ä»¯
4. Êý¾Ý»º´æ£¨data buffer£©Çå³ýËùÓÐÔÚ»º´æÉϵÄÔàÊý¾Ý£¬Êý¾ÝÎļþ±»¸üС£
1¡¢»Ö¸´Ä£Ê½ÀàÐÍ
ËùÓеÄÊý¾Ý¿â¶¼¿ÉÒÔÉ
Ïà¹ØÎĵµ£º
50¸ö³£ÓÃsqlÓï¾ä
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ;
select a.S# from (select s#,score from SC where C#='001') a,(select s#,sc ......
ÊÓͼ²éѯÖÐÔõÑù½«Ô¶¨ÓÚÈçÐÔ±ðsex ÕâÑùµÄ×ֶΣ¬×Ö¶ÎֵΪ0£¬1ÕâÑùµÄintÀàÐÍÖµ£¬²éѯʱֱ½Ó·µ»Øvarchar
Ð͵Ä×Ö·û‘ÄÐ’£¬‘Å®’ÒÔ±ãÓÚÎÒÃǶÁÈ¡ÄØ£¿
ÓÐÈË»áÏëµ½if …else…ÕâÑùµÄÓï¾ä£¬¿ÉÊÇÔõô¼Ó£¬¶¼²»ÖªµÀ¼ÓÄÄÀÒòΪ×ÜÊÇ»á³ö´í¡£Æä ......
¸´ÖƱíµÄ½á¹¹
select * into newtable from oldtable where 1<>1
¸´ÖƱí´ï¶îÄÚÈÝ
insert into new table select *(ÁÐÃû) from oldtable
datediffº¯ÊýÓ÷¨
×÷ÓÃ
·µ»ØÁ½¸öÈÕÆÚÖ®¼äµÄ¼ä¸ô¡£
Óï·¨ ......
µÚ1ÖÖ·½·¨£º
ÆóÒµ¹ÜÀíÆ÷
--¹ÜÀí
--ÓÒ¼üÊý¾Ý¿âά»¤¼Æ»®
--н¨Î¬»¤¼Æ»®
--<ÏÂÒ»²½>
--Ñ¡ÔñÄãÒª±¸·ÝµÄÊý¾Ý¿â
--<ÏÂÒ»²½>Ö±µ½"Ö¸¶¨Êý¾Ý¿â±¸·Ý¼Æ»®"ÕâÏî
--Ö¸¶¨´æ´¢±¸·ÝÎļþµÄλÖÃ,ÕâÀï¸ù¾ÝÐèҪѡÔñ±¸·Öµ½´Å´ø»¹ÊÇ´ÅÅÌ
--µ¥»÷µ÷¶ÈºóÃæµÄ"¸ü¸Ä"°´ ......
sql serverµÄËæ»úº¯ÊýnewID()ºÍRAND()¡¡¡¡
¡¡¡¡SELECT * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--Ëæ»úÅÅÐò
¡¡¡¡SELECT TOP 10 * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--´ÓOrders±íÖÐËæ»úÈ¡³ö10Ìõ¼Ç¼¡¡¡¡
¡¡¡¡Ê¾Àý¡¡¡¡
¡¡¡¡A.¶Ô±äÁ¿Ê¹Óà NEWID º¯Êý
¡¡¡¡ÒÔÏÂʾÀýʹÓà NEWID() ¶ÔÉùÃ÷Ϊ uniq ......