Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLServerË÷Òýµ÷ÓÅʵ¼ù(2) Ë÷Òý¸²¸Ç

¼ÌÐøSQLServerË÷Òýµ÷ÓÅʵ¼ù¡£Õâ´Î̽ÌÖÒ»ÏÂË÷Òý¸²¸Ç - SQL ServerÖ÷ҪʹÓÃË÷ÒýÈ¥²éѯÄãÐèÒªµÄÊý¾Ý£¬µ±Ë÷Òý°üÀ¨ËùÓеÄÄãÇëÇó²éѯµÄ×ֶΣ¬SQL Server½«²»ÐèҪȥÔÚ±íÖвéѯ¡£Õâ¸ö¸ÅÄî³Æ×ö“Ë÷Òý¸²¸Ç”¡£
SQLServer2005µÄNon-clustered INDEXÔö¼ÓÁËÒ»¸ö“°üº¬ÁÐ(included column)
”Ñ¡Ïî¡£ÔÚ SQL Server 2005
ÖУ¬¿ÉÒÔͨ¹ý½«·Ç¼üÁÐÌí¼Óµ½·Ç¾Û¼¯Ë÷ÒýµÄÒ¶¼¶±ðÀ´À©Õ¹·Ç¾Û¼¯Ë÷ÒýµÄ¹¦ÄÜ¡£Í¨¹ý°üº¬·Ç¼üÁУ¬¿ÉÒÔ´´½¨¸²¸Ç¸ü¶à²éѯµÄ·Ç¾Û¼¯Ë÷Òý¡£µ±²éѯÖеÄËùÓÐÁж¼×÷Ϊ¼üÁÐ
»ò·Ç¼üÁаüº¬ÔÚË÷ÒýÖÐʱ£¬´øÓаüº¬ÐԷǼüÁеÄË÷Òý¿ÉÒÔÏÔÖøÌá¸ß²éѯÐÔÄÜ¡£ÕâÑù¿ÉÒÔʵÏÖÐÔÄÜÌáÉý£¬ÒòΪ²éѯÓÅ»¯Æ÷¿ÉÒÔÔÚË÷ÒýÖÐÕÒµ½ËùÓÐÁÐÖµ£»²»·ÃÎʱí»ò¾Û¼¯
Ë÷ÒýÊý¾Ý£¬´Ó¶ø¼õÉÙ´ÅÅÌ I/O ²Ù×÷¡£
µ«Ó¦±ÜÃâÌí¼Ó²»±ØÒªµÄÁС£Ìí¼Ó¹ý¶àµÄË÷ÒýÁУ¨¼üÁлò·Ç¼üÁУ©»á¶ÔÐÔÄܲúÉú²»Á¼Ó°Ï죬Ӧ¸ÃºÏÀíʹÓ᣺ÍClustered
INDEX£¬»òÕß×éºÏË÷Òý£¬½áºÏʹÓã¬À©´óË÷Òý¸²¸Ç£¬µ«²»´ó¿ÉÄÜËùÓÐÁж¼ÓÐË÷Òý¸²¸Ç£¬´ÅÅÌ¿ªÏúºÍÊý¾Ýinsert
updatʱË÷ÒýµÄÖØÐ¼ÆËãµÄʱ¼ä¿ªÏúÊǾ޴óµÄ¡£×ÜÖ®£¬ºÏÀíµÄË÷ÒýÉè¼ÆÊǽ¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉϵģ¬Ö»ÓÐÕýÈ·µØÊ¹Ë÷ÒýÓë³ÌÐò½áºÏÆðÀ´,²ÅÄܲúÉú×î
¼ÑµÄÓÅ»¯·½°¸¡£
¼ÌÐøÊµ¼ù£¬ÏȽ¨¸öʵÑé±í Table1£º

½¨Á½¸öË÷Òý£º
1. Ö÷¼üIDÊÇClustered INDEX
2. ·Ç¾Û´ØË÷ÒýNon-Clustered INDEX½¨Á¢ÔÚAgeÁÐÉÏ£¬°üº¬ÁУºCount¡£
CREATE NONCLUSTERED INDEX [cnt] ON [dbo].[table1]
(
    [Age] ASC
)
INCLUDE ( [Count])
ON [PRIMARY]
ÎÒÃǵIJâÊÔSQLÓï¾äÊÇ£º´Ó10ÍòÌõ¼Ç¼ÖÐÈ¡³ö4Ìõ¼Ç¼£¬Á½ÖÖд·¨
1. SELECT *
from table1 WHERE age < 100;
2. SELECT count
from table1 WHERE age < 100;
¿´¿´ÔËÐÐЧÂÊÈçºÎ£º
´ÅÅÌIOºÍʱ¼ä£º

ʵ¼ÊÖ´Ðмƻ®£º

ÐÔÄܾÓÈ»Ïà²î20¶à±¶¡£ÎªÊ²Ã´£¿
Ô­À´µÚ¶þ¾äSelect Count
ÔÚË÷Òý¸²¸Ç·¶Î§ÄÚ£¬ÒòΪ²éѯÓÅ»¯Æ÷¿ÉÒÔÔÚË÷ÒýÖÐÕÒµ½ËùÓÐÁÐÖµ£»²»·ÃÎʱí»ò¾Û¼¯Ë÷ÒýÊý¾Ý£¬´Ó¶ø¼õÉÙ´ÅÅÌ I/O ²Ù×÷¡£¶øµÚÒ»¾äSelect *
Ñ¡ÔñÁËËùÓÐ×ֶΣ¬ÆäÖÐÓÐÒ»¸ö×Ö¶ÎName²»ÔÚË÷Òý¸²¸Ç·¶Î§ÄÚ£¨¼È²»ÔÚ¾Û´ØË÷ÒýÁУ¬Ò²²»ÔڷǾ۴ØË÷Òý¸²¸ÇÁÐÄÚ£©£¬SQL
Server¿ÉÒÔÔÚͬһ¸ö²éѯÖÐΪһ¸ö±íʹÓöà¸öË÷Òý£¬²¢¿ÉÒԺϲ¢¶à¸öË÷Òý£¨Ê¹ÓÃÁª½ÓËã·¨£©£¬ÒÔ±ãËÑË÷¹Ø¼ü×Ö¹²Í¬¸²¸ÇÒ»¸ö²éѯ¡£²éѯ·ÖÎöÓÅ»¯Æ÷»á×Ô¶¯½øÐÐ
Ñ¡Ôñ, ÉÏÊöÖ´Ðмƻ®¾ÍÊÇÓÅ»¯µÄ½á¹û£¬ÒÀÈ»±ÈµÚ¶þ¸öindex seekÂýÁË20±¶¡£
È»ºóÎÒÓÖ°Ñsql¸ÄÁËһϣ¬±ä³É´Ó10ÍòÌõ


Ïà¹ØÎĵµ£º

Óû§"sa "µÇ½ʧ°Ü SQLServer ´íÎó18456

ÓÃwindowsÕ˺ݹÊÇ¿ÉÒԵǼµÄ¡£
²éÕÒÎÊÌâµÄ¹ý³Ì£º
µÚÒ»²½£º Æô¶¯ËùÓÐÓëSQLÓйصķþÎñ£¬ÎÊÌâÒÀ¾É£»
µÚ¶þ²½£º ²é¿´windows·À»ðǽ£¬±»Ä¬ÈÏÆô¶¯ÁË£¬²»Æô¶¯Ñ¡ÔñÏî±»Disabled¡£Á½¸öÌáʾ“ÓÉÓÚ°²È«¿¼ÂÇ,ijЩÉèÖÃÓÉ×é²ßÂÔ¿ØÖÆ”“Windows·À»ðǽÕýÔÚʹÓÃÄúµÄÓòÉèÖÔ£¬ÏÈÓÃgpedit.msc´ò×é²ßÂԱ༭Æ÷£¬»úËã»úÅ ......

DatetimeÏÔʾ·½Ê½SQLServer


½ñÌìÔÚдÊÓͼʱ£¬Óöµ½Òª°ÑDatetimeÀàÐÍתVarcharÀàÐÍ¡£ÒÔǰÔÚORALCE¾ÍÈÝÒ×£¬Ö±½ÓToChar(getdate(),'yyyy-mm-dd')¡£ÔÚSQL Server 2005
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
200 ......

ÔÚSqlServer´æ´¢¹ý³ÌÖÐʹÓÃCursor£¨Óα꣩²Ù×÷¼Ç¼

 
1. ΪºÎʹÓÃÓα꣺
    
ʹÓÃÓαê(cursor)µÄÒ»¸öÖ÷ÒªµÄÔ­Òò¾ÍÊǰѼ¯ºÏ²Ù×÷ת»»³Éµ¥¸ö¼Ç¼´¦Àí·½Ê½¡£ÓÃSQLÓïÑÔ´ÓÊý¾Ý¿âÖмìË÷Êý¾Ýºó£¬½á¹û·ÅÔÚÄÚ´æµÄÒ»¿éÇøÓòÖУ¬ÇÒ½á¹û
ÍùÍùÊÇÒ»¸öº¬Óжà¸ö¼Ç¼µÄ¼¯ºÏ¡£Óαê»úÖÆÔÊÐíÓû§ÔÚSQL serverÄÚÖðÐеطÃÎÊÕâЩ¼Ç¼£¬°´ÕÕÓû§×Ô¼ºµÄÒâÔ¸À´ÏÔʾºÍ´¦ÀíÕ ......

ʹÓÃÁÙʱ±íÌáÉýSqlServerÊÓͼ²éѯÐÔÄÜ

дÁËÒ»¸ö´æ´¢¹ý³Ì¶ÔÊÓͼ½øÐзÖÒ³²éѯ£¬µ«Êý¾ÝÔö¶àºó·¢ÏÖ»ùЧÂʵ͵ÃÒªÃü£¬ÈýÍò¶àÌõÊý¾ÝÒª²éѯһ¸ö°ëСʱ¶¼Ã»³öÀ´£¬Õâ²»ÊÇÒªÁËÃü£¬ÓÚÊÇÏëµ½ÁËË÷Òý£¬Ó¦ÓùýºóÈÔÎÞ¼ÃÓÚÊ¡£×îºó¶Ôsql½øÐзÖÎöºÍʵ¼ùÖеóö£¬Ê¹ÓÃÁÙʱ±í¿ÉÒÔ´ó´ó¼Ó¿ìÊÓͼµÄ²éѯËÙ¶È£¬¼ûÈçÏÂsqlÓï¾ä
ÐÔÄܳ¬µÍµÄÊÓͼ·ÖÒ³sqlÓï¾ä£º
select top 100 * from
view_c ......

JavaScript ºÍJSP Á¬½ÓSqlServer Êý¾Ý¿â

µÚÒ»ÖÖ JavaScriptÁ¬½ÓÊý¾Ý¿â£º
<mce:script language="JavaScript"><!--
// ´´½¨Êý¾Ý¿â¶ÔÏó
var objdbConn = new ActiveXObject("ADODB.Connection");
// DSN×Ö·û´®
var strdsn = "Driver={SQL Server};SERVER=192.168.0.210;UID=xhb;PWD=xhb;DATABASE=books";
// ´ò¿ªÊý¾ÝÔ´
objdbConn.Open(strdsn);
// ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ