真正高效的SQLSERVER分页查询(多种方案)
复制具体相关代码 具体相关代码如下:,SELECT TOP 30 * FROM ARTICLE WhERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID,复制具体相关代码 具体相关代码如下:,SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ,复制具体相关代码 具体相关代码如下:,SELECT * FROM ARTICLE w1, ( SELECT TOP 30 ID FROM ( SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY ,复制具体相关代码 具体相关代码如下:,SELECT * FROM ARTICLE w1 WhERE ID in ( SELECT top 30 ID FROM ( SELECT top 45030 ID, YEAR FROM ARTICL,复制具体相关代码 具体相关代码如下:,SELECT w2.n, w1.* FROM ARTICLE w1, ( SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DE,复制具体相关代码 具体相关代码如下:,SELECT TOP 30 * FROM ARTICLE WhERE ID NOT IN(SELECT TOP 1000 ID FROM ARTICLE ORDER BY YEAR DESC, ID ,复制具体相关代码 具体相关代码如下:,SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 1030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC),复制具体相关代码 具体相关代码如下:,SELECT * FROM ARTICLE w1, ( SELECT TOP 30 ID FROM ( SELECT TOP 1030 ID, YEAR FROM ARTICLE ORDER BY Y,复制具体相关代码 具体相关代码如下:,SELECT * FROM ARTICLE w1 WhERE ID in ( SELECT top 30 ID FROM ( SELECT top 1030 ID, YEAR FROM ARTICLE,复制具体相关代码 具体相关代码如下:,SELECT w2.n, w1.* FROM ARTICLE w1,( SELECT TOP 1030 row_number() OVER (ORDER BY YEAR DESC, ID DES,复制具体相关代码 具体相关代码如下:,if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_Page_v2]') and OBJECTPROPE
step 1：perpar database & data
复制具体相关代码 具体相关代码如下:
use master
go
Create database Inventory
go
use Inventory
go
Create table inventory
(
TitleId nvarchar(6) not null,
Quantity int not null,
primary key (TitleId,Quantity)
);
go
insert inventory values ('pc1001',100);
insert inventory values ('pc1002',200);
insert inventory values ('pc1003',300);
insert inventory values ('pc1004',400);
step 2: 创建整个SSB的基础框架.
首先创建信息类型
复制具体相关代码 具体相关代码如下:
Create Message Type [//mark/v10/types/inventory]
Create Message Type [//mark/v10/types/inventoryresponse]
创建合约
复制具体相关代码 具体相关代码如下:
Create contracts [//mark/v10/contracts/inventorycontract]
(
[//mark/v10/types/inventory] send by initiator,
[//mark/v10/types/inventoryresponse] send by target
);
创建队列与服务
复制具体相关代码 具体相关代码如下:
create queue [inventory client queue];
create service [//mark/v10/services/inventory client]
on queue [inventory client queue]
go
--create inventory service
create queue [inventory queue];
create service [//mark/v10/services/inventory]
on queue [inventory queue]
(
[//mark/v10/contracts/inventorycontract]
);
step 3：发送消息到消息队列中
复制具体相关代码 具体相关代码如下:
use Inventory
go
--send message
begin transaction
declare @dialog_id uniqueidentifier
begin dialog conversation @dialog_id
from service [//mark/v10/services/inventory client]
to service '//mark/v10/services/inventory'
on contract [//mark/v10/contracts/inventorycontract]
with encryption = off;
send on conversation @dialog_id message type [//mark/v10/types/inventory]
(
'<InventoryUpdate>
<TitleId>pc1001</TitleId>
<Quantity>102</Quantity>
</InventoryUpdate>'
);
commit transaction;
发送成功后，可以查询 inventory_queue
复制具体相关代码 具体相关代码如下:
select conversation_handle, cast(message_body as xml) as conversation_body from [Inventory queue]
这里只是查询了两列。
其中conversation_body 就是 咱们发出的信息了。
setp 4: 从 inventory queue 中获取信息并更新数据
复制具体相关代码 具体相关代码如下:
use inventory
go
begin transaction
declare @dialog_id uniqueidentifier
declare @message_body xml
declare @amount int;
declare @title nvarchar(128);
receive
@dialog_id = conversation_handle,
@message_body = message_body
from [dbo].[Inventory queue]
print 'handle is' cast(@dialog_id as nvarchar(90))
print 'message body is' cast(@message_body as nvarchar(max) )
if(@dialog_id is not null)
begin
set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int');
set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)');
print 'a:' cast(@amount as nvarchar(100))
print 'b:' @title;
Update inventory set Quantity = @amount where TitleId = @title;
end
end conversation @dialog_id;
commit transaction;
操作办法成功后再去查询inventory queue的表 犹豫里面的数据已经被receive 出来 就为空了。
数据同时被更新了
step 5: 接下来做个自动监听功能，只要有信息发送到队列，相关队列，就可以自动执行功能.
首先建立个存储过程，（把receive 中的那段具体相关代码拿过来就可以了）
复制具体相关代码 具体相关代码如下:
use Inventory
go
--create procedure to receive message and update table
create proc InventoryProc as
begin transaction
declare @dialog_id uniqueidentifier
declare @message_body xml
declare @amount int;
declare @title nvarchar(128);
waitfor(
receive
@dialog_id = conversation_handle,
@message_body = message_body
from [dbo].[Inventory queue]),timeout 5000;
print 'handle is' cast(@dialog_id as nvarchar(90))
print 'message body is' cast(@message_body as nvarchar(max) )
if(@dialog_id is not null)
begin
set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int');
set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)');
print 'a:' cast(@amount as nvarchar(100))
print 'b:' @title;
Update inventory set Quantity = @amount where TitleId = @title;
end
end conversation @dialog_id;
commit transaction;
go
队列监听
复制具体相关代码 具体相关代码如下:
