SQLServer ά»¤Ë÷ÒýʵÏÖ²éѯÓÅ»¯
Ë÷Òý·ÖΪÁ½´óÀà:¾Û¼¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷Òý
Ò»¡¢¾Û¼¯Ë÷Òý
µ±Êý¾Ý±íÖеÄÒ»Áб»È·¶¨ÎªÖ÷¼üºó,SQLServer»á×Ô¶¯ÎªËü½¨Á¢¾Û¼¯Ë÷Òý,ÒòΪ¾Û¼¯Ë÷ÒýÊDZêʶÿ¸ö¼Ç¼Ðеļü,ËùÒÔËü½«±»Ó¦Óõ½Ã¿¸ö²éѯÖÐ.
¶þ¡¢·Ç¾Û¼¯Ë÷Òý
·Ç¾Û¼¯Ë÷ÒýµÄÇé¿ö¾Í±È½Ï¸´ÔÓÁË,ÒòΪËüÊÇÏà¶ÔÓÚ±í¶ÀÁ¢×éÖ¯µÄ,ÔÚSQLServerÖÐÓе¥¶ÀµÄ½á¹¹À´´æ´¢·Ç¾Û¼¯Ë÷Òý.
ÓÐÒ»µãÊÇҪעÒâµÄ,²»Òª´úÌæ²éѯÓÅ»¯Æ÷È¥Ö¸¶¨Ä³¸öË÷Òý,DBAÓ¦¸ÃÏë°ì·¨±ÜÃâÓÅ»¯Æ÷²»Ê¹ÓÃË÷Òý½øÐвéѯ:
1¡¢¸ßËéƬÂÊ
µ±Ë÷ÒýËéƬÂʳ¬¹ý40%µÄʱºò,ÓÅ»¯Æ÷»á·ÅÆúʹÓÃË÷Òý,ÒòΪÔÚËéƬÂÊÈç´Ë¸ßµÄË÷ÒýÖÐɨÃèÊý¾Ý»¹²»ÈçÖ±½Óµ½±íÖÐȥɨÃè.
DBAÓ¦¸Ã³£³£¼àÊÓË÷ÒýËéƬÂÊ,µ±´ïµ½30%µÄʱºò¾ÍÒª¿¼ÂÇÖØÐÂ×éÖ¯Ë÷Òý
2¡¢Î¨Ò»ÐÔ
Èç¹ûÒ»¸öË÷Òý²»ÊÇΨһË÷Òý,ÄÇôÓÅ»¯Æ÷»áÈÏΪֱ½Óµ½Êý¾Ý±íÖÐɨÃè±ÈʹÓÃÕâ¸öË÷ÒýµÄЧÂÊÒª¸ß
3¡¢²éѯÖÐʹÓÃÁ˺¯Êý
µ±²éѯÖÐʹÓÃÁ˺¯Êýʱ,ÔÚ¸ÃÁÐÉϽ¨Á¢µÄË÷ÒýÊDz»»á±»Ê¹ÓõÄ.
ÀýÈç:col½¨Á¢ÁËË÷Òý,µ«ÊÇÔÚ²éѯÖÐʹÓÃÁË convert(nvarchar,col)=’test’,Õâʱ²éѯÓÅ»¯Æ÷²»»áʹÓøÃË÷Òý
4¡¢²éѯ´íÎóµÄ×Ö¶Î
Èç¹û½¨Á¢×éºÏ×ֶεÄË÷Òý,ÄÇôֻÓе±µÚÒ»¸öÁб»²éѯʹÓÃʱ,¸ÃË÷Òý²Å»á±»Ê¹ÓÃ
ÀýÈç:½¨Á¢Ò»¸öË÷Òý(col1,col2,col3),µ±²éѯÖÐʹÓÃcol2=’test’ʱË÷Òý²»»á±»Ê¹ÓÃ.Ö»ÓÐcol1=’test’ÕâÑùµÄ²éѯ,Õâ¸öË÷Òý²Å»á±»Ê¹ÓÃ
Èç¹ûÄãÏë¶Ôcol3²éѯʱʹÓÃË÷Òý,ÄÇôֻÄܽ¨Á¢Á½¸öË÷Òý,Ò»¸ö½¨Á¢ÔÚcol1ÉÏ,ÁíÒ»¸ö½¨Á¢ÔÚcol3ÉÏ
ÁíÍâÒ»¸öҪעÒâµÄÊÇ,·Ç¾Û¼¯Ë÷ÒýÓë¾Û¼¯Ë÷Òý²»Í¬,¾Û¼¯Ë÷ÒýÌî³äÒò×ÓΪ100%²¢ÇÒûÓÐËéƬ,ÒòΪÊý¾Ýʱ˳Ðò×·¼ÓµÄ.·Ç¾Û¼¯Ë÷ÒýÒª¿¼Âǵ½Êý¾ÝÌî³äʱ,Ë÷ÒýÒ³Ìî³äÒò×Ó.
ÔÚÖؽ¨Ë÷Òýʱ»¹Òª¸ù¾ÝÇ°Ò»½×¶ÎµÄ¼àÊÓÖØÐÂÉ趨Ìî³äÒò×Ó.Ò»°ãµÄÇé¿öÊÇ70%-80%,ÎÒ×îµÍʹÓùý65%µÄÌî³äÒò×Ó.
Ïà¹ØÎĵµ£º
sp_databases --Áгö·þÎñÆ÷ÉϵÄËùÓÐÊý¾Ý¿â
sp_server_info --Áгö·þÎñÆ÷ÐÅÏ¢£¬Èç×Ö·û¼¯£¬°æ±¾ºÍÅÅÁÐ˳Ðò
sp_stored_procedures--Áгöµ±Ç°»·¾³ÖеÄËùÓд洢¹ý³Ì
sp_tables --Áгöµ±Ç°»·¾³ÖÐËùÓпÉÒÔ²éѯµÄ¶ÔÏó
sp_start_job --Á¢¼´Æô¶¯×Ô¶¯»¯ÈÎÎñ
sp_stop_job --Í£Ö¹ÕýÔÚÖ´ÐеÄ×Ô¶¯»¯ÈÎÎñ
sp_password --Ìí¼Ó»òÐ ......
ÔÚÓÃAsp.net¶Ô±¸·ÝµÄÊý¾Ý¿âÎļþ½øÐл¹ÔµÄʱºò£¬ÓÐʱºò»á³öÏÖÏÂÃæµÄ´íÎóÒì³££º
[ÒòΪÊý¾Ý¿âÕýÔÚʹÓã¬ËùÒÔδÄÜ»ñµÃ¶ÔÊý¾Ý¿âµÄÅÅËü·ÃÎÊȨ¡£ RESTORE DATABASE ²Ù×÷Òì³£ÖÕÖ¹¡£Òѽ«Êý¾Ý¿âÉÏÏÂÎĸÄΪ 'master'¡£]
Õâ¸öʱºò£¬×ÔÓɵȴýÆäËû½ø³ÌÊͷŶÔӦȨÏ޺󣬲ſɻ¹Ô³É¹¦£¡ÓÐûÓнâ¾ö°ì·¨ÄØ£¿ÎÒ²ÎÕÕ¡¾Java¡¿¶ÔÕâ¸öÎÊÌâµÄ½â¾ö ......
18¾ä»°ÈëÃÅSQLServer XML ÊÕ²Ø
/*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏʶԪËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: ......
SQLServerºÍOracleÊÇ´ó¼Ò¾³£Óõ½µÄÊý¾Ý¿â£¬ÔÚ´Ë×ܽá³öÕâЩ³£Óú¯ÊýÒÔ¹©
´ó¼Ò²Î¿¼¡£
Êýѧº¯Êý
¡¡¡¡1.¾ø¶ÔÖµ
¡¡¡¡S:select abs(-1) value
¡¡¡¡O:select abs(-1) value from dual
¡¡¡¡2.È¡Õû(´ó)
¡¡¡¡S:select ceiling(-1.001) value
¡¡¡¡O:select ceil(-1.001) value from dual
¡¡¡¡3.È¡Õû£¨Ð¡£©
¡¡¡¡S:s ......
sqlserver 2000ÅÅÃûº¯Êý
Ãû´ÎÉú³É·½Ê½1,Öظ´Ê±ºÏ²¢Ãû´Î
SELECT *¡¡,¡¡Place=(SELECT COUNT(DISTINCT Score) +1 from jh03 WHERE Score >= a.Score)
from jh03 a
ORDER BY Place
Ãû´ÎÉú³É·½Ê½2 , Öظ´Ê±±£ÁôÃû´Î¿Õȱ
SELECT * , Place=(SELECT COUNT(Score) from jh03 WHERE Score > a.Score) + 1
from j ......