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ÐС£
Ïà¹ØÎĵµ£º
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.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§± ......
DECLARE @dt datetime
SET @dt=GETDATE()
DECLARE @number int
SET @number=3
--1£®Ö¸¶¨ÈÕÆÚ¸ÃÄêµÄµÚÒ»Ìì»ò×îºóÒ»Ìì
--A. ÄêµÄµÚÒ»Ìì
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. ÄêµÄ×îºóÒ»Ìì
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2£®Ö¸¶¨ÈÕÆÚËùÔÚ¼¾¶ÈµÄµÚÒ»Ìì»ò×îºóÒ»Ìì
--A. ¼¾¶ÈµÄµÚÒ»Ìì
SELECT CON ......
ÔÚSQL UPDATEÓï¾äÖУ¬¶Ô»»Á½¸ö±äÁ¿µÄÖµ£¬²»ÐèÒªÁÙʱ±äÁ¿¡£=ÓÒ²àµÄÖµÔÚÕû¸öUPDATEÓï¾äÖж¼ÊÇÒ»Öµģ»ËùÓеĸüÐÂͬʱ·¢Éú£¬¶ø²»ÊÇÒ»¸ö½Ó×ÅÒ»¸ö·¢Éú¡£
ÀýÈ磺
UPDATE offenceTeam
SET goalShooter=wingAttack,
& ......
1.¼ì²éÄãÊÇ·ñÏÞÖÆÁËÎļþÔö³¤:
ÆóÒµ¹ÜÀíÆ÷--ÓÒ¼üÄãµÄÊý¾Ý¿â--ÊôÐÔ--Êý¾ÝÎļþ--¿´¿´ÓÐûÓÐÉèÖÃÎļþµÄ×î´óÖµ
2.¼ì²éÄãµÄ´ÅÅÌ·ÖÇø¸ñʽ,Èç¹û²»ÊÇNTFS·ÖÇø,Ôò»áÓÐÏÞÖÆ,½«´ÅÅÌ·ÖÇø¸ñʽ¸Ä³ÉNTFS
&n ......
×ö¿ª·¢µÄ¹ý³ÌÖо³£Óõ½Êý¾Ý¿âÔ¶³ÌÁ¬½ÓµÄÎÊÌ⣬ÓÐʱºòŪÁË°ëÌìÒ²½â¾ö²»ÁË£¬ÕâÀï¸ù¾ÝÎÒ×Ô¼ºµÄÒ»µã¾Àú¶ÔSQL ServerÔ¶³ÌÁ¬½ÓÎÊÌâ×öÒ»×ܼơ£
Ê×ÏÈÕâÀïÖ÷Ҫ˵µÄÊÇSQL Server 2005²»ÊÇ2000£¬ÒòΪ2000ÓÐһЩСµÄÀýÍ⣬ÀýÈç°²×°sp4²¹¶¡µÈ£¬ÕâÀï²»ÔÙÌÖÂÛ¡£ÊÂʵÉÏÎÒ ......