Ò»´ÎÉñÆæµÄSQL ´íÎóµ÷ÊÔ¾Àú
ÉÏÖܽӵ½Ò»¸öÆæ¹ÖµÄbug£¬Ò»¸öÔø¾ÔËÐеúܺõĴ洢¹ý³ÌͻȻ²úÉúÁË´íÎóµÄ½á¹û¡£
¸ºÔðά»¤µÄÐÖµÜÃǺܸºÔðÈεĶԴíÎó½øÐÐÁ˸ú×Ù£¬²¢°Ñ´íÎó¶¨Î»Ò»¸öÈçϵÄÓï¾ä£º
SELECT *
into SomeTable
from A join B on A.id=B.id
join C on A.id=C.id
ËûÃÇ·¢ÏÖ´ÓSomeTable×ö²éѯµÄʱºò£¬³öÀ´µÄ½á¹û±Èʵ¼Ê½á¹ûÒª´óÈô¸É±¶£¬Í¦ÆëÕûµÄ£¬4±¶»òÕß2±¶¡£
ÎҵĵÚÒ»¸ö¸Ð¾õÊÇJOIN³öÎÊÌâÁË£¬±ØÈ»ÊÇÐγÉÁ˶à¶Ô¶àµÄJOIN£¬±ÈÈçAÀïÃæÓÐÁ½ÐÐIDÒ»Ñù£¬BÒ²ÓÐÁ½ÐÐIDÒ»Ñù£¬½á¹û±í¾Í»á³öÏÖ4ÐÐÒ»ÑùµÄ£¬µÃµ½Ò»¸ö4±¶µÄ½á¹û£¬Í¬ÑùµÄµÀÀí£¬Èç¹ûAÀïÓÐÒ»ÐУ¬BÀïÓÐ2ÐУ¬½á¹û¾ÍÊÇ2±¶ÁË¡£
ÓÚÊÇÎÒÈÃËûÃǰÑSomeTable×ö³ÉÒ»¸öÎı¾Îļþ´«¸øÎÒ£¬àÅ£¬300MµÄÒ»¸öÎļþ£¬BCPµ½Êý¾Ý¿âÀSELECTÁËÒ»°Ñ£¬°Ñ³öÎÊÌâµÄÄǸöID×¥³öÀ´£¬¹ûÈ»¿´µ½¼¸¸öÖØ¸´µÄÐС£È·ÇеÄ˵¿´µ½¼¸Áм¸ºõÒ»ÑùµÄÐС£
ÓÚÊÇÎÒ¾ÍÐ˳å³åµÄ¸øËûÃÇ˵£¬¼ì²ìÒ»ÏÂBºÍCÉÏÃæµÄIDÊÇ·ñÊÇuniqueµÄ¡£
½á¹ûºÜ¿ì·µ»Ø£¬Ëµ£¬ÊÇuniqueµÄ¡£
ÕâÏÂÎÒ¾ÍɵÑÛÁË£¬ÕâÔõô¿ÉÄÜÄØ¡£ÖØÐÂ×ÐϸµÄ¿´Á˱ãÉϴβ鴦µÄ½á¹û£¬·¢ÏÖÔ±¾ÒÔÎªÖØ¸´µÄÐУ¬¾¹È»²»ÊÇÍêÈ«ÏàͬµÄ£¬àÅ£¬ÓÐÒ»ÁÐÊDz»Í¬µÄ£¬¶øÕâÒ»ÁÐÕýʽB.ID¡£Çë×¢Ò⣬ÕâЩÐÐÔÚA.idÉÏÊÇÒ»ÑùµÄ¡£
ÕâÊÇÒ»¸ö¾ªÈ˵ķ¢ÏÖ£¬ÎÒͨ¹ýA JOIN B JOIN C³öÀ´µÄ½á¹û¾ÓÈ»²»Âú×ãJIONµÄÌõ¼þ£¬Õâ³¹µ×µß¸²Á˶àÄêѧϰµÄ¹ØÏµÊý¾Ý¿â֪ʶ¡£
ºÁÎÞÒÉÎÊ£¬ÕâÊÇSQL ServerµÄÒ»¸öbug¡£È»¶øÈçºÎ½¨Ò»¸ö×îСµÄÊý¾Ý¼¯ºÏÀ´ÖØÏÖÕâ¸öbug³ÉÁËеÄÌôÕ½¡£ÒòΪABCÈý¸ö±í¼ÓÆðÀ´ÓÐ200G£¬¶øÈç¹ûÎÒɾµôһЩ¿´ÆðÀ´²»Ïà¹ØµÄÐУ¬´íÎó¾ÍÏûʧÁË¡£ÎÒÒ²ÊÔͼ½«ABCµÄJION·Ö³ÉÁ½²½À´×ö£¬
±ÈÈ磺
select *
from
(SELECT * from A JOIN B) AS D
JOIN C ON...
½á¹ûÈÔÈ»´íÎó¡£
µ«ÊÇÈç¹ûÔÙ½øÒ»²½£¬
SElect * into temp from A join B...
Select * from temp join C...
´íÎóÏûʧÁË¡£
Ç¿¼¼ÇîÖ®ºó£¬°³Ö»ºÃÇó¾ÈÓÚSQl serverµÄ¼¼ÊõÖ§³Ö¡£¸Õ¿ªÊ¼·¢ÁË·âÐÅ£¬ÎÊÓÐûÓÐÈËÓöµ½¹ýÕâÖÖÎÊÌ⣬ABC ½»³öµÄ½á¹û°üº¬²»Âú×ãJOINÌõ¼þµÄÐС£½á¹ûÒ»¸öС×Ó¾ÓȻ˵£¬Õâ¸öÎÊÌâÌ«»ù±¾£¬ÕâÖÖÇé¿ö¸ù±¾²»¿ÉÄܳöÏÖ¡£ Sigh£¬´óÊåÎÒÒ²²»ÊÇÐÂÊÖ£¬¼òµ¥ÎÊÌ⻹ÐèÒªÀÍ·³ÄúÀÏÈ˼ÒÂð¡£
ÓÚÊÇÔÙ×ÐϸµÄÃèÊöÎÊÌ⣬ÖÕÓÚ³öÁËÒ»¸ö±È½Ï¿¿Æ×µÄ¸çÃÇ£¬ÈÃÎÒ°ÑÖ´Ðмƻ®·¢¸øËû¿´¿´¡£ÔÙÈ»ºóÁíÍâÒ»¸ö¸çÃÇ˵¿ÉÄÜÊDz¢·¢ÒýÆðµÄÎÊÌ⣬½¨ÒéÎÒÓÃMAXDOPÀ´ÏÞÖÆ²ÎÓëÖ´ÐеÄCPU¸öÊý¡£ÓÚÊÇ£¬ÎÒÔÚÔÀ´µÄSQLºóÃæ¼ÓÁ˸öOPTION MAXDOP=1£¬¹ûÈ»¾¹ý7·ÖÖÓµÄÖ´ÐУ¬Îҵõ½ÁËÕýÈ·µÄ½
Ïà¹ØÎĵµ£º
[code=SQL][/code]
--Óï ¾ä ¹¦ ÄÜ
--Êý¾Ý²Ù×÷
SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
--Êý¾Ý¶¨Òå
CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
DROP TABLE --´ÓÊý¾Ý ......
SQL Server 2005 CE¿ª·¢»·¾³½éÉÜ£º
×î½üѧϰʹÓÃSQL Server 2005 Compact Edition½øÐÐÊý¾Ý´æ´¢£¬ÔÚѧϰµÄ¹ý³ÌÖз¢ÏÖ£¬Ê¹ÓÃSQL Server2005 management Studio´´½¨Êý¾Ý¿âʱ£¬ÔÚ“·þÎñÆ÷ÀàÐÍ”ÏÂÀÁбíÖÐûÓГSQL Server Compact Edition”Ñ¡Ïî¡£ÒòΪÎÄÕ ......
Tuning an Application / Reducing Load
If your whole application is performing suboptimally, or if you are attempting to
reduce the overall CPU or I/O load on the database server, then identifying
resource-intensive SQL involves the following steps:
1. Determine which period in the day you would ......
×òÌìÎÒµÄSQL£¨Microsoft SQL Server 2005 £©µÇ¼²»ÉÏÈ¥ÁË£¬ÔÀ´ÊdzöÏÖÁ˼¸¸öСÎÊÌ⣬ÏÖÔڼǼһÏÂÎҵĽâ¾öÕ⼸¸öÇé¿öµÄ°ì·¨¡££¨½â¾ö·½·¨ÓкܶàÖÖ£¬ÕâЩֻÊÇÎҵĽâ¾ö·½·¨£¬½ö¹©²Î¿¼£©
²éÕÒÎÊÌâµÄ¹ý³Ì£º£¨×¢£ºÓÃwindowsÕ˺ݹÊÇ¿ÉÒԵǼµÄ£©
µÚÒ»²½£ºÆô¶¯ËùÓÐÓëSQLÓйصķþÎñ£¬ÎÊÌâÒÀ¾É£»
µÚ¶þ²½£º²é¿´windows·À»ðǽ£¬±»Ä¬ ......
Êý¾Ý¿â×Öµä°üÀ¨±í½á¹¹(·Ö2KºÍ2005£©¡¢Ë÷ÒýºÍÖ÷¼ü.Íâ¼ü.Ô¼Êø.ÊÓͼ.º¯Êý.´æ´¢¹ý³Ì.´¥·¢Æ÷.¹æÔò¡£¿ÉÒÔÔÚÆóÒµ¹ÜÀíÆ÷¡¢²éѯ·ÖÎöÆ÷Öмòµ¥Ö´ÐУ¬Ö±½ÓÁ˵±µÄ²é³öSQL2K¼°SQL2005µÄËùÓÐÊý¾Ý×ֵ䣬·½±ãÎĵµµÄ±àд£¬Ï£Íû¶Ô´ó¼ÒÓаïÖú¡£
1. SqlServer2000Êý¾Ý¿â×Öµä--±í½á¹¹.sql
SELECT TOP 100 PERCENT --a.id,
& ......