Implementing Paging Using OFFSET FETCH NEXT in SQL Server

Implementing Paging Using OFFSET FETCH NEXT in SQL Server
5 (100%) 2 votes

A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.

A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:

Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.

In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.

Read More

Similarities and Differences among RANK, DENSE_RANK and ROW_NUMBER Functions

Similarities and Differences among RANK, DENSE_RANK and ROW_NUMBER Functions
4.5 (90%) 2 votes

The RANK, DENSE_RANK and ROW_NUMBER functions are used to retrieve an increasing integer value. They start with a value based on the condition imposed by the ORDER BY clause. All of these functions require the ORDER BY clause to function properly. In case of partitioned data, the integer counter is reset to 1 for each partition.

In this article, we will study the RANK, DENSE_RANK and ROW_NUMBER functions in detail, but before that, let’s create dummy data that these functions can be used on unless your database is fully backed up. Read More

Understanding GROUPING and GROUPING_ID Functions in SQL Server

Understanding GROUPING and GROUPING_ID Functions in SQL Server
5 (100%) 1 vote

The ROLLUP and CUBE operators are used to return results aggregated by the columns in the GROUP BY clause.

The GROUPING and GROUPING_ID functions are used to identify whether the columns in the GROUP BY list are aggregated (using the ROLLUP or CUBE operators) or not.

There are two major differences between the GROUPING and GROUPING_ID Functions.

They are as follows:

  • The GROUPING function is applicable on a single column, whereas the column list for the GROUPING_ID function has to match the column list in the GROUP BY clause.
  • The GROUPING function indicates whether a column in the GROUP BY list is aggregated or not. It returns 1 if the result set is aggregated, and 0 if the result set is not aggregated.

On the other hand, the GROUPING_ID function also returns an integer. However, it performs the binary to decimal conversion after concatenating the outcome of all of the GROUPING functions.

In this article, we will see the GROUPING and GROUPING_ID functions in action with the help of examples. Read More

Logon Triggers in SQL Server

Logon Triggers in SQL Server
Rate this post

A logon trigger, as the name suggests, is a trigger that fires in response to a LOGON event in SQL Server.

In simple terms, a logon trigger fires whenever someone tries to establish a new connection to a database server. The trigger fires after the user authentication and the login phase completes but before the user session is actually initiated. Read More

Understanding PIVOT, UNPIVOT, and Reverse PIVOT Statements

Understanding PIVOT, UNPIVOT, and Reverse PIVOT Statements
Rate this post

The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the  UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.

In this article, we will study these three concepts on different examples.

Read More

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server
3.7 (73.33%) 3 votes

You often come across scenarios where you have to calculate a running total of a quantity.

A running total refers to the sum of values in all cells of a column that precedes the next cell in that particular column.

Let’s take a look at an example to make this clearer.

Read More

Creating Dynamic Pivot Table with QUOTENAME Function

Creating Dynamic Pivot Table with QUOTENAME Function
Rate this post

In my previous article on the basic pivot operator, we saw how pivot operator could be used to convert rows to columns, resulting in pivot tables. We saw that there were three main steps to create a pivot table. The first step was selecting the base data. The second step was converting the base data to a table-valued expression, and the final step involved applying a pivot operator to the temporary data, which resulted in the pivot table.

Read More