Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Sql Server 2005 row_number()·ÖÒ³ÐÔÄܲâÊÔ

  ÏÖÔÚ·ÖÒ³·½·¨´ó¶à¼¯ÖÐÔÚselect top/not in/Óαê/row_number£¬¶øselect top·ÖÒ³(ÔÚÕâ»ù´¡ÉÏ»¹Óжþ·Ö·¨)·½·¨Ëƺõ¸üÊÜ´ó¼Ò»¶Ó­£¬ÕâÆªÎÄÕ²¢²»´òËãÈ¥ÌÖÂÛÊÇ·ñͨÓõÄÎÊÌ⣬±¾×ÅʵÓõÄÔ­Ôò£¬»¨ÁËһЩʱ¼äÈ¥²âÊÔrow_number()·ÖÒ³µÄÐÔÄÜ£¬¸Ð¾õ²¢²»ÏñÒ»²¿·ÖÈËËù˵µÄÄÇô¼¦Àߣ¬ÓÉÓÚ½Ó´¥Èí¼þ¿ª·¢²ÅÊ®¸öÔ£¬·½·½ÃæÃæµÄ¶«Î÷¶¼ÒªÑ§£¬¾­ÑéʵÔÚÓÐÏÞ,²»×ãÖ®´¦ÇëÔ­Á£¬²âÊÔÈçÏ£º
ƽ̨Óë»·¾³£º
CPU:AMD 1150 2G µ¥ºË
Äڴ棺1G(ϵͳÕý³£Æô¶¯ºóÔ¼Õ¼300M¿Õ¼ä)
Ó²ÅÌ£ºSATA 160G 8M Cache
ϵͳ£ºwindows 2003 ent+Sql Server 2005 sp2
Êý¾Ý£º¹²500ÍòÌõ
-------------------------------------------------------------------
²âÊÔÊý¾Ý£º
create table test_table
(
id   int identity(1,1) primary key not null,
cid   int  not null,
userName  varchar(50) null,
userPwd   varchar(50) null,
createTime datetime null
)
---------------------------------------------------------------------
²åÈë¼Ç¼(cid·Ö±ð²åÈë1,2,3,4,»úÆ÷ʵÔÚÌ«Âý,×ܹ²Ö»²åÈë500ÍòÌõ)£º
declare @count int
set @count=1
while @count<=1000000
begin
insert into test_table(cid,userName,userPwd,createTime) values(2,'admin','admin888',getdate())
set @count=@count+1
end
-------------------------------------------------------------------------------------------------------
·ÖÒ³²âÊÔ´úÂ룺
ÕâÀï²ÉÓÃrow_numberµÄÁ½ÖÖ·ÖÒ³·½Ê½£º·Ö±ðÓÃtopºÍbetween¹ýÂË
/*row_number() ²éѯ·½·¨Ò»*/
declare @tdiff datetime
set @tdiff=getdate()
select top 20 * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber>120000
select datediff(ms,@tdiff,getdate()) as 'ºÄʱ(ºÁÃë)'
/*row_number() ²éѯ·½·¨¶þ*/
declare @tdiff datetime 
set @tdiff=getdate()
select * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber between 120000 and 120200
select datediff(ms,@tdiff,getdate()) as 'ºÄʱ(ºÁÃë)'
----------------------------------------------------------------------------------------------------------
²âÊÔ·½·¨¼°½á¹û(È¡


Ïà¹ØÎĵµ£º

SQLÓï¾äÊÕ¼¯

Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......

SQL Server£º²é¿´Êý¾Ý¿âÓû§È¨ÏÞ£¨SQL 2005£©

1. ²é¿´ SQL 2005 Óû§ËùÊôÊý¾Ý¿â½ÇÉ«
use yourdb
go
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
......

SQL SERVER ²é¿´Óû§È¨ÏÞ

--Óû§Óë½ÇÉ«¹ØÏµ
select a.uid as uid,a.status as uStatus,a.name as uName,
  b.uid as rId,b.status as rStatus,b.name as rName
from sysusers a inner join sysusers b on a.gid = b.uid
where a.issqlrole = 0 and a.isapprole = 0 and a.hasdbaccess = 1 and (b.issqlrole = 1 or b.isapprole = 1)
......

Sql³£¼ûÃæÊÔÌâ ÊÜÓÃÁË

1.
ÓÃÒ»ÌõSQL
Óï¾ä ²éѯ³öÿÃſζ¼´óÓÚ80
·ÖµÄѧÉúÐÕÃû
name   kecheng   fenshu
ÕÅÈý    
ÓïÎÄ       81
ÕÅÈý    
Êýѧ       75
ÀîËÄ    
ÓïÎÄ &nbs ......

SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨Ò»£©

Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃæ£¬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
¡¡¡¡Æäʵ£¬ÎÒÃǵĺºÓï×Öµäµ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ