Welcome to SQLServer MVP Blog: August 2012

24.8.12

The OFFSET-FETCH Filter in select clause

This is one of the fantastic feature introduced in SQL Server.

As we all know about "Top" Clause. It will filter the data in a specific order.

Select Top (10) * from Venkat_Table

The above query will fetch top 10 records from the table.

We don't have any provision to avoid a set of data and fetch the next level of data. Here is the provision and it can be done using Offset-Fetch Filter option.

Here is the query format,

create table venkat_Table( id int identity(1,1), val varchar(100))
go
insert into venkat_Table values
('a')
go 25
go
insert into venkat_Table values
('b')
go 25
select * from venkat_Table
GO
select * from venkat_Table
offset 25 rows fetch next 10 rows only

The above query will fetch 10 rows after leaving 25 rows.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Select Query order in SQL Server

Writing my articles after a very long break. Tied up with lot of activities with my company KaaShiv InfoTech. Met lot of people, new environment, New friends, New Technological ideas.... lots of new things happening around me.

I feel like, I've missed out something even I was tied up with lot of activities that's nothing but my technological updates. I've decided to start my articles in this blog Hope it will go a long way from now. Let's see.

As we are familiar with "Select" clause. Here is the logical order the query will be processed in the Select clause

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Example Query:

FROM Venkat_EmployeeTable
WHERE custid = 1071
GROUP BY empName, YEAR(orderdate)
HAVING COUNT(*) > 10
SELECT empiName YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
ORDER BY empid, orderyear

Cheers,
Venkatesan Prabu .J