×ªÔØµÄһƪsqlserverÎÄÕÂ
¹«Ë¾×éÖ¯SQLˮƽ¿¼ÊÔ£¬¿´ÁËд½Ì²Ä£¬Ð´Á˵ã×ܽᷢÉÏÀ´¸ú´ó¼Ò·ÖÏí¡£
ÎÒ¹«Ë¾Ê¹ÓõÄÊÇSybase ASE12.5£¬ËùÒÔÏÂÃæµÄÒ»Ð©ÌØÐÔÊÇÕë¶ÔSybase ASEµÄ¡£
Ò»£ºSQL Bisic
1£ºSQL(Structured Quary Language)ÌØÐÔ£º
a£º±ê×¼»¯
b£º·Ç¹ý³Ì»¯µÄ
c£º¿ÉÓÅ»¯µÄ
d£ºÃæÏò¼¯ºÏ²Ù×÷µÄ
2£ºASEÖеÄÊý¾ÝÀàÐÍ
a£ºNumberic
b£ºCharacter
c£ºDate/Time
d£ºLobs
3: convert(varchar, textColumn),Èç¹û²»Ö¸¶¨varchar(n)nÄÇôĬÈÏÊÇ30
4£ºwhere ÔÚsqlÖеÄ×÷ÓÃ
a£º¹ýÂËÊý¾Ý
b£º×ö±íÁ¬½Ó(sql92ÒÔǰ)
c£ºÑ¡ÔñË÷Òý
5£ºwhare ºÍ havingµÄÇø±ð
whereÓï¾ä°Ñ¹ýÂ˺õÄÊý¾Ý²åÈëµ½work tableÖÐ
havingÓï¾ä´Ówork tableÖжÔÊý¾Ý½øÐÐÔÚ¹ýÂËÒԵõ½×îºóµÄ½á¹û¡£
6£ºÒ»¸öselectÓï¾äµÄÖ´ÐÐ˳Ðò
a£ºfrom clause
b£ºwhere clause
c£ºgroup by clause
d£ºselect clause
e£ºhaving clause
f£ºorder by clause
7£ºUnion VS Union All
a£ºUnion »á°ÑÁ½¸ö½á¹û¼¯ÅÅÐò£¬²¢ÇÒ³ýÈ¥ÖØ¸´µÄÔªËØ£¨Ð§ÂʲÇáÒײ»ÒªÓã©
b£ºUnion All½ö½öÊǰÑÁ½¸ö½á¹û¼¯ºÏ²¢£¬Ã»ÓÐÅÅÐò£¬Ò²²»È¥³ýÖØ¸´ÔªËØ£¨Ð§Âʺã©
¶þ£ºË÷ÒýºÍ²éѯ²ÎÊý
1£ºASEÖÐÓÐÈýÖÖaccessÊý¾Ý·½Ê½
a£ºclustered Index
b£ºnonclustered Index
c£ºtable scan
2£ºCovered Query
Ò»¸öCovered Query ½ö½ö´ÓË÷ÒýÖеõ½Êý¾Ý£¬²»ÓÃȥɨÃèÊý¾Ý¿â±í£¬ÕâÊÇ×î¿ìµÄÊý¾Ý²éѯ·½Ê½¡£
ÏÞÖÆ1£ºÖ»ÄÜÔÚseleceÖÐÉúЧ
ÏÞÖÆ2£ºËùÓб»ÒýÓõÄÁбØÐëÔÚͬһ¸önonclustered indexÖÐ
3£ºfunctional index
ÔÚASE15.0ÒÔºó²Å±»Ö§³Ö£¬Ò²¾ÍÊÇ˵ÔÚASE15.0ÒÔǰµÄ°æ±¾£¬ÏÂÁÐÓï¾äÊǿɶ¨²»»áÓÃÉÏË÷ÒýµÄ
sql ´úÂë
select
column1
from
table1
where
upper
(column2) = 'IVANL'
4£ºÈçºÎ²é¿´Ö´Ðмƻ®
sql ´úÂë
set
showplan
on
go
your sql
go
set
showplan
off
go
5: ÈçºÎ²é¿´IO
sql ´úÂë
set
statistics
io
on
set
statistics
time
on
go
you sql
go
set
statistics
io
off
set
statistics
time
off
go &nbs
Ïà¹ØÎĵµ£º
--spid:ËÀËøµÄ½ø³Ì,tableName :ËÀËøµÄ±í
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName from sys.dm_tran_locks
where resource_type='OBJECT'
--spid:Òª½áÊøµÄ½ø³Ìid
kill spid ......
sqlserver¸½¼ÓÊý¾Ý¿â´íÎó823µÄ½â¾ö·½°¸2008-10-13 15:06sqlserver¸½¼ÓÊý¾Ý¿â´íÎó823µÄ½â¾ö·½°¸Ò»¡¢SQL-Server¸½¼ÓÊý¾Ý¿âʱʧ°Ü¡£
1¡¢Òì³£Çé¿ö£º·þÎñÆ÷ÔÚÕý³£ÔËÐеÄÇé¿öÏÂͻȻ¶Ïµç£¬µ¼ÖÂÊý¾Ý¿âÎļþË𻵣¬¾ßÌå±íÏÖÊÇ£ºÊý¾Ý¿âÃûºóÃæÓГ£¨ÖÃÒÉ£©”×ÖÑù¡£
2¡¢Òì³£·ÖÎö£º¹ØÓÚ823´íÎóµÄ SQL-SERVER ÖеİïÖú£º
=== ......
ǰÕóÓöµ½ ±¾µØsqlserver2005¿ç¿â²éѯ
½á¹ûÊ×Ïȱ¨´íµÄÊÇ ÎÞ·¨ ´¦Àí²»Í¬µÄÅÅÐò¹æÔò
ÔÚÐÞ¸ÄÅÅÐò¹æÔòµÄ¹ý³ÌÖУ¬ÓÖ³öÏÖÁË£¬“ÎÞ·¨ÓÃÅÅËûËøËø¶¨¸ÃÊý¾Ý¿â£¬ÒÔÖ´ÐиòÙ×÷¡£”ÕâÑùµÄ´íÎó¡£
¾²é×ÊÁÏ·¢ÏÖ£¬ÐÞ¸ÄÊý¾Ý¿âΪµ¥Óû§·ÃÎÊ£¬¿ÉÒÔËø¶¨Êý¾Ý¿â¡£
alter database yourdatabase set single_user with rollback ......
¡¡È·Êµ£¬Êý¾Ý¿âµÄά»¤³£³£½»¸øÄÇЩרҵµÄÊý¾Ý¿â¹ÜÀíÔ±£¬µ«ÊÇ×÷Ϊһ¸ö¿ª·¢Õߣ¬ÄãÒ²Ðíż¶ûÐèÒªÔÝʱ´ÓÊÂÕâ¸ö¹¤×÷¡£ËùÒÔ£¬ÊÔÒ»ÊÔÕâÁ½¸öSQL·þÎñÆ÷ά»¤¼¼ÇÉ£ºÇáËɸıäÊý¾Ý¿âÓµÓÐÕß¡¢ÕûÀíË÷ÒýË鯬¡£Ë»áÏëµ½ÄãÉõÖÁ¿ÉÒÔ¸øÄÇЩÊý¾Ý¿â¹ÜÀíÔ±½ÌÉÏÒ»Á½¸öм¼ÇÉÄØ£¿ÖØÖ¸¶¨Êý¾Ý¿âÓµÓÐÕßµ±»Ø¸´»òÕßн¨Êý¾Ý¿âʱ£¬ÄãÓÐûÓÐ×¢Òâµ½SQL Server° ......
sqlserver ÖÐ È«¾Ö±äÁ¿ ÊÇÒ»ÖÖÌØÊâÀàÐ͵ıäÁ¿£¬È«¾Ö±äÁ¿ÒÔ@@¿ªÍ·£¬ÊÇÓÉϵͳ¶¨ÒåµÄ¡£
@@CONNECTIONS ·µ»Ø
SQL Server
×ÔÉÏ´ÎÆô¶¯ÒÔÀ´³¢ÊÔµÄÁ¬½ÓÊý¡£
@@CURSOR_ROWS
È·¶¨µ±Æä±»µ÷ÓÃʱ¼ìË÷ÁËÓαê·ûºÏÌõ¼þµÄÐÐÊý¡£
@@DATEFIRST
Õë¶Ô»á»°·µ»Ø
S ......