Tag: sql

Implementing Paging Using OFFSET FETCH NEXT in SQL Server

Ben Richardson
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

Traditional and Natively Compiled Scalar-Valued User-Defined Functions

Esat Erkeç
Traditional and Natively Compiled Scalar-Valued User-Defined Functions
4.6 (91.11%) 9 votes

In the software programming world, there are several approaches used by developers that help them with effortless software development. Design patterns, object-oriented programming, test driven development are some of them. If we particularly mention the code reuse; “Reuse of a typed code. It is written once and can be used in most places. It is a basic concept of software engineering. It is a structure necessary for modular programming. ”

For this reason, user-defined functions in SQL Server help us to avoid rewriting of T-SQL queries. At the same time, they improve code readability. In this post, we will discuss the pros and cons (advantages and disadvantages) of the traditional and natively compiled scalar user-defined functions and make a simple performance test.

Read More

Similarities and Differences among RANK, DENSE_RANK and ROW_NUMBER Functions

Ben Richardson
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

Structured Query Language – Importance of learning SQL

Amarendra Babu
Structured Query Language – Importance of learning SQL
Rate this post

Computer programming language is a set of detailed instructions for computers or machines for performing specific actions. Through a programming language, we can control the behavior and output of a computer via accurate algorithms. A programming language is also called a computer language or programming system. The computer performs with various programming languages, such as SQL, Java, C++, Python, etc.. These languages allow computers and machines processing the large and complex data more effectively and quickly. That is the reason we find them valuable to organizations, particularly in the creation of system database management services. Read More

Understanding GROUPING and GROUPING_ID Functions in SQL Server

Ben Richardson
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

Ben Richardson
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

Ben Richardson
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