sqlË÷ÒýÀàÐÍ
Ë÷ÒýÀàÐÍ
ΨһË÷Òý£ºÎ¨Ò»Ë÷Òý²»ÔÊÐíÁ½ÐоßÓÐÏàͬµÄË÷ÒýÖµ
Ö÷¼üË÷Òý£ºÎª±í¶¨ÒåÒ»¸öÖ÷¼ü½«×Ô¶¯´´½¨Ö÷¼üË÷Òý£¬Ö÷¼üË÷ÒýÊÇΨһË÷ÒýµÄÌØÊâÀàÐÍ¡£Ö÷¼üË÷ÒýÒªÇóÖ÷¼üÖеÄÿ¸öÖµÊÇΨһµÄ£¬²¢ÇÒ²»ÄÜΪ¿Õ
¾Û¼¯Ë÷Òý(Clustered)£º±íÖи÷ÐеÄÎïÀí˳ÐòÓë¼üÖµµÄÂß¼£¨Ë÷Òý£©Ë³ÐòÏàͬ£¬Ã¿¸ö±íÖ»ÄÜÓÐÒ»¸ö
·Ç¾Û¼¯Ë÷Òý(Non-clustered)£º·Ç¾Û¼¯Ë÷ÒýÖ¸¶¨±íµÄÂ߼˳Ðò¡£Êý¾Ý´æ´¢ÔÚÒ»¸öλÖã¬Ë÷Òý´æ´¢ÔÚÁíÒ»¸öλÖã¬Ë÷ÒýÖаüº¬Ö¸ÏòÊý¾Ý´æ´¢Î»ÖõÄÖ¸Õë¡£¿ÉÒÔÓжà¸ö£¬Ð¡ÓÚ249¸ö
Óŵã
¼Ó¿ì·ÃÎÊËÙ¶È
¼ÓÇ¿ÐеÄΨһÐÔ
ȱµã
´øË÷ÒýµÄ±íÔÚÊý¾Ý¿âÖÐÐèÒª¸ü¶àµÄ´æ´¢¿Õ¼ä
²Ù×ÝÊý¾ÝµÄÃüÁîÐèÒª¸ü³¤µÄ´¦Àíʱ¼ä£¬ÒòΪËüÃÇÐèÒª¶ÔË÷Òý½øÐиüÐÂ
Çë°´ÕÕÏÂÁбê׼ѡÔñ½¨Á¢Ë÷ÒýµÄÁС£
¸ÃÁÐÓÃÓÚÆµ·±ËÑË÷
¸ÃÁÐÓÃÓÚ¶ÔÊý¾Ý½øÐÐÅÅÐò
Ò»¡¢Ë÷ÒýµÄ¸ÅÄî
Ë÷Òý¾ÍÊǼӿì¼ìË÷±íÖÐÊý¾ÝµÄ·½·¨¡£Êý¾Ý¿âµÄË÷ÒýÀàËÆÓÚÊé¼®µÄË÷Òý¡£ÔÚÊé¼®ÖУ¬Ë÷ÒýÔÊÐíÓû§²»±Ø·ÔÄÍêÕû¸öÊé¾ÍÄÜѸËÙµØÕÒµ½ËùÐèÒªµÄÐÅÏ¢¡£ÔÚÊý¾Ý¿âÖУ¬Ë÷ÒýÒ²ÔÊÐíÊý¾Ý¿â³ÌÐòѸËÙµØÕÒµ½±íÖеÄÊý¾Ý£¬¶ø²»±ØÉ¨ÃèÕû¸öÊý¾Ý¿â¡£
¶þ¡¢Ë÷ÒýµÄÌØµã
1.Ë÷Òý¿ÉÒÔ¼Ó¿ìÊý¾Ý¿âµÄ¼ìË÷ËÙ¶È
2.Ë÷Òý½µµÍÁËÊý¾Ý¿â²åÈë¡¢Ð޸ġ¢É¾³ýµÈά»¤ÈÎÎñµÄËÙ¶È
3.Ë÷Òý´´½¨ÔÚ±íÉÏ£¬²»ÄÜ´´½¨ÔÚÊÓͼÉÏ
4.Ë÷Òý¼È¿ÉÒÔÖ±½Ó´´½¨£¬Ò²¿ÉÒÔ¼ä½Ó´´½¨
5.¿ÉÒÔÔÚÓÅ»¯Òþ²ØÖУ¬Ê¹ÓÃË÷Òý
6.ʹÓòéѯ´¦ÀíÆ÷Ö´ÐÐSQLÓï¾ä£¬ÔÚÒ»¸ö±íÉÏ£¬Ò»´ÎÖ»ÄÜʹÓÃÒ»¸öË÷Òý
7.ÆäËû
Èý¡¢Ë÷ÒýµÄÓŵã
1.´´½¨Î¨Ò»ÐÔË÷Òý£¬±£Ö¤Êý¾Ý¿â±íÖÐÿһÐÐÊý¾ÝµÄΨһÐÔ
2.´ó´ó¼Ó¿ìÊý¾ÝµÄ¼ìË÷ËÙ¶È£¬ÕâÒ²ÊÇ´´½¨Ë÷ÒýµÄ×îÖ÷ÒªµÄÔÒò
3.¼ÓËÙ±íºÍ±íÖ®¼äµÄÁ¬½Ó£¬ÌرðÊÇÔÚʵÏÖÊý¾ÝµÄ²Î¿¼ÍêÕûÐÔ·½ÃæÌرðÓÐÒâÒå¡£
4.ÔÚʹÓ÷Ö×éºÍÅÅÐò×Ӿ佸ÐÐÊý¾Ý¼ìË÷ʱ£¬Í¬Ñù¿ÉÒÔÏÔÖø¼õÉÙ²éѯÖзÖ×éºÍÅÅÐòµÄʱ¼ä¡£
5.ͨ¹ýʹÓÃË÷Òý£¬¿ÉÒÔÔÚ²éѯµÄ¹ý³ÌÖÐʹÓÃÓÅ»¯Òþ²ØÆ÷£¬Ìá¸ßϵͳµÄÐÔÄÜ¡£
ËÄ¡¢Ë÷ÒýµÄȱµã
1.´´½¨Ë÷ÒýºÍά»¤Ë÷ÒýÒªºÄ·Ñʱ¼ä£¬ÕâÖÖʱ¼äËæ×ÅÊý¾ÝÁ¿µÄÔö¼Ó¶øÔö¼Ó
2.Ë÷ÒýÐèÒªÕ¼ÎïÀí¿Õ¼ä£¬³ýÁËÊý¾Ý±íÕ¼Êý¾Ý¿Õ¼äÖ®Í⣬
Ïà¹ØÎĵµ£º
Ò»¡¢SQL SERVER ºÍACCESSµÄÊý¾Ýµ¼Èëµ¼³ö
³£¹æµÄÊý¾Ýµ¼Èëµ¼³ö£º
ʹÓÃDTSÏòµ¼Ç¨ÒÆÄãµÄAccessÊý¾Ýµ½SQL Server£¬Äã¿ÉÒÔʹÓÃÕâЩ²½Öè:
¡¡¡¡¡ð1ÔÚSQL SERVERÆóÒµ¹ÜÀíÆ÷ÖеÄTools£¨¹¤¾ß£©²Ëµ¥ÉÏ£¬Ñ¡ÔñData Transformation
¡¡¡¡¡ð2Services£¨Êý¾Ýת»»·þÎñ£©£¬È»ºóÑ¡Ôñ czdImport Dat ......
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØÊµÏÖSQL SERVER¡¢ACCESS¡¢EXCELÊý¾Ýת»»£ ......
Use equality first.
ʹÓõÈÁ¬½Ó
Use range operators only where equality does not apply.
Ö»ÓÐÔÚµÈÁ¬½Ó²»¿ÉÓõÄÇé¿öÏÂÊÂÓÉÇø¼äÁ¬½Ó
Avoid use of negatives in the form of !=
or NOT.
±ÜÃâʹÓà £¡= »òÕß not
Avoid LIKE pattern matching.
±ÜÃâʹÓà LIKEÆ¥Åä
Try to retrieve specific rows and in small n ......
Õâ¸öº¯ÊýDateAdd(month,2,WriteTime)£º
ÈÕÆÚ²¿·ÖËõд Year yy, yyyy quarter qq, q Month mm, m dayofyear dy, y Day dd, d Week wk, ww Hour hh minute mi, n second ss, s millisecond ms
¡¡¡¡Õâ¸ö±í×㹻˵Ã÷ÎÊÌâÁ˰É,´Óyearµ½millisecond¶¼¿ÉÒÔ´¦Àí£¬¹»·½±ãÁ˰É.
DATEDIFF º¯Êý [ÈÕÆÚºÍʱ¼ä]
-------------- ......
''' <summary>
''' SQLÎÄ執ÐÐ結¹û¤ÎEXCEL³öÁ¦
''' </summary>
''' <param name="connString">OLEDB½Ó続ÎÄ×ÖÁÐ</param>
''' <param name="sqlString">SQLÎÄ</param>
''' <param name="savePath">³öÁ¦¥Õ¥¡¥¤¥ë¥Ñ¥¹</param>
......