ÀûÓÃͬÒå´Ê¼ò»¯SQL Server 2005¿ª·¢
Ò». ÒýÑÔ
¡¡¡¡Èç¹ûÄãÔø¾Ê¹ÓÃSQL Server¿ª·¢¹ýÈκÎÈí¼þ£¬ÄÇôÄã¿Ï¶¨»áϰ¹ßÓÚʹÓÃËIJ¿·Ö±êʶ·ûÀ´²Î¿¼Ò»¸ö¶ÔÏó£º
[[[server.][database].][schema_name].]object_name
¡¡¡¡ÕýÈçÉÏÃæµÄ·½À¨ºÅËùÏÔʾµÄ£¬¸ÃÓï·¨µÄ²»Í¬²¿·Ö¿ÉÒÔ±»ºöÂÔ£¬Ö»ÒªÄãÌṩ×ã¹»µÄ±í´ïÀ´ÎÞ¶þÒ嵨±êʶÄãµÄ¶ÔÏó¼´¿É¡£ÀýÈ磬ÏÂÃæËùÓÐÕâЩ±í´ï¶¼¿ÉÄܲο¼ÏàͬµÄ¶ÔÏó£º
Server1.AdventureWorks.Production.ProductCategory
AdventureWorks.Production.ProductCategory
AdventureWorks..ProductCategory
ProductCategory
¡¡¡¡ÔÚ´ó¶àÊýÇé¿öÏ£¬Äã¿ÉÒÔ½öʹÓÃÈý²¿·ÖÃû×Ö¶øºöÂÔ·þÎñÆ÷Ãû-³ý·ÇÄãÊÇÔÚ´¦ÀíÔÚÒ»¸öÁ¬½ÓµÄ·þÎñÆ÷ÉϵĶÔÏó¡£Ä¬ÈÏÇé¿öÏ£¬ËùÓжÔÏóµÄÉÏÏÂÎͼÊDZ¾µØÊý¾Ý¿â-ÄãµÄSQLÓï¾äÔÚÆäÖÐÖ´ÐС£µ«ÊÇ£¬ÓÐЩÇé¿öÏ£¬»òÕ߸üΪ׼ȷµØËµ£¬Äã±ØÐëʹÓÃÕâÕû¸öËIJ¿·ÖÃüÃû£¨»ò³Æ×÷Ò»ÖÖÈ«³ÆÏÞ¶¨Ãû£©¡£µ«ÊÇ£¬ÔÚSQL Server 2005ÖУ¬ÕâÖÖÇé¿öÓÐËù¸Ä±ä¡£
¡¡¡¡¶þ. ÊìϤͬÒå´Ê
¡¡¡¡SQL Server 2005ÒýÈëÁËÒ»¸öͬÒå´ÊµÄ¸ÅÄÕâÊÇÒ»¸öµ¥²¿·ÖµÄÃû×Ö£¬Ëü¿ÉÒÔÌæ»»ÔÚÐí¶àSQLÓï¾äÖеÄÒ»¸öÁ½¡¢Èý»òËIJ¿·ÖµÄÃû×Ö¡£Ê¹ÓÃͬÒå´Ê¿ÉÒÔÈÃÄã¼õÉÙÊäÈ룬¶øÇÒ»¹ÄÜÌṩһ¸ö³éÏó²ãÀ´ÎªÄã±£»¤µ×²ã¶ÔÏóµÄ¸Ä±ä¡£ÎªÁËÀí½âÆä¹¤×÷ÔÀí£¬ÈÃÎÒÃÇÏÈ¿´Ò»Ï´´½¨Í¬Òå´ÊµÄÓï·¨¡£ÏÂÃæÊÇCREATE SYNONYMÓï¾äµÄÓï·¨£º
CREATE SYNONYM [schema_name.]synonym_name FOR object_name
¡¡¡¡ÔÚ´Ë£¬object_nameÊÇÒ»¸öSQL Server¶ÔÏó(È«³ÆÏÞ¶¨ÒѾ×ãÒÔÄܹ»±êʶ³öÕâ¸ö¶ÔÏó)µÄÃû×Ö£¬¶øsynonym_nameÊÇÄãÏ븳¸øËüµÄÐÂÃû×Ö¡£Èç¹ûÄã²»ÏëΪͬÒå´ÊÖ¸¶¨Ò»ÖÖģʽ£¬ÄÇôSQL ServerʹÓõ±Ç°µÄÓû§µÄĬÈÏģʽ¡£µ±Äã´´½¨Í¬Òå´Êʱ£¬ÏàÓ¦µÄ¶ÔÏó²»ÐèÒª´æÔÚ£¬ÒòΪͬÒå´ÊÊdzٰ󶨵ģºµ±Äãʵ¼ÊÉÏʹÓÃͬÒå´Êʱ£¬SQL Server½ö½ö¼ì²é»ù¶ÔÏó¡£
¡¡¡¡ÀýÈ磬ÔÚAdventureWorksʾÀýÊý¾Ý¿âÖУ¬Äã¿ÉÒÔÒÔÏÂÁз½Ê½´´½¨Ò»¸öͬÒå´Ê£º
CREATE SYNONYM ProdCat
FOR AdventureWorks.Production.ProductCategory
¡¡¡¡Ö®ºó£¬Äã¿ÉÒÔ¼ÌÐøÊ¹ÓÃÔÚSQLÓï¾äÖеÄͬÒå´ÊÁË¡£ÀýÈ磺
SELECT * from ProdCat
¡¡¡¡Í¼1ÏÔʾ³öÏàÓ¦µÄ½á¹û£º
¡¡¡¡µ±ÄãʹÓÃÍêÒ»¸öͬÒå´Êºó£¬Äã¿ÉÒÔʹÓÃDROP SYNONYMÓï¾äÀ´È¥µôËü£¬Ëü¾ßÓÐÄãÆÚÍûµÄÓï·¨¸ñʽ£º
DROP SYNONYM [schema_name.]synonym_name
Èý. ¼¸µãϸ½Ú
¡¡¡¡µ±È»£¬¶ÔÓÚͬÒå´ÊµÄʹÓÃÉÏÒ²´æÔÚһЩÏÞÖÆ¡£¶ÔÓÚ³õѧÕßÀ´Ëµ£¬Äã²»ÄÜΪÈκζ«Î÷¶¼´´½¨Ò»¸öͬÒå´Ê¡£SQL Server 2005±ÈÒÔǰÒýÈëÁ˸ü¶àµÄ¶ÔÏ󣬵«ÊÇͬÒå´Ê½öÏÞÓÚÓ¦ÓÃÓÚºËÐÄ×îÓÐÓõÄһЩ¶ÔÏóÖУº
Ïà¹ØÎĵµ£º
1. ²é¿´Êý¾Ý¿âµÄ°æ±¾
select @@version
2.²é¿´Êý¾Ý¿âËùÔÚ»úÆ÷²Ù×÷ϵͳ²ÎÊý
exec master..xp_msver
3. ²é¿´Êý¾Ý¿âÆô¶¯µÄ²ÎÊý
sp_configure
4.²é¿´Êý¾Ý¿âÆô¶¯Ê±¼ä
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
²é¿´Êý¾Ý¿â·þÎñÆ÷ÃûºÍʵÀýÃû
print ''Server Name.... ......
¸øÄã¸ö×îÏêϸµÄ°É ¿ÉÄÜÓÐÄãÒªµÄÄÚÈÝ
ËøµÄ¸ÅÊö
Ò». ΪʲôҪÒýÈëËø
¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌâ:
¶ªÊ§¸üÐÂ
A,BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐÞ¸Ä,ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û,±ÈÈ綩Ʊϵͳ
Ôà¶Á
AÓû§ÐÞ¸ÄÁËÊý¾Ý,ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý,µ«AÓû§ÒòΪijЩÔÒòÈ¡Ï ......
-- ʾÀýÒ», ʹÓÃÖ¤Êé¼ÓÃÜÊý¾Ý.
-- ½¨Á¢²âÊÔÊý¾Ý±í
CREATE TABLE tb(ID int IDENTITY (1,1),data varbinary (8000));
GO
-- ½¨Á¢Ö¤ÊéÒ», ¸ÃÖ¤ÊéʹÓÃÊý¾Ý¿âÖ÷ÃÜÔ¿À´¼ÓÃÜ
CREATE CERTIFICATE Cert_Demo1
WITH
SUBJECT = N'cert1 encryption by database master key' ,
START_DATE = ......
½ñÌì×öSQL ÓÅ»¯£¬²éÕÒÖ´Ðмƻ®Ê±£¬Ö´Ðмƻ®£¬·¢ÏÖ´ËÖ´Ðмƻ®ÓëÒÔÍùµÄ¼Æ»®ÓÐËùÇø±ð£»ÕÒ¼»¥ÁªÍø£¬ÖÕÓÚÕÒһƪÓйØÑо¿±È½ÏÉîÈëµÄÎÄÕ£»
ÔÖ´Ðмƻ®Ê¹ÓõÄÊÇË÷ÒýɨÃ裬ͻȻһÏ»áʹÓÃË÷Òý¸²¸Ç¼¼Êõ£¬Ð§ÂÊ´óÔö£»
SELECT * µÄÕæÏࣺË÷Òý¸²¸Ç(index coverage)
SELECT *µÄЧÂʺÜÔã¸âÂ𣿵±È»£¬ËùÓÐÈ˶¼ÖªµÀÕâÒ»µã£¬µ«ÊÇΪʲô ......
ÎÒÃÇÏÈ¿´ NestedLoop ºÍ MergeJoin µÄËã·¨£¨ÒÔÏÂΪÒýÓ㬼û RicCC µÄ¡¶ ͨÍùÐÔÄÜÓÅ»¯µÄÌìÌà - µØÓü JOIN ·½·¨ËµÃ÷ ¡· ):
==================================
NestedLoop:
foreach rowA in tableA where tableA.col2=?
{
search rowsB from tableB where tableB.c ......