Paging with OFFSET and FETCH in SQL 2012

Paging with OFFSET and FETCH

 

Created a sample table with few records. @PAGESIZE variable gives the no of records needed in each page, @STARTINGROW variable gives the starting record count, @SORTCONDITION gives the sorting attribute and order (functional requirement).

 

DECLARE @PAGESIZE INT

DECLARE @STARTINGROW INT

DECLARE @SORTCONDITION VARCHAR(250)

 

SET @PAGESIZE = 20

SET @STARTINGROW = 10 -1

SET @SORTCONDITION = 'EMPNO ACS'

 

SELECT        R1.ID 

,             R1.EMPNO 

,             R1.EMPNAME

,             R1.EMPADD1

FROM   SAMPLEFETCH R1       (NOLOCK)

ORDER BY

              CASE   @SORTCONDITION

              WHEN   'EMPNO ACS' THEN EMPNO

              WHEN   'EMPNAME ASC' THEN EMPNAME

              WHEN   'EMPADD1 ASC' THEN EMPADD1

              END ASC,

              CASE   @SORTCONDITION

              WHEN   'EMPNO DESC' THEN EMPNO

              WHEN   'EMPNAME DESC' THEN EMPNAME

              WHEN   'EMPADD1 DESC' THEN EMPADD1

              END DESC

OFFSET @STARTINGROW ROWS

FETCH NEXT @PAGESIZE ROWS ONLY

Query Execution Steps

Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree

Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.

Execution time is total time spent during execution of compiled plan.

It is really a good idea to split this kind of stored procedure to sub procedures that would help to reduce the parse and compile time.

--Clear the cache and buffer

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

--How much time a query is taking can be best measured by setting STATISTICS TIME ON

 

SET STATISTICS TIME ON

 

--Execute the Stored procedure

 

EXEC ExecutionTimeTest 'Supervisor', 123037

 

SET STATISTICS TIME  OFF

SQL Server parse and compile time:

   CPU time = 281 ms, elapsed time = 365 ms.

 

SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 209 ms.

 

SQL Server Execution Times:

  CPU time = 421 ms,  elapsed time = 574 ms.

Must Know-Database Design Models & Terminology

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

Conceptual- Involves entity relationship model at high level. Involves relationship defination w.r.t entity. This may constitutes high level entity boxes with rough relationship and missing cardinality and modality.

Logical Model- This contains ER diagram with details of cardinality and modality. Logical relationship with key entities member defined. Can layout normalization and denormalization as per business and application style specifications.

Physical - It replicates logical and it is phyical table and overall database defination in implementation mode.

Some Key Technology Jargaons

• Soft Delete
• Hard Delete
• Back Door Enteries
• Audit Trail
• Master Table
• Transaction Table
• DDL
• DML
• SandBox
• Table Lock
• Primary Key
• Refrencial Integrity
• Foreign Key
• Secondary Key
• Cardinality
• Modality
• Commit RollBack
• Transactions
• Records
• Fields
• Indexing
• Clustered vs Non clustered
• Execution plan
• Db Advisor
• Db Examiner
• SQL Lint
• Database Schema
• Replication
• Cursor
• Transaction Log
• Transaction File System
• Temp DB Shrink
• Partision Table
• Scalar Function
• Aggregate Function
• Joins-Set based query
• ACID
• Atomicity
• Link Server
• RDMS-Relational DB Management System
• Blob- Binary Large object

 

MDTC Transactionscope vs SqlTransaction

Microsoft distributed Transaction Coordinator is inbuilt transaction handling engine where it take care of integrity and execution of transaction scope. Now if we have same database operation then handling transaction integrity within database is the best option but if the transaction and business operation span across multiple database and data source then Transaction scope of MDTC is the best option to do so.

SQLTransaction ADO.net is for database Alternative can be done smartly in stored procedures itself with Begin Trans, Commit and rollback

TransactionScope is Operating system based.
http://codingcramp.blogspot.se/2009/06/how-to-setup-and-use-transactionscope.html

SQL Transaction Example
public void DoWorkSqlTransactions()
{
using (SqlConnection con1 = new SqlConnection("my connection string"))
using (SqlConnection con2 = new SqlConnection("my second connection string"))
{
try
{
con1.Open();
con2.Open();
SqlTransaction tran1 = con1.BeginTransaction();
SqlTransaction tran2 = con2.BeginTransaction();

try
{
SqlCommand cmd1 = new SqlCommand("update ...", con1, tran1);
cmd1.ExecuteNonQuery();

// Don't want select in transaction
cmd1 = new SqlCommand("select ...", con1);

SqlCommand cmd2 = new SqlCommand("insert ...", con2, tran2);
cmd2.ExecuteNonQuery();
tran1.Save("savepoint");
tran2.Save("savepoint");
tran1.Commit();
tran2.Commit();
}
catch (Exception)
{
tran1.Rollback();
tran2.Rollback();
}
}
catch (Exception)
{
// error handling for connection failure
}
finally
{
con1.Close();
con2.Close();
}
}
}
Transaction Scope
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
{}
Just sorted.