SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process.
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.
I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.
I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.
Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.
Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.
A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.
Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.
Recently, I came across an application that generated DB queries. I understand that there is nothing new about that, but when application began running slow and I had to find out the reason of the slowdown, I was amazed to find these queries. Here is what SQL Server sometimes has to deal with: Read More
In this article, we are going to talk about using the nvarchar data type. We will explore how SQL Server stores this data type on the disk and how it is processed in the RAM. We will also examine how the size of nvarchar may affect performance.
Actual data size: nchar vs nvarchar
We use nvarchar when the size of column data entries are probably going to vary considerably. The storage size (in bytes) is twice as much the actual length of data entered + 2 bytes. This allows us to save disk storage in comparison of using nchar data type. Let us consider following example. We are creating two tables. One table contains nvarchar column, another table contains nchar columns. The size of the column is 2000 characters (4000 bytes).
- If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished and will return data entries of uncommitted transactions.
- If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
- Shared locks are not used. Identical to setting the NOLOCK hint for all selections in Read Committed.
- Exclusive locks are enabled during statement execution and disabled at the end of the transaction.
Databases that serve business applications should often support temporal data. For example, suppose a contract with a supplier is valid for a limited time only. It can be valid from a specific point in time onward, or it can be valid for a specific time interval—from a starting time point to an ending time point. In addition, many times you need to audit all changes in one or more tables. You might also need to be able to show the state at a specific point in time or all changes made to a table in a specific period of time. From the data integrity perspective, you might need to implement many additional temporal specific constraints.