Both EXCEPT and NOT IN operators are used to filter records from a table based on a specific criterion. In this article, we will look at these operators in detail and will investigate the differences between them. Read More
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
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
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.
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.
The OVER and PARTITION BY functions are both functions used to portion a results set according to specified criteria.
This article explains how these two functions can be used in conjunction to retrieve partitioned data in very specific ways.
Dynamic SQL and stored procedures are two of the most important components of SQL Server. In this article, we will look at the advantages and disadvantages of each of them and when to use them. Read More
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.
The pivot operator in SQL Server converts each row in the aggregated result set into corresponding columns in the output set. The pivot operator is particularly useful in writing cross-tabulation queries.
Let’s take a look at how it works in practice.
A transaction in SQL is a unit of execution that groups one or more tasks together. A transaction is considered successful if all the tasks within it are executed without error.
However, if any of the tasks within a transaction fails to execute, the whole transaction fails. A transaction has only two results: successful or failed. Read More