SQL Server: The dark side of NVARCHAR

Introduction

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).

(more…)

What Can Query Plan Tell?

Introduction

SQL query describes the expected result, not the way to get the result. The set of specific steps the server must take to return the result is called the query execution plan. The plan is built by the optimizer. Selection of a plan affects execution speed, what makes it one of the most important elements of the query performance problem analysis.

Execution plan comprises operators and their properties that are interrelated with each other in the form of the tree structure. Each operator is responsible for a separate logical or physical operation. All together, they ensure the result described in the query text. Inside the tree, operators are represented by the class objects in the memory of SQL Server. Server users (that is, you and me) see the description generated in XML format with a specific schema, that is displayed graphically by the SQL Server Management Studio (SSMS) environment.

There are many various plan operators and even more properties. Besides, new ones emerge from time to time. This article does not dare to describe all possible variety of operators. Instead, I would like to share the most interesting additions in this subject and to remind some old but useful elements. (more…)

Tips for Read/Write Locks Depending on Transaction Isolation Level in MSSQL

Read Uncommitted

  • 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.

(more…)

Automatic Data Collection of Database Schema Changes in MS SQL Server

Introduction

Have you ever faced a situation when you need to make changes in a stored procedure or a view very quickly? I have, very often, especially at the implementation stage. Unfortunately, a version control system cannot help in this case. Still, how could I understand that something has been modified, and when?

This article describes a possible solution for automatic data collection about database schema changes in MS SQL Server. As usual, I will be glad to hear any alternative solutions.

(more…)

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution

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.

(more…)