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.
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.
Let us start our SQL journey to understand aggregating data in SQL and types of aggregations including simple and sliding aggregations.
Before we jump to the aggregations, it is worth considering interesting facts often missed by some developers when it comes to SQL in general and the aggregation in particular.
In this article, SQL refers to T-SQL which is the Microsoft version of SQL and has more features than the standard SQL. Read More
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.
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.
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:
In my previous article, Microsoft SQL Operations Studio: Understanding and Installation, we discussed deeply the need for a new SQL Server development and administration graphical user interface tool that can replace the default SQL Server user interface tool, the SQL Server Management Studio. We discussed deeply the new features and the advantages of SQL Operations Studio over the SQL Server Management Studio tool and how to download and install it to the Windows machine.
In this article, we will see the different configuration of the SQL Operations Window and how we can perform our daily tasks with it.
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.
This article is an effort to dissect the output of the sp_spaceused stored procedure.
Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s most widely-executed system stored procedure to find the disk space used by a database. This helps get a quick glimpse of the database usage. statistics. sp_spaceused is used to display the number of rows, the data size, index size, amount of used space, unused space by each object, and the unallocated size of the database. Although looking at the values given by sp_spaceused, one shouldn’t think of shrinking the database or data file or log file. Many a time, we are unaware of what we are doing. Many a time, we don’t know what would be the aftereffects of doing such resource intrinsic operations. The output of sp_spaceused tells us a lot about the current performance of the database. The unallocated column and the unused column tell us the free space left at the database and the table levels. Read More
The SQL Server trigger is a special type of stored procedures that is automatically executed when an event occurs in a specific database server. SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. The DDL triggers will be fired in response to different Data Definition Language (DDL) events, such as executing CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. The DDL trigger can respond to the DDL actions by preventing these changes from affecting the database, perform another action in response to these DDL actions or recording these changes that are executed against the database. 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.