Two tips for segmentation

Two tips for segmentation

There are two kinds of tips, one is direct through the T-SQL, the other is through the store procedure, in this post to share with you:
T-SQL:
Assume that northwind has a customer table. You need to retrieve 41 ~ 50 yuan of memory, how to do the T-SQL statement?

Select top 10 mermerid, companyName, contactname, country from customers where customerid not in
(Select Top 40 mermerid from customers order by country, customerid)
Order by country, customerid

Store procedure:
From msdn magazine, it is the wisdom of others
Create procedure northwind_orderspaged
(
@ Pageindex int,
@ Pagesize int
)
As
Begin
Declare @ pagelowerbound int
Declare @ pageupperbound int
Declare @ rowstoreturn int

-- First set the rowcount
Set @ rowstoreturn = @ pagesize * (@ pageindex + 1)
Set rowcount @ rowstoreturn

-- Set the page Bounds
Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1

-- Create a temp table to store the select results
Create Table # pageindex
(
Indexid int identity (1, 1) not null,
Orderid int
)

-- Insert into the temp table
Insert into # pageindex (orderid)
Select
Orderid
From
Orders
Order
Orderid DESC

-- Return total count
Select count (orderid) from orders

-- Return paged results
Select
O .*
From
Orders O,
# Pageindex
Where
O. orderid = pageindex. orderid and
Pageindex. indexid> @ pagelowerbound and
Pageindex. indexid <@ pageupperbound
Order
Pageindex. indexid

End