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
Tag: sql
Structured Query Language – Importance of learning SQL
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
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
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
Difference Between Inline and Out-of-Line Constraints
Constraints on tables and columns allow you to enforce the data quality. In SQL, there are two ways of creating constraints on a table: inline and out of line.
In this article, I am going to explore these constraints and the advantages they have, as well as explain which one I recommend and why.
How to Handle Errors in SQL Server Nested Transactions
In this article, we will explore SQL Server Nested Transactions, a transaction block with one or several transactions.
The image describes a simple model of the nested transaction.
Understanding PIVOT, UNPIVOT, and Reverse PIVOT Statements
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.
Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server
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.
SQL Server Table Partitioning
Problem
In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.
Why do we need table partitioning?
Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:
- Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
- Back up.
- Lock problems.
Microsoft SQL Operations Studio: Understanding and Installation
SQL Server Management Studio is considered as the default integrated graphical user interface tool that has been used for many years to configure, manage, monitor and administrate the SQL Server instances hosted on the local machines, on remote servers or in the cloud by all SQL Server administrators and developers. It provides us with editing, debugging and deploying environment for many languages including T-SQL, XML, MDX and DMX languages. Due to the fact that Microsoft SQL Server can be installed now on the Linux platform, and that the SQL Server Management Studio tool is not compatible with any operating system outside Microsoft Windows, the need for a new cross-platform graphical user interface appears.