Passing Data table as Parameter to Stored Procedures

Passing Data table as Parameter to Stored Procedures
3.6 (72%) 5 votes

Real-world database applications need to make multiple requests from the front end to the database in order to carry out all sorts of functions.

If an application is data-intensive, such as the ones used in banks or airports etc, the number of data trips can be huge. Each request to a database utilizes bandwidth and requires time to execute. Without the table-valued parameters, a front application needs to make multiple data trips in order to manipulate multiple rows of data. However, with table-valued parameters, multiple rows can be inserted, updated and deleted from a database using a single parameterized command that takes a table-valued parameter. Read More

Implementing Paging Using OFFSET FETCH NEXT in SQL Server

Implementing Paging Using OFFSET FETCH NEXT in SQL Server
4.4 (88.89%) 9 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.7 (93.33%) 6 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
4.3 (86.67%) 6 votes

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
5 (100%) 1 vote

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
4.3 (86%) 10 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