How to achieve UNION ALL and UNION in LINQ

In one of my application, I came to a situation where I had to use UNION ALL like functionality for two enumerable list by using LINQ. So in today’s post we’ll learn about how we can achieve UNION and UNION ALL like functionality using LINQ. You can do a UNION operation using the Union method in LINQ and UNION ALL operation using the Concat method.

Let’s see this practically –

First we will learn about Union method.

static void Main(string[] args)
{
	var productsFromMicrosoft = new List<string>
	{
		"Visual Studio",
		"MS Office",
		"Xbox",
		"Mail Service"  // <-- This product is in both list
	};

	var productsFromGoogle = new List<string>
	{
		"Google Chrome",
		"Mail Service",  // <-- This product is in both list
		"Blogger"
	};

	var unionList = productsFromMicrosoft.Union(productsFromGoogle).ToList();

	Console.WriteLine(unionList);
}

UnionList

So as expected, UNION method excludes duplicates from the return set.

Now we will see Concat operator.

static void Main(string[] args)
{
	var productsFromMicrosoft = new List<string>
	{
		"Visual Studio",
		"MS Office",
		"Xbox",
		"Mail Service"  // <-- This product is in both list
	};

	var productsFromGoogle = new new List<string>
	{
		"Google Chrome",
		"Mail Service",  // <-- This product is in both list
		"Blogger"
	};

	var unionAllList = productsFromMicrosoft.Concat(productsFromGoogle).ToList();

	foreach (var product in unionAllList)
	{
		Console.WriteLine(product);
	}
}

UnionAllList

The Concat method differs from the Union method because the Concat() method returns all the original elements in the input sequences. The Union method returns only unique elements.

Reference Link:

That’s it folks. Hope you like this post.

Advertisements

NOWAIT hint in SQL Server

Today, I was reading about Table Hint in MSDN for other reason and came across this “NOWAIT” hint. In today’s post we will learn about NOWAIT hint. Well, SQL server uses locks to ensure that the data in your table can be accessed, without risk of corruption or dirty reads. Let say when you are inserting or updating records within transaction then your table are locked and other transaction have to wait to read or change the records.

Sometime we are in situation that when our application request for data and if our request is locked by SQL server then rather than wait for some interval, its better to move out. So in that type of situations “NOWAIT” hint comes into the picture. It means when you apply this hint and requesting for select records from the table, the command fails immediately if current request is blocked and then reporting an error.

Let’s see this practically –

Open two query window in your SQL Server Management Studio. Assume that you have a table named Products and you tried to insert some records within one transaction as shown below in query window 1.

BEGIN TRAN

INSERT INTO Products
     VALUES (1, 'iPhone 5s', 55200)

--ROLLBACK
--COMMIT TRAN

To create the blocking situation, i have not committed transaction in above query

Query_Window_1

Now in second query window if you fire select statement command without any hint then you have to wait for infinite interval of time as our above transaction is not completed. But what happen if you fire select command with “NOWAIT” hint like below –

SELECT * FROM Products WITH (NOWAIT);

Above query results in an error as shown below.

Query_With_NoWaitHint

And you have to now manage the exception in your application and modify the code accordingly. Hope this post may help you in future.

That’s it for now folks. Hope you like this post.

Resolve same namespace collision between two assembly

One of my friend worked in his freelancing project. In his project there were more than 4 class library projects used within his application. But by mistake he had created the same namespace with same class name within the two class library project. After adding the references of that class library in his main project, when he tried to create an instance of that class, he got the following error.


Error 1
The type ‘Lib1.PatientDataContext’ exists in both ‘d:\TFS\Lib1\bin\Debug\Lib1.dll’ and ‘d:\TFS\Lib2\bin\Debug\Lib2.dll’

It means the C# compiler gets confused that which assembly it has to use for creating an instance of the class. That’s why it throws an above error.

So he asked me to resolve his issue. well, i have never faced this type of error before. So i did some googling and came to the solution that by using extern alias provided in C#, above issue can be resolve.  How ? Let’s see this practically by creating that scenario.

In Visual studio, let’s create one console application and named it as – NameSpaceCollisionApp. Now within the same solution of the application add the two new class library project. In each class library assembly add one new class named – PatientDataContext.cs. But keep in mind that both of these class are within same namespace. The code of both the class looks like this –

ClassLibrary1 / PatientDataContext.cs

namespace DataServiceFactory.DataContext
{
    public class PatientDataContext
    {
        public PatientDataContext()
        {
        }

        public override string ToString()
        {
            return "From Class Library 1";
        }
    }
}

ClassLibrary2 / PatientDataContext.cs

namespace DataServiceFactory.DataContext
{
    public class PatientDataContext
    {
        public PatientDataContext()
        {
        }

        public override string ToString()
        {
            return "From Class Library 2";
        }
    }
}

When you build the application, you’ll get the error highlighted above. So let’s resolve it by following the below steps.

Step-1: In the solution explorer, expand the “References” folder and  select the required assembly name then right click on it and open its property window.

Service1Property

Step-2: In the property window, change the aliases name from “global” to “Service1 (Whatever you want)”.

ChangeAliase

Step-3: Same way follow the Step-2 for ClassLibrary2 reference also.

Step-4: Now to use it in your main application, you need to add – extern alias “alias name”; line of code to the top of your source file, something like this –

extern alias Service1;
extern alias Service2;

using System;

namespace NameSpaceCollision
{
    class Program
    {
        static void Main(string[] args)
        {
            var object1 = new Service1.DataServiceFactory.DataContext.PatientDataContext();

            var object2 = new Service2.DataServiceFactory.DataContext.PatientDataContext();

            Console.WriteLine(object1);
            Console.WriteLine(object2);
            Console.ReadLine();
        }
    }
}

And we are done. Hope you like this post and hope it may help to my future readers. You can find the demo application from here.

 

Tip: Finding a value in any of the columns in a SQL Server table

Few days ago one of my colleague working in another team in my office asked me one question that he have had one table named “PatientDetail” with 58 columns and he wanted to find value from all varchar columns. As a developer the first solution came in my mind was – check value in each column like this FirstName = ‘hello’ OR LastName = ‘hello’ OR…so on. One thing to note here is there are more than 50 varchar columns out of 58 columns. So your query looks something like this –

SELECT * FROM PatientDetail
    WHERE FirstName = 'hello'
	  OR LastName = 'hello'
	  OR Column3 = 'hello'
	  OR Column4 = 'hello'
	  Or Column5 = 'hello'
	  OR ... Column52 = 'hello'

Don’t you think that above query becomes too much lengthy if i write each column name here. So What is the preferred solution ?

Hmm Do you know that you can use column name in “IN Clause” in sql server!! Really ??

Yes, the above query you can rewrite like this –

SELECT * FROM PatientDetail
     WHERE 'hello' IN (FirstName, LastName, Column3, Column4, Column5,...)

Well, we just reverse the IN Clause! and this is perfectly valid statement for sql server. Try it yourself. Now the only problem here is we still have to write more than 50 columns in “IN” clause. So to overcome this problem, let’s convert the above query into dynamic sql.

-- *** Prepare comma seperated column list ***
DECLARE @ColumnNames VARCHAR(3000)

SELECT @ColumnNames = ISNULL(@ColumnNames + ', ' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME)) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PatientDetail'
	AND DATA_TYPE IN ('char', 'nvarchar', 'text', 'varchar')

--PRINT @ColumnNames

DECLARE @query VARCHAR(MAX)=
	'SELECT * FROM PatientDetail
	WHERE ''hello'' IN (' + @ColumnNames + ')'
	
EXEC SP_EXECUTESQL @query

That’s it. Hope you like this tip. If you know any other solution then post it in the comments. Thank you for reading this post.

Zen Coding in Visual Studio 2015 using Web Essesntial Extension

Few months ago, I have added web essentials 2015 extension to my visual studio and studied its features. This is truly for web developers which makes their life easier. In this post we’ll see one of its great productivity booster feature and that is Zen Coding.

Zen Coding is a plug-in to generate HTML or CSS code in much faster way using jquery/css like selectors. Before we go into the detail let’s first install it in our visual studio.

Follow the below steps.

  • Open the visual studio.
  • Go to the Tools menu and click on Extensions and Updates menu item which will open one window.
    Web_Essesntials_Install
  • In the Extensions and Updates window, click on “Online” in the left pane. Once you click on it you got the list of extensions on the right side.

Web_Essesntials_Extension

  • Select the “Web Essesntials 2015” and click on “Download” button.

 

After installing this plug-in, it’s time to play with it.

Let’s say I want to generate simple table tr HTML. So here is how you can generate it with Zen coding.

table>tr>td

Now hit “Tab” key and it generate the following html code.

<table>
    <tr>
        <td></td>
    </tr>
</table>

Now I want to generate table with 3 “tr” and 2 “td” with some content in each “td”.

table>tr*3>td{hello world}*2

Now hit “Tab” key and it generate the following html code.

<table>
    <tr>
        <td>hello world</td>
        <td>hello world</td>
    </tr>
    <tr>
        <td>hello world</td>
        <td>hello world</td>
    </tr>
    <tr>
        <td>hello world</td>
        <td>hello world</td>
    </tr>
</table>

As a web developer you know all the html tags but what is the use of “*”(star) and “{ }”(curly braces) in the above zen code ?

Well, there are some more characters available which will have its own usage in zen code. Let’s first understand each one with example.

1). “#” – Creates an Id attribute for specified tag.

Example

div#testId>span

Hit “Tab” key which will generate the following html code.

<div id="testId">
    <span></span>
</div>

 

2). “.” – Creates a class attribute for specified tag.

Example

div.inlineBlock>ul>li

Hit “Tab” key which will generate the following html code.

<div class="inlineBlock">
    <ul>
        <li></li>
    </ul>
</div>

 

3). “[]” – Creates a custom attribute for specified tag.

Example

div[title]>button[value=”Save”]

generates the following html.

<div title="">
    <button value="Save"></button>
</div>

 

4). “>” – Creates a child element after specified tag.

Example

div>p>img

generates the following html.

<div>
    <p>
        <img src="" alt="" />
    </p>
</div>

 

5). “+” – Creates a Sibling element.

Example

div>p>img+a

generates the following html.

<div>
    <p>
        <img src="" alt="" />
        <a href=""></a>
    </p>
</div>

 

6). “*n” – Creates the specified elements for n number of times.

Example

div>ul>li*2>a

generates the following html.

<div>
    <ul>
        <li><a href=""></a></li>
        <li><a href=""></a></li>
    </ul>
</div>

 

7). “$$” – Generates the incremental numbers.

Example

table>tr#id$$*3>td

generates the following html.

<table>
    <tr id="id01">
        <td></td>
    </tr>
    <tr id="id02">
        <td></td>
    </tr>
    <tr id="id03">
        <td></td>
    </tr>
</table>

 

8). “{}” – Add the text inside the elements.

Example

div>p*4>span{This is my own text $$}

generates the following html.

<div>
    <p>
        <span>This is my own text 01</span>
    </p>
    <p>
        <span>This is my own text 02</span>
    </p>
    <p>
        <span>This is my own text 03</span>
    </p>
    <p>
        <span>This is my own text 04</span>
    </p>
</div>

 

9). “()” – Grouping the expression and generate HTML accordingly.

Example

table>(tr>th{column $$}*3)+(tr>td*3)

generates the following html.

<table>
    <tr>
        <th>column 01</th>
        <th>column 02</th>
        <th>column 03</th>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td></td>
    </tr>
</table>

 

Apart from all of the above, you can now also add Lorem Ipsum text in your HTML Code. Simply type lorem and hit TAB and a 30 words of Lorem Ipsum text is inserted. If you want to add only 4 words of Lorem Ipsum then just type below code and hit “Tab” key.

div>p*4>lorem4

<div>
    <p>Amet, consectetur adipiscing elit.</p>
    <p>Fusce vel sapien elit.</p>
    <p>In malesuada semper mi.</p>
    <p>Id sollicitudin urna fermentum.</p>
</div>

You can find many more other features from its official web site.

That’s it for now. Hope you enjoy this post.

Get list of property value from another list based on supplied property name using indexer in c#

Recently in one of our MVC application, I have had a requirement to read property value from one generic list by using property name, i.e. For exa. Consider one function named – GetPropertyValueList() which has one parameter named – propertyName. By using that parameter this function return the List<object> (List of property value).

OK, Let me explain further my requirement in detail. One of our end user wanted to migrate some of his customers manually. So I had to develop one page which displays specific customer list retrieve from the database. Now this page contains different sections to display list of property value. For exa. “LastName” property section displays only list of customer’s last name in radio-button list. Same way in other section let say “Age” property section displays only list of customer’s age in radio-button list. Same way for other properties too of customer class.

Now let’s see this practically. To achieve this functionality I need to develop several modal classes in my application’s modal layer. For demo purpose I have shown you these things in action in one console application in this post.

My first modal class is a normal “Customer” class.

public class Customer
{

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int Age { get; set; }

    public string MobileNo { get; set; }

    // Same way there are more than 20 properties in my mvc application
    //public string Address { get; set; }
}

My second modal class is “SourceCustomerInfo” class.

public class SourceCustomerInfo
{
	///<summary>
	/// To get the name of property at run time of Customer class
	/// </summary>

	public Customer customerField { get; set; }

	///<summary>
	/// Contain list of customers from database to migrate
	/// </summary>

	public List<Customer> customerList { get; set; }

}

 

In SourceCustomerInfo class first property customerField is used to get list of property value from customerList property. You can see the detail usage of this class in our console application.

My third modal class is “CustomerFieldValueList” class.

public class CustomerFieldValueList
{
	///<summary>
	/// Contain name of the property to render in the lable
	/// </summary>

	public string FieldName { get; set; }

	///<summary>
	/// Contain list of property value to render in the radio-button list
	/// </summary>

	public List<object> FieldValueList { get; set; }

}

Why I need to develop this modal ? Well, my MVC View renders all labels and its related radio-button list dynamically (By using above modal class). In the above class first property FieldName is used as label in MVC View and second property FieldValueList is used to fill radio-button list. Hmmm sorry for my bad English, But Here is the screen-shot of this class of what you’ll get at run time in each property for better understanding.

FieldValueList

All right, now the key point is here. To achieve my key requirement I need to use “Indexers” – a very special type of class member in C#. An indexer allows an object to be indexed like an array. The main use of “indexers” is to support the creation of specialized arrays or list. For more information on “Indexers” refer this MSDN link.

Here is the indexer that I have added in “Customer” class.

public class Customer
{

	///<summary>
	/// Get the value by supplied property name
	/// </summary>
	/// <param name="propertyName"></param>
	/// <returns></returns>
	public object this[string propertyName]
	{
		get
		{
			return this.GetType().GetProperty(propertyName).GetValue(this, null);
		}
	}

	public string FirstName { get; set; }

	public string LastName { get; set; }

	public int Age { get; set; }

	public string MobileNo { get; set; }

	// Same way there are more than 20 properties in my mvc application
	//public string Address { get; set; }

}

As you can see that I have read property value by using propertyName as parameter thru reflection in indexer.
Let’s combine all of these modal classes in one console application and see it in action. Here i have show you only the Main() method code. You can download the full source code from here.

static void Main(string[] args)
{
	var sourceCustomerInfo = new SourceCustomerInfo() {customerField = new Customer() };

	// Get the name of all property of customer class using reflection in array.
	var arrayPropNames = sourceCustomerInfo.customerField
							.GetType()
							.GetProperties()
							.Select(p => p.Name)
							.ToArray();
	// Fill the Customer list
	sourceCustomerInfo.customerList = new List<Customer>
	{
		new Customer() { FirstName="Kapil", LastName="Dev", Age=55, MobileNo="123" },
		new Customer() { FirstName="Sachin", LastName="Tendulkar", Age=45, MobileNo="123" },
		new Customer() { FirstName="Rahul", LastName="Dravid", Age=50, MobileNo="123" }
	};

	var fieldValueList = new List<CustomerFieldValueList>();
	//Now we iterate thru each property name that we already get in array
	foreach (var propertyName in arrayPropNames)
	{
		// Check propertyName is not blank or not our indexers
		if (!string.IsNullOrEmpty(propertyName) && propertyName != "Item")
		{
			// Initialize new instance of CustomerFieldValueList class
			var customerFieldValueList = new CustomerFieldValueList();

			// Set customerField with current property name
			customerFieldValueList.FieldName = propertyName;

			// Get the list of property value by supplied propertyName parameter using indexers
			// from the customerList property of SourceCustomerInfo class.
			customerFieldValueList.FieldValueList = sourceCustomerInfo.customerList.Select(p => p[propertyName]).ToList();

			// Now add this class instance into fieldValueList List.
			fieldValueList.Add(customerFieldValueList);
		}
	}

	// Now we are ready to render it in our MVC View.
	// Here we'll render it in console.
	fieldValueList.ForEach(obj =>
		{
			// Display FieldName value after 15 spaces
                        Console.WriteLine("FieldName  => {0, 15}", obj.FieldName);

			obj.FieldValueList.ForEach(lst =>
				{
					// Display property value after 15 spaces
					Console.WriteLine("     List  => {0, 15}", lst);
				});
			Console.WriteLine("=========================================");
		});

	Console.ReadLine();
}

And here is the output in console.

FinalResult

That’s it folks. Hope you like this post. Leave your comments.

The Role of data type precedence for COALESCE and ISNULL Function in SQL Server

Few days ago, I had a discussion regarding key difference between COALESCE and ISNULL function with one of my colleague in my office. Though he knows some of the differences but he doesn’t have any idea about data type precedence in both of these functions. How the data type of the result is determine here. So i thought to write the post regarding that.

In COALESCE data type of the result determines based on the data type precedence. As per the MSDN

Returns the data type of expression with the highest data type precedence.

 Let’s see it practically.

SELECT COALESCE (NULL, 1, GETDATE());

As this function evaluates the arguments in order and returns the first non-nullable value. In the above query first argument is null, so it evaluates second argument which is “1” and is of “Int” data type. But the third argument is “GetDate()” which is of “Datetime” data type and DATETIME data type has a higher precedence than INT. So the output of the above query is in DATETIME i.e. When we execute that query SQL Server will convert “1” into DATETIME.

1900-01-02 00:00:00.000

coalesce_exa1

Let’s see the another query.

SELECT COALESCE (NULL, 'Hello World', 1);

 coalesce_exa2

As you can see in above query that SQL Server will try to convert “Hello World” which is of VARCHAR data type into INT and results in error. Why ? because “INT” has highest data type precedence than “VARCHAR”. You can refer the Data Type Precedence chart from MSDN.

In ISNULL data type of the result determines based on the “first argument”, i.e data type precedence will not influenced the result. As per the MSDN

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value.

 NOTE: Here check_expression is the first argument and replacement_value is the second argument passed in the ISNULL  function.

Now Let’s see it practically.

DECLARE @var_datetime DATETIME

SELECT ISNULL(@var_datetime, 1)

isnull_exa1

Here variable “@var_datetime” is of type datetime and by default it’s value is NULL. So as the first argument is of type DATETIME which means result type is of datetime. So when you execute query, sql server will convert “1” into datetime and return the result.

Let’s see the another query.

DECLARE @var_int INT

SELECT ISNULL(@var_int, 'Hello World')

isnull_exa2

As you can see that first argument is of type INT. So SQL Server will try to convert “Hello World” which is of VARCHAR into INT and results in error.

That’s it for now. Hope you like this post.

Display query editor and results in seperate tab in SSMS

As a developer, Its very helpful for us to set Sql Server Management Studio’s (SSMS) environment as friendly as possible. In the recent conversation in my team, one of my colleague asked me that how you set query editor and its results in separate tabs in SSMS ? So i thought to write the post regarding this feature given in SSMS for my readers.

By Default when you execute the query, results and messages or execution plan tabs are shown in lower half of the query editor window as shown in below screen.

Before_Seperate_Tab

Now to set query, results and messages in separate tabs, we have to follow the below steps.

  • Click on the “Options…” in Tools menu.
  • Then expand “Query Results” in left pane and then select “Results to Grid” option.
  • Now in the right pane, Click on “Display results in a separate tab” checkbox.
  • Click OK.

Option_Window

That’s it. Now when you open new query window, you will see latest configuration changes as show in below image.

Result_With_SeperateTab

This feature is really very helpful when we have more than one results set or analyzing query execution plan. Hope you like this tips.

SQL Server -Misconception about Count(ColumnName) vs Count(1) vs Count(*)

Few days ago, I was having a healthy debate with one of my colleague in my team regarding Count(columnName) vs Count(1) vs Count(*). And i observed that he was having some misunderstanding regarding Count function in sql server. So i thought it’ll be a good idea to write post regarding it.

So what’s the misunderstanding is – According to him, Count(1) will give the count of records of first column in the table and Count(2) will give the count of records of second column in the table and so on. Well really is it ? Let’s see it practically.

First let’s create the table with some sample data in it.


DECLARE @Products TABLE
(
ProductID INT,
ProductName VARCHAR(50),
Price MONEY
)

INSERT INTO @Products
     VALUES (1, 'iPhone 6s', 55200),
     (1, 'HTC ONE E9', 18000),
     (1, 'Samsung Galaxy J7', 15100),
     (1, NULL, 10200),
     (1, 'MOTO G3', NULL)

First let’s see the Count(*) in action.

COUNT(*) : – It counts the number of records in the table regardless of NULL values and duplicate values. Pretty simple right.

SELECT Count(*) AS [Count(*)] FROM @Products

COUNT(1) / COUNT(‘Test’) :- It counts the number of records in the table regardless of NULL values and duplicate values.

But here 1 does NOT refer to an ordinal position of a column. This will not count the records of the first column of the table as COUNT(ColumnName) does.

 

SELECT Count(*) AS [Count(*)]            -- 5 Count
	,COUNT(1) AS [Count(1)]          -- 5 Count
	,COUNT('Test') AS [Count(Test)]   -- 5 Count
FROM @Products

Count_diffMethod

Here COUNT(1) and COUNT(‘Test’) gives you the same count because 1 or 2 or “Test” inside Count() function is a non-null expression. When you specify any non-null expression, sql server will treat them as shown in below image.

WithoutCount

Hmmm the picture is clear now. But then what is the difference between COUNT(*) and COUNT(1) and COUNT(‘Test’) ?

Well the answer is – there is absolutely no difference between them! Alright, Let’s move ahead.

COUNT(ColumnName) :- It counts the number of records of specified column name regardless of duplicate values, But it doesn’t count the NULL values. i.e. It counts only the non-null values.

SELECT Count(ProductID) AS CountProductID
,COUNT(ProductName) AS CountProductName
,COUNT(Price) AS CountPrice
FROM @Products

CountColumn

That’s it guys. Hope you enjoyed this post. Please leave your comment. Thank you.

SQL Pagination using OFFSET / FETCH NEXT Clause in SQL Server 2012

Well, I think we as a developer implemented a pagination in many application. But implement pagination with millions of records by keeping performance in mind always be a headache for developers. So we always tend to look for better approach and technique. From SQL Server 2012 sql server team at Microsoft comes with a lot of t-sql enhancement and OFFSET…FETCH NEXT clause is one of them which is used to implement pagination at sql server side in a very simple and elegant way. This feature is also appears to be less expensinve from the performance point of view than the previous technique we were using. We have recently upgraded one of our application database from SQL Server 2008 R2 to SQL Server 2014. Before SQL Server 2014, we were using Derived table with ROW_NUMBER() Function. In this post I’ll show you both the technique.

First let’s take a look at OFFSET…FETCH NEXT clause in action.

Create Schema

Let’s create a table with 3 millions of records.

SET NOCOUNT ON;

CREATE TABLE Customer
(
  [CustomerID] [int] IDENTITY(1,1) NOT NULL,
  FirstName AS ('First' + CAST(CustomerID AS VARCHAR)),
  LastName AS ('Last' + CAST(CustomerID AS VARCHAR)),
  MobileNo VARCHAR(20),
  LandLineNo VARCHAR(20),
  City VARCHAR(20),
  State VARCHAR(20),
  Zip VARCHAR(20)
)

INSERT INTO Customer VALUES('7625045789', '261-754-28354', 'NW', 'NW', '6592626')
GO 3000000

Syntax

Below syntax I have refer it from MSDN.

[ ORDER BY { expression }
OFFSET { integer_const | offset_row_count_expression } { ROW | ROWS }
FETCH { FIRST|NEXT } { ROW|ROWS } ONLY ]

Now, Let’s understand each part.

OFFSET { integer_const | offset_row_count_expression } { ROW | ROWS } :  It specifies the number of rows to skip.

FETCH { FIRST|NEXT } { ROW|ROWS } ONLY  :  It specifies the number of rows to return.

This clause is something like ready made option for paging. Very easy to implement and understand. See the below script where we see the records of page no 91 with page size of 15.

DECLARE @PageNo INT = 91, @RecordPerPage INT = 15

SELECT
	CustomerID
	,FirstName
	,LastName
	,MobileNo
	,LandLineNo
	,City
	,State
	,Zip
FROM Customer
ORDER BY CustomerID
OFFSET ((@PageNo - 1) * @RecordPerPage) ROWS
FETCH NEXT @RecordPerPage ROWS ONLY

Paging_With_Offset

Though this clause have some syntactic limitation like –

  • OFFSET clause is mandatory with FETCH. You cannot directly use ORDER BY…FETCH.
  • You cannot be combined TOP clause with OFFSET and FETCH in the same query.
  • To use OFFSET…FETCH NEXT clause ORDER BY is compulsory.

In SQL Server 2008, we have implemented same paging functionality using ROW_NUMBER() Function like this –

DECLARE @PageNo INT = 91,
@RecordPerPage INT = 15,
@FromRowNum INT,
@ToRowNum INT

SET @FromRowNum = ((@PageNo - 1) * @RecordPerPage + 1);

SET @ToRowNum = @PageNo * @RecordPerPage;

SELECT
	*
FROM (SELECT
		CustomerID
		,FirstName
		,LastName
		,MobileNo
		,LandLineNo
		,City
		,State
		,Zip
		,RowNum = ROW_NUMBER() OVER (ORDER BY CustomerID)
	FROM Customer) result
WHERE (result.RowNum >= @FromRowNum
AND result.RowNum <= @ToRowNum)

Paging_With_RowNumber

All-right both the query return the same result. But how OFFSET…FETCH NEXT clause is less expensive than above script which uses ROW_NUMBER() Function. Let’s take a look at execution plan of both the query.

Offset_ExecutionPlanRowNumber_ExecutionPlan

Please take a note that the execution plan shown in above image may differ as per the environment. I have tested it in my PC with 3 million records. So before applying it to your production server, please test it with more records.

That’s it guys. Hope you enjoyed this post. Thank you for your time.