SQL SERVERµ¥Ò³Êý¾Ý´æ´¢ÐÐÊý¼ÆËã
Ó¦ÓÃʾÀý£º
-- ´´½¨2¸ö²âÊÔ±í
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- Ìî³äÊý¾Ý
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
insert Table_2020(Data) values('')
select @i = @i + 1
end
go
²é¿´Ã¿¸ö±íËùÕ¼ÓõĿռ䣬½á¹û£º
Table_2020 ±íÊý¾ÝÕ¼ÁË 160kb £¬Table_2019 ±íÊý¾ÝÕ¼ÁË 80 kb
½âÊÍ£º
SQL ServerÒ³¶¨Ò壺
ÔÚ SQL Server ÖУ¬Ò³µÄ´óСΪ 8 KB¡£ÕâÒâζ×Å SQL Server Êý¾Ý¿âÖÐÿ MB ÓÐ 128 Ò³¡£Ã¿Ò³µÄ¿ªÍ·ÊÇ 96 ×ֽڵıêÍ·£¬ÓÃÓÚ´æ´¢ÓйØÒ³µÄϵͳÐÅÏ¢
ÔÚ SQL Server 2005 ÖУ¬Ðв»ÄÜ¿çÒ³£¬µ«ÊÇÐеIJ¿·Ö¿ÉÒÔÒÆ³öÐÐËùÔÚµÄÒ³£¬Òò´ËÐÐʵ¼Ê¿ÉÄܷdz£´ó¡£Ò³µÄµ¥¸öÐÐÖеÄ×î´óÊý¾ÝÁ¿ºÍ¿ªÏúÊÇ 8,060 ×Ö½Ú (8 KB)¡£
ÔÚÊý¾ÝÒ³ÉÏ£¬Êý¾ÝÐнô½Ó×űêÍ·°´Ë³Ðò·ÅÖá£Ò³µÄĩβÊÇÐÐÆ«ÒÆ±í£¬¶ÔÓÚÒ³ÖеÄÿһÐУ¬Ã¿¸öÐÐÆ«ÒÆ±í¶¼°üº¬Ò»¸öÌõÄ¿¡£Ã¿¸öÌõÄ¿¼Ç¼¶ÔÓ¦ÐеĵÚÒ»¸ö×Ö½ÚÓëÒ³Ê׵ľàÀë¡£ÐÐÆ«ÒƱíÖеÄÌõÄ¿µÄ˳ÐòÓëÒ³ÖÐÐеÄ˳ÐòÏà·´¡£
¼ÆË㣺
¼ÆËã×ܵÄÐдóС£º
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
¹«Ê½ÖеÄÖµ 4 ÊÇÊý¾ÝÐеÄÐбêÌ⿪Ïú¡£
ÓÉÓÚncharÊǹ̶¨³¤¶È£¬ËùÒÔVariable_Data_Size=0
±£ÁôÐÐÖгÆÎª Null λͼµÄ²¿·ÖÒÔ¹ÜÀíÁеÄΪ¿ÕÐÔ¡£¼ÆËãÆä´óС£º
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
ÓÉÓÚÖ»ÓÐÒ»ÁУ¬ËùÒÔNull_Bitmap = 3
ÓÉÒÔÉÏÔÔò¿ÉÒÔ¼ÆË㣺
nchar(2019): 2019×2+0+3+4=4045
nchar(2020): 2020×2+0+3+4=4047
΢Èí¸ø³öµÄÿҳÈÝÄÉÐÐÊý¼ÆË㹫ʽ£º
¼ÆËãÿҳµÄÐÐÊý£¨Ã¿Ò³ÓÐ 8096 ¸ö¿ÉÓÃ×Ö½Ú£©£º
Rows_Per_Page = 8096 / (Row_Size + 2)
ÒòΪÐв»¿çÒ³£¬ËùÒÔÿҳµÄÐÐÊýÓ¦ÏòÏÂÉáÈëµ½×î½Ó½üµÄÕûÊý¡£¹«Ê½ÖеÄÊýÖµ 2 ÊǼÆËãÐÐÊýʱÒýÈëµÄÐдóСÓàÁ¿¡£
nchar(2019): 8096/(4045+2)=2.000494193229
nchar(2020): 8096/(4047+2£©=1.999506050876
¾ÝÒÔÉϼÆË㣬¿ÉÒԵóö£¬Ò»Ò³¿ÉÒÔ´æ´¢Ö»ÓÐÒ»ÁÐnchar£¨2019£©Êý¾ÝµÄÐÐÊýΪ2ÐУ¬¶ø´æ´¢Ö»ÓÐÒ»ÁÐnchar£¨2020£©Êý¾ÝµÄÐÐÊýΪ1ÐС£
Ïà¹ØÎĵµ£º
ÔÚµ÷Ó÷þÎñÆ÷ºóÒª¼ÌÐøÔÚ·þÎñÆ÷Öб£ÁôÊý¾Ýͨ³£ºÜÄÑ£¬Äã¿ÉÒÔÓÃSQL ServerÀ´½øÐлỰ״̬¹ÜÀí(Óû§»á»°Êý¾Ý)¡£ÒÔϽÌÄãÔõÑùÓÃËüÀ´½øÐйÜÀí¡£
¡¡¡¡ÔÚÒ³Ãæ¿ª·¢ÖУ¬ÔÚ·þÎñÆ÷µ÷ÓÃÀï±£ÁôÊý¾ÝÊÇÒ»¸ö³£¼ûµÄ²»ºÃ½â¾öµÄÄÑÌâ¡£ÄãÒ²ÐíÐèҪΪÄãµÄÓ¦ÓóÌÐò»òÌØÊâÓû§µÄ»á»°±£´æÐÅÏ¢¡£´æ´¢ÕâÑùµÄÊý¾Ý±»³ÆÎª×´Ì¬¹ÜÀí£¬ASP.NETΪÄãÌṩ ......
1.°´ÐÕÊϱʻÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.·ÖÒ³SQLÓï¾ä
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøÎ»ÖÃ
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§± ......
ÔÚSQL UPDATEÓï¾äÖУ¬¶Ô»»Á½¸ö±äÁ¿µÄÖµ£¬²»ÐèÒªÁÙʱ±äÁ¿¡£=ÓÒ²àµÄÖµÔÚÕû¸öUPDATEÓï¾äÖж¼ÊÇÒ»Öµģ»ËùÓеĸüÐÂͬʱ·¢Éú£¬¶ø²»ÊÇÒ»¸ö½Ó×ÅÒ»¸ö·¢Éú¡£
ÀýÈ磺
UPDATE offenceTeam
SET goalShooter=wingAttack,
& ......
ÎÊÌâÃèÊö:
¡¡¡¡Îª¹ÜÀí¸ÚλҵÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í:
¡¡¡¡S (S#,SN,SD,SA) S#,SN,SD,SA ·Ö±ð´ú±íѧºÅ¡¢Ñ§Ô±ÐÕÃû¡¢ËùÊôµ¥Î»¡¢Ñ§Ô±ÄêÁä
¡¡¡¡C (C#,CN ) C#,CN ·Ö±ð´ú±í¿Î³Ì±àºÅ¡¢¿Î³ÌÃû³Æ
¡¡¡¡SC ( S#,C#,G ) S#,C#,G ·Ö±ð´ú±íѧºÅ¡¢ËùÑ¡Ð޵Ŀγ̱àºÅ¡¢Ñ§Ï°³É¼¨
¡¡¡¡1. ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆÎª&rsquo ......
ÔÚÎÒÃÇ×öÊý¾Ý¿â³ÌÐò¿ª·¢µÄʱºò£¬¾³£»áÓöµ½ÕâÖÖÇé¿ö£ºÐèÒª½«Ò»¸öÊý¾Ý¿â·þÎñÆ÷ÖеÄÊý¾Ýµ¼Èëµ½ÁíÒ»¸öÊý¾Ý¿â·þÎñÆ÷µÄ±íÖС£Í¨³£ÎÒÃÇ»áʹÓÃÕâÖÖ·½·¨£ºÏȰÑÒ»¸öÊý¾Ý¿âÖеÄÊý¾ÝÈ¡³öÀ´·Åµ½Ä³³ö£¬È»ºóÔÙ°ÑÕâЩÊý¾ÝÒ»ÌõÌõ²åÈ뵽ĿµÄÊý¾Ý¿âÖУ¬ÕâÖÖ·½·¨Ð§Âʽϵͣ¬Ð´Æð³ÌÐòÀ´Ò²ºÜ·±Ëö£¬ÈÝÒ׳ö´í¡£ÁíÍâÒ»ÖÖ·½·¨ÊÇʹÓÃbcp»òBULK IN ......