´óÊý¾ÝÁ¿Ï¿ìËÙ»ñÈ¡ SQL Server Êý¾Ý¿â±í¼Ç¼ÊýµÄ·½·¨
ÔÚÊý¾Ý¿âÓ¦ÓõÄÉè¼ÆÖУ¬ÎÒÃÇÍùÍù»áÐèÒª»ñȡijЩ±íµÄ¼Ç¼×ÜÊý£¬ÓÃÓÚÅжϱíµÄ¼Ç¼×ÜÊýÊÇ·ñ¹ý´ó£¬ÊÇ·ñÐèÒª±¸·ÝÊý¾ÝµÈ¡£ÎÒÃÇͨ³£µÄ×ö·¨ÊÇ£ºselect count(*) as c from tableA ¡£È»¶ø¶ÔÓڼǼÊý¾Þ´óµÄ±í£¬ÉÏÊö×ö·¨½«»á·Ç³£ºÄʱ¡£ÔÚDELL 4400 ·þÎñÆ÷ÉÏ×öÊÔÑ飬MS Sqlserver 2000 Êý¾Ý¿â¶ÔÓÚ100Íò¼Ç¼µÄ¼òµ¥Êý¾Ý±íÖ´ÐÐÉÏÊöÓï¾ä£¬Ê±¼äÔÚ1·ÖÖÓÒÔÉÏ¡£Èç¹ûÔÚ±íµÄij¸ö×Ö¶ÎÉÏ×ö¾Û´ØË÷Òý£¬µÚÒ»´ÎÖ´ÐиÃÓï¾äµÄʱ¼äºÍûÓÐË÷ÒýµÄʱ¼ä²î²»¶à£¬Ö®ºóÖ´ÐÐÉÏÊöÓï¾ä£¬ËٶȺܿ죬ÔÚ1ÃëÖÐÒÔÄÚ£¬µ«µ±±íµÄ¼Ç¼Êý·¢Éú½Ï´ó±ä»¯ºó£¬ÔÙÖ´ÐиÃÓï¾äÓÖ»á¾ÀúÒ»´ÎºÄʱµÄ¹ý³Ì¡£¶øÇÒ²»ÊÇÿ¸ö±í¶¼ÊʺÏ×ö¾Û´ØË÷ÒýµÄ£¬¶ÔÓÚÊýÁ¿¾Þ´óµÄ±í£¬Èç¹ûÐèÒª¾³£Ôöɾ²Ù×÷£¬½¨¾Û´ØË÷ÒýÊÇÒ»¸öºÜ²»Ã÷ÖǵÄ×ö·¨£¬½«»á¼«´óµÄÓ°ÏìÔöɾµÄËٶȡ£ÄÇôÓÐûÓÐÒ»¸ö±È½Ï¼òµ¥µÄ·½·¨¿ìËÙ»ñÈ¡±íµÄ¼Ç¼×ÜÊýÄØ£¿´ð°¸ÊÇÓеġ£
ÔÚMS SQL Êý¾Ý¿âÖÐÿ¸ö±í¶¼ÔÚsysindexes ϵͳ±íÖÐÓµÓÐÖÁÉÙÒ»Ìõ¼Ç¼£¬¸Ã¼Ç¼ÖеÄrows ×ֶλᶨʱ¼Ç¼±íµÄ¼Ç¼×ÜÊý¡£
ÏÂÃæÊÇsysindexes ±íµÄÏà¹Ø¼Ç¼µÄº¬Ò壺
ÁÐÃû Êý¾ÝÀàÐÍ ÃèÊö
id int ±íID(Èç¹û indid = 0 »ò255)¡£·ñÔòΪË÷ÒýËùÊô±íµÄID
Indid smallint Ë÷ÒýID£º
0£½±í
1£½¾Û´ØË÷Òý
>1£½·Ç¾Û´ØË÷Òý
255£½¾ßÓÐtext»òimageÊý¾ÝµÄ±íÌõÄ¿¡£
rows int »ùÓÚindid=0 ºÍ indid=1µØÊý¾Ý¼¶ÐÐÊý£¬¸ÃÖµ¶ÔÓÚindid>1ÖØ ¸´¡£Èç¹ûindid=255£¬rowsÉèÖÃΪ0¡£
µ±±íûÓо۴ØË÷Òýʱ£¬Indid = 0 ·ñÔòΪ 1¡£
ÄÇôÏÖÔÚ´ó¼ÒÓ¦¸ÃÖªµÀÈçºÎ»ñÈ¡±íµÄ¼Ç¼×ÜÊýÁË£¬Ö»ÐèÖ´ÐÐÈçÏÂÓï¾ä£º
select rows from sysindexes where id = object_id('tablename') and indid in (0,1)
¸Ã·½·¨»ñÈ¡±íµÄ¼Ç¼×ÜÊýµÄËٶȷdz£¿ì£¬ÔÚºÁÃ뼶¾Í¿ÉÒÔÍê³É£¬Ïà±Èselect count(*) Òª¿ìÉÏÊýÍò±¶£¬µ«ÊÇ´ó¼ÒÔÚÔËÓø÷½·¨ÊÇÒ»¶¨ÒªÖ÷Òª£¬¸Ã·½·¨µÃµ½µÄ±íµÄ×ܼǼÊý²»ÊÇÒ»¸ö¾«È·Öµ£¬ÔÒòÊÇMS SQL ²¢²»ÊÇʵʱ¸üиÃ×ֶεÄÖµ£¬¶øÊǶ¨Ê±¸üУ¬µ±´Óʵ¼ùÀ´¿´¸ÃÖµºÍ¾«È·ÖµÒ»°ãÎó²î²»´ó£¬Èç¹ûÄãÏ£Íû¿ìËٵĴÖÂÔ¹ÀËã±íµÄ´óС£¬½¨ÒéÄã²ÉÓø÷½·¨¡£Èç¹ûÄãÏ£ÍûµÃµ½¾«È·Öµ£¬ÄÇôÇëÔÚÖ´ÐÐÉÏÊöÓï¾äÇ°Ö´ÐÐDBCC UpdateUSAGE(DatabaseName,[TABLENAME]) WITH ROW_COUNTS Ç¿ÖƸüиÃ×ֶεÄÖµ£¬µ«ÕâÑùµÚÒ»´Î¸üÐÂʱ»áºÄ·Ñ´óÁ¿µÄʱ¼ä£¬ÕâÑù×öµÄЧ¹ûºÍ½¨Óо۴ØË÷ÒýµÄ±í select count (*) Ч¹ûÏà²î²»´ó£¬ËùÒÔÈç¹ûÄãÏ£ÍûÏà¶Ô¿ìËٵصõ½¾«È·µÄ±íµÄ¼Ç¼×ÜÊý£¬ÄÇôÄãÓÐÁ½ÖÖÑ¡Ôñ£¬½¨¾Û´ØË÷Òý»òÕßÏÈDBCC ÔÙʹÓÃÉÏÊö·½·¨
Ïà¹ØÎĵµ£º
ÔÚ¹«¹²ÐÂÎÅ×éÖУ¬Ò»¸ö¾³£³öÏÖµÄÎÊÌâÊÇ“ÔõÑù²ÅÄܸù¾Ý´«µÝ¸ø´æ´¢¹ý³ÌµÄ²ÎÊý·µ»ØÒ»¸öÅÅÐòµÄÊä³ö£¿”¡£ÔÚһЩ¸ßˮƽר¼ÒµÄ°ïÖú֮ϣ¬ÎÒÕûÀí³öÁËÕâ¸öÎÊÌâµÄ¼¸ÖÖ½â¾ö·½°¸¡£
Ò»¡¢ÓÃIF...ELSEÖ´ÐÐÔ¤ÏȱàдºÃµÄ²éѯ
¡¡¡¡¶ÔÓÚ´ó¶àÊýÈËÀ´Ëµ£¬Ê×ÏÈÏëµ½µÄ×ö·¨Ò²ÐíÊÇ£ºÍ¨¹ýIF...ELSEÓï¾ä£¬Ö´Ðм¸¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ» ......
ÔÚÍøÉÏ¿´µ½¹ØÓڹ鲢Ëã·¨£¬Ã»ÓÐÊý¾Ý¿â¼¶µÄ£¬µ±È»Êý¾Ý¼¶µÄËã·¨¶ÔÓÚÊý¾Ý¿âÀ´Ëµ¾ÍÊÇÒ»¸öÅÅÐò¡£½ñÌìдһ¸öpl/sqlµÄ¹é²¢Ëã·¨¡£
¹é²¢µÄËã·¨ºÜÈÝÒ×Àí½â£¬ÍøÉϺÜÏêϸ£¬ÏÖÖ»°ÑÔ´ÂëÌù³öÀ´¡£
£¨°üÉùÃ÷²¿·Ö£©
CREATE OR REPLACE PACKAGE PG_MERGESORT IS
-- Author : wealth_khb@126.com
-- Created : 2009-10- ......
SQLÓï¾ä²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(Create£¬Alter£¬Drop£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(Select£¬Delete£¬Update£¬Insert)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
Create&n ......
ÔÚÈ¡Ò»¸öƽ¾ùÖµµÄʱºòÍùÍùÓöµ½Ð¡ÊýµãλÊýµÄ½ØÈ¡¡£
Ç°¼¸ÌìÓöµ½¹ýÕâô¸öÎÊÌ⣬ÔÚKingDee EAS µÄ²éѯ·ÖÎöÆ÷ÀïÃæÈç¹ûÇóijһÎïÁϵÄƽ¾ùµ¥¼ÛʱÎÒÕâÑùдµÀ£º
select avg = case sum(bentry.FQuantity) when 0 then 0 else (sum(bentry.famount)/sum(bentry.FQuantity)) end,
´ËÓï¾ä¿´ËÆûʲôÎÊÌ⣬±Ï¾¹ÊÇÇóƽ¾ùÖµ£¬¿ÉÊÇÈç¹ûÒªÔ ......
Table-Naming Standards
Table-naming standards, as well as any standard within a business, are critical to
maintaining control. After studying the tables and data in the previous sections, you
probably noticed that each table’s suffix is _TBL. This is a naming standard selected
for use, suc ......