Pagination with SQL Server 2011 (Denali)

by Amol 24. February 2011 09:04

One of T-SQL enhancement in SQL Server 2011 is Pagination. You can control number of rows in a resultset. In earlier version we used to use TOP (n) keyword to control number of rows in a resultset. But TOP (n) doesn't have control from which row you want to have resulset and finally how many rows you want to return.

Microsoft SQL Server 2011 has introduce some enhancement in ORDER BY clause. In earlier versions of SQL Server we were able to use ORDER BY for having sorted resultset by specifying ascending and descending order, We were able to sort the resultset in conditional order, also ORDER BY is used in ranking function as well. Apart from all these functionalities, SQL Server 2011 has introduced OFFSET and FETCH options for ORDER BY Clause.

Let see how OFFSET and FETCH are getting used along with ORDER BY.

Specifying integer constants for OFFSET and FETCH values

The following example specifies an integer constant as the value for the OFFSET and FETCH clauses.
First query returns all rows sorted by the column DepartmentId.
The second query uses the clause OFFSET 5 ROWS, to skip the first 5 rows and return all remaining rows.
The third query uses the clause OFFSET 0 ROWS to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

USE AdventureWorks2008R2;
GO
-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

Specifying variables for OFFSET and FETCH values

The following example declares the variables @StartingRowNumber and @FetchRows and specifies these variables in the OFFSET and FETCH clauses.

USE AdventureWorks2008R2;
GO
-- Specifying variables for OFFSET and FETCH values 
DECLARE @StartingRowNumber tinyint = 1
      , @FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
    OFFSET @StartingRowNumber ROWS
    FETCH NEXT @FetchRows ROWS ONLY;

Note: Examples are used from SQL Server BOL.

Tags:

SQL Server Denali

Modeling Types

by Amol 23. February 2011 10:31

What is Modeling?

Modeling is an efficient way to represent, communicate organization needs. Modeling is an information provided to the members of an organization to understand business and its rules.

Business Modeling Types:

Business Process Modeling:

Business Process Modeling is an efficient way to represent the business processes, activities, main entities in graphical form. Below figure indicate typical busicess process model.

Process Flow Modeling:

Process Flow Modeling graphically describe the various business processes that happen in an organization and the relationships between them. Process Flow Modeling contains a diagram of several related processes often refered as Unit of Work. Process Flow Modeling is represented in the form of Arrows and Junctions. Junctions are the boxes which are used for the branching or joining operation between processes that enter or leave a junction.

Data Flow Modeling:

As name describes, Data Flow Modeling is the flow of data between various Buiness Processes or activities.

Data Modeling Types:

Following are the Data Modeling types. Click on it see more details about it.

Tags:

Data Modeling

T-SQL Get Partition Details

by Amol 22. February 2011 19:16

SELECT 

      i.object_id,

      i.name AS IndexName,

      p.partition_number,

      fg.name AS FileGroupName,

      value,

      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags:

SQL Server Partitioning | SQL Server Scripts

Category

Recent Posts

Tag cloud