SQL Server²éѯËٶȻºÂý½â¾ö°ì·¨(2)
ÔÚT-sqlµÄд·¨ÉÏÓкܴóµÄ½²¾¿£¬ÏÂÃæÁгö³£¼ûµÄÒªµã£ºÊ×ÏÈ£¬DBMS´¦Àí²éѯ¼Æ»®µÄ¹ý³ÌÊÇÕâÑùµÄ£º
1¡¢²éѯÓï¾äµÄ´Ê·¨¡¢Óï·¨¼ì²é
2¡¢½«Óï¾äÌá½»¸øDBMSµÄ²éѯÓÅ»¯Æ÷
3¡¢ÓÅ»¯Æ÷×ö´úÊýÓÅ»¯ºÍ´æȡ·¾¶µÄÓÅ»¯
4¡¢ÓÉÔ¤±àÒëÄ£¿éÉú³É²éѯ¹æ»®
5¡¢È»ºóÔÚºÏÊʵÄʱ¼äÌá½»¸øϵͳ´¦ÀíÖ´ÐÐ
6¡¢×îºó½«Ö´Ðнá¹û·µ»Ø¸øÓû§¡£
Æä´Î£¬¿´Ò»ÏÂSQL SERVERµÄÊý¾Ý´æ·ÅµÄ½á¹¹£ºÒ»¸öÒ³ÃæµÄ´óСΪ8K(8060)×Ö½Ú£¬8¸öÒ³ÃæΪһ¸öÅÌÇø£¬°´ÕÕBÊ÷´æ·Å¡£
12¡¢CommitºÍrollbackµÄÇø±ðRollback£º»Ø¹öËùÓеÄÊÂÎï¡£Commit£ºÌá½»µ±Ç°µÄÊÂÎûÓбØÒªÔÚ¶¯Ì¬SQLÀïдÊÂÎÈç¹ûҪдÇëдÔÚÍâÃæÈ磺begin tran exec(@s) commit trans»òÕß½«¶¯Ì¬SQLд³Éº¯Êý»òÕß´æ´¢¹ý³Ì¡£
13¡¢ÔÚ²éѯSelectÓï¾äÖÐÓÃWhere×Ö¾äÏÞÖÆ·µ»ØµÄÐÐÊý,±ÜÃâ±íɨÃè,Èç¹û·µ»Ø²»±ØÒªµÄÊý¾Ý£¬ÀË·ÑÁË·þÎñÆ÷µÄI/O×ÊÔ´£¬¼ÓÖØÁËÍøÂçµÄ¸ºµ£½µµÍÐÔÄÜ¡£Èç¹û±íºÜ´ó£¬ÔÚ±íɨÃèµÄÆڼ佫±íËøס£¬½ûÖ¹ÆäËûµÄÁª½Ó·ÃÎʱí,ºó¹ûÑÏÖØ¡£
14¡¢SQLµÄ×¢ÊÍÉêÃ÷¶ÔÖ´ÐÐûÓÐÈκÎÓ°Ïì
15¡¢¾¡¿ÉÄܲ»Ê¹Óùâ±ê£¬ËüÕ¼ÓôóÁ¿µÄ×ÊÔ´¡£Èç¹ûÐèÒªrow-by-rowµØÖ´ÐУ¬¾¡Á¿²ÉÓ÷ǹâ±ê¼¼Êõ£¬È磺ÔÚ¿Í»§¶ËÑ»·£¬ÓÃÁÙʱ±í£¬Table±äÁ¿£¬ÓÃ×Ó²éѯ£¬ÓÃCaseÓï¾äµÈµÈ¡£
Óαê¿ÉÒÔ°´ÕÕËüËùÖ§³ÖµÄÌáÈ¡Ñ¡Ïî½øÐзÖÀֻࣺ½ø±ØÐë°´ÕÕ´ÓµÚÒ»Ðе½×îºóÒ»ÐеÄ˳ÐòÌáÈ¡ÐС£FETCH NEXTÊÇΨһÔÊÐíµÄÌáÈ¡²Ù×÷£¬Ò²ÊÇĬÈÏ·½Ê½¡£¿É¹ö¶¯ÐÔ¿ÉÒÔÔÚÓαêÖÐÈκεط½Ëæ»úÌáÈ¡ÈÎÒâÐС£ÓαêµÄ¼¼ÊõÔÚSQL2000ϱäµÃ¹¦ÄܺÜÇ¿´ó£¬ËûµÄÄ¿µÄÊÇÖ§³ÖÑ»·¡£
ÓÐËĸö²¢·¢Ñ¡Ïî READ_ONLY£º²»ÔÊÐíͨ¹ýÓα궨λ¸üÐÂ(Update)£¬ÇÒÔÚ×é³É½á¹û¼¯µÄÐÐÖÐûÓÐËø¡£
OPTIMISTIC WITH valueS£ºÀÖ¹Û²¢·¢¿ØÖÆÊÇÊÂÎñ¿ØÖÆÀíÂÛµÄÒ»¸ö±ê×¼²¿·Ö¡£ÀÖ¹Û²¢·¢¿ØÖÆÓÃÓÚÕâÑùµÄÇéÐΣ¬¼´ÔÚ´ò¿ªÓα꼰¸üÐÂÐеļä¸ôÖУ¬Ö»ÓкÜСµÄ»ú»áÈõڶþ¸öÓû§¸üÐÂijһÐС£µ±Ä³¸öÓαêÒÔ´ËÑ¡Ïî´ò¿ªÊ±£¬Ã»ÓÐËø¿ØÖÆÆäÖеÄÐУ¬Õ⽫ÓÐÖúÓÚ×î´ó»¯Æä´¦ÀíÄÜÁ¦¡£Èç¹ûÓû§ÊÔͼÐÞ¸ÄijһÐУ¬Ôò´ËÐеĵ±Ç°Öµ»áÓë×îºóÒ»´ÎÌáÈ¡´ËÐÐʱ»ñÈ¡µÄÖµ½øÐбȽϡ£Èç¹ûÈκÎÖµ·¢Éú¸Ä±ä£¬Ôò·þÎñÆ÷¾Í»áÖªµÀÆäËûÈËÒѸüÐÂÁË´ËÐУ¬²¢»á·µ»ØÒ»¸ö´íÎó¡£Èç¹ûÖµÊÇÒ»ÑùµÄ£¬·þÎñÆ÷¾ÍÖ´ÐÐÐ޸ġ£
Ñ¡ÔñÕâ¸ö²¢·¢Ñ¡ÏîOPTIMISTIC WITH ROW VERSIONING:´ËÀÖ¹Û²¢·¢¿ØÖÆÑ¡Ïî»ùÓÚÐа汾¿ØÖÆ¡£Ê¹ÓÃÐа汾¿ØÖÆ£¬ÆäÖеıí±ØÐë¾ßÓÐijÖÖ°æ±¾±êʶ·û£¬·þÎñÆ÷¿ÉÓÃËüÀ´È·¶¨¸ÃÐÐÔÚ¶ÁÈëÓαêºóÊÇ·ñÓÐËù¸ü¸Ä¡£ÔÚSQL ServerÖУ¬Õâ¸öÐÔÄÜÓÉtimestampÊý¾ÝÀàÐÍÌṩ£¬ËüÊÇÒ»¸ö¶þ½øÖÆÊý×Ö£¬±íʾÊý¾Ý¿âÖиü¸ÄµÄÏà¶Ô˳Ðò¡£
ÿ¸öÊ
Ïà¹ØÎĵµ£º
Óï¾äÐÎʽ£º¡¡ SELECTTOP10*
fromTestTable
WHERE(ID>
¡¡¡¡¡¡¡¡¡¡(SELECTMAX(id)
¡¡¡¡¡¡¡¡from(SELECTTOP20id
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡fromTestTable
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ORDERBYid)AST))
ORDERBYID
SELECTTOPÒ³´óС*
fromTestTable
WHERE(ID>
¡¡¡¡¡¡¡¡¡¡(SELECTMAX(id)
¡¡¡¡¡¡¡¡from(SELECTTOPÒ³´óС*Ò³Êýid
¡¡¡¡¡ ......
1.Êý¾ÝµÄ·Ö²¼Skip()ºÍtake()
Skip(100)´ú±íÌø¹ý100Ðкó.´ÓµÚ101ÐпªÊ¼·ÖÒ³;
take(10)±íʾ¶àÉÙÐзÖÒ³;
Àý:
var result=from pdt in db.Product
select pdt;
this.dataview.DataSource=result.Skip(10).take(10);
......
½¨±í£º
CREATE TABLE [DB.dbo].tableName
(Stud_id int CONSTRAINT constraintName1 not null primary key,
Name nvarchar(5) not null,
Birthday datetime,
Gender nchar(1),
Telcode char(12),
Zipcode char(6) CONSTRAINT constraintName2 CHECK(zipcode like [ ......
BEGIN TRANSACTION--¿ªÊ¼ÊÂÎñ
DECLARE @errorSun INT --¶¨Òå´íÎó¼ÆÊýÆ÷
SET @errorSun=0 --û´íΪ0
UPDATE a SET id=232 WHERE a=1 --ÊÂÎñ²Ù×÷SQLÓï¾ä
SET @errorSun=@errorSun+@@ERROR --ÀÛ¼ÆÊÇ·ñÓдí
UPDATE aa SET id=2 WHERE a=1 --ÊÂÎñ²Ù×÷SQLÓï¾ä
SET @errorSun=@errorSun+@@ERROR --ÀÛ¼ÆÊÇ·ñÓдí
I ......
ÈçºÎÔÚSQLÖÐÆôÓÃÈ«ÎļìË÷¹¦ÄÜ£¿
¡¡¡¡--------------------------------------------------------------------------------
¡¡¡¡È«ÎÄË÷ÒýµÄÒ»¸öÀý×Ó£¬ÔÚ²éѯ·ÖÎöÆ÷ÖÐʹÓãº
¡¡¡¡use pubs
¡¡¡¡go
¡¡¡¡--´ò¿ªÊý¾Ý¿âÈ«ÎÄË÷ÒýµÄÖ§³Ö
¡¡¡¡execute sp_fulltext_database 'enable'
¡¡¡¡go
¡¡¡¡--½¨Á¢È«ÎÄĿ¼ft_titles
¡¡¡ ......