SQLËø»úÖƺÍÊÂÎñ¸ôÀ뼶±ð£¨×ª£©
NOLOCKºÍREADPASTµÄÇø±ð¡£
1.¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2.Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ”a”ºÍ”a”¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²ÏíËøÒÔ×èÖ¹ÆäËüÊÂÎñ¶ÔÊý¾Ý±íÊý¾ÝµÄÐ޸ġ£
SELECT * from Customer
ÕâÌõÓï¾ä½«Ò»Ö±ËÀËø£¬Ö±µ½ÅÅËûËø½â³ý»òÕßËø³¬Ê±ÎªÖ¹¡£(×¢:ÉèÖÃËø³¬Ê±SET LOCK_TIMEOUT 1800)
SELECT * from Customer WITH (READPAST)
ÕâÌõÓï¾ä½«ÏÔʾaδÌύǰµÄ״̬£¬µ«²»Ëø¶¨Õû¸ö±í¡£Õâ¸öÌáʾָÃ÷Êý¾Ý¿âÒýÇæ·µ»Ø½á¹ûʱºöÂÔ¼ÓËøµÄÐлòÊý¾ÝÒ³¡£
3.Ö´ÐÐÒ»Ìõ²åÈëÓï¾ä¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'b','b'
COMMIT TRAN t
Õâ¸öʱºò£¬¼´Ê¹²½Öè1µÄÊÂÎñ»Ø¹ö£¬ÄÇôaÕâÌõÊý¾Ý½«¶ªÊ§£¬¶øb¼ÌÐø²åÈëÊý¾Ý¿âÖС£
NOLOCK
1£® Ö´ÐÐÈçÏÂÓï¾ä¡£
BEGIN TRAN ttt
SELECT * from Customer WITH (NOLOCK)
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºNOLOCK²»¼ÓÈκÎËø£¬¿ÉÒÔÔöɾ²é¸Ä¶ø²»Ëø¶¨¡£
INSERT INTO Customer SELECT 'a','b' –²»Ëø¶¨
DELETE Customer where ID=1 –²»Ëø¶¨
SELECT * from Customer –²»Ëø¶¨
UPDATE Customer SET Title='aa' WHERE ID=1 –²»Ëø¶¨
ROWLOCK
1.Ö´ÐÐÒ»Ìõ´øÐÐËøµÄ²éѯÓï¾ä¡£
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (±ØÐë)
BEGIN TRAN ttt
SELECT * from Customer WITH (ROWLOCK) WHERE ID=17
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºÔÚɾ³ýºÍ¸üÐÂÕýÔÚ²éѯµÄÊý¾Ýʱ£¬»áËø¶¨Êý¾Ý¡£¶ÔÆäËûδ²éѯµÄÐкÍÔö¼Ó£¬²éѯÊý¾ÝÎÞÓ°Ïì¡£
INSERT INTO Customer SELECT 'a','b' –²»µÈ´ý
DELETE Customer where ID=17 –µÈ´ý
DELETE Customer where ID<>17 –²»µÈ´ý
SELECT * from Customer –²»µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID=17–µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID<>17–²»µÈ´ý
HOLDLOCK£¬TABLOCKºÍTABLOCKX
1.Ö´ÐÐHOLDLOCK
BEGIN TRAN ttt
SELECT * from Customer WITH (HOLDLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
×¢£ºÆäËûÊÂÎñ¿ÉÒÔ¶ÁÈ¡±í£¬µ«²»ÄܸüÐÂɾ³ý
update Customer se
Ïà¹ØÎĵµ£º
ÔÚʹÓÃsqlserver2005´´½¨×÷ҵʱ£¬´´½¨²»ÁË£¬Ìáʾ
ÎÞ·¨½«ÀàÐÍΪ“Microsoft.SqlServer.Management.Smo.SimpleObjectKey”µÄ¶ÔÏóÇ¿ÖÆת»»ÎªÀàÐÍ“Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey”¡£ (Microsoft.SqlServer.Smo)
ÓÚÊǾÍgoogle£¬°Ù¶ÈÁËһϣ¬ºÃ¶àÎÄÕºÍÌù×Ó˵ÐèÒª° ......
SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
¡¡¡¡2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â ......
MS Sql server Êý¾Ý¿â
1.°´¶¨Òåʱ´æ´¢×´Ì¬·ÖÐÐÏÔʾ¶¨Ò壨sp_helptext´æ´¢¹ý³Ì£©£¬µ÷ÓãºExec sp_helptext '¶ÔÏóÃû'
2.ÒÔ±í¸ñÐÎʽÏÔʾ·µ»ØÏà¹Ø²ÎÊý(sys.objectsÊÓͼ),µ÷Óãºselect * from sys.objects where name='¶ÔÏóÃû'
3.×÷Ϊ½á¹û¼¯ÏÔʾ¶¨Ò壨object_definition£©£¬µ÷Óãºselect object_definition(object_id('¶ÔÏóÃû ......
IN
È·¶¨¸ø¶¨µÄÖµÊÇ·ñÓë×Ó²éѯ»òÁбíÖеÄÖµÏàÆ¥Åä¡£
EXISTS
Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐеĴæÔÚ¡£
±È½ÏʹÓà EXISTS ºÍ IN µÄ²éѯ
Õâ¸öÀý×ӱȽÏÁËÁ½¸öÓïÒåÀàËƵIJéѯ¡£µÚÒ»¸ö²éѯʹÓà EXISTS ¶øµÚ¶þ¸ö²éѯʹÓà IN¡£×¢ÒâÁ½¸ö²éѯ·µ»ØÏàͬµÄÐÅÏ¢¡£
USE pubs
GO
SELECT DISTINCT pub_name
from publishers
WHERE ......
Statement µÄ execute(sql) ·½·¨µÄ·µ»ØÖµ
true if the first result is a ResultSet object;
false if it is an update count or there are no ......