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 'ºÄʱ(ºÁÃë)'
----------------------------------------------------------------------------------------------------------
²âÊÔ·½·¨¼°½á¹û(È¡
Ïà¹ØÎĵµ£º
Ò»¡¢»ù´¡
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¡¢Ëµ ......
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
......
--Óû§Óë½ÇÉ«¹ØÏµ
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)
......
1.
ÓÃÒ»ÌõSQL
Óï¾ä ²éѯ³öÿÃſζ¼´óÓÚ80
·ÖµÄѧÉúÐÕÃû
name kecheng fenshu
ÕÅÈý
ÓïÎÄ 81
ÕÅÈý
Êýѧ 75
ÀîËÄ
ÓïÎÄ &nbs ......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃæ£¬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
¡¡¡¡Æäʵ£¬ÎÒÃǵĺºÓï×Öµäµ ......