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: (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.
In this article, we will discuss typical errors that newbie developers may face with while designing T-SQL code. In addition, we will have a look at the best practices and some useful tips that may help you when working with SQL Server, as well as workarounds to improve performance.
The focus of this article is going to be on utilizing JOINs. We will start off by talking a bit about how JOINs are going to happen and why you need to JOIN data. Then we will take a look at the JOIN types that we have available to us and how to use them.
JOINs in TSQL are typically going to be done on the FROM line.
Before we are getting to anything else, the real big question becomes — “Why do we have to do JOINs, and how are we actually going to perform our JOINs?” (more…)
In this article, we will talk about CHECK constraints. We will see how to add CHECK constraints to SQL Server table columns and discuss the pitfalls you may encounter when using this type of SQL Server constraints.
CHECK constraint basics
CHECK constraints are simply conditional statements (predicates that return TRUE or FALSE) that refer to table columns for maintaining data integrity. When one inserts data into a column or several columns in a single row, CHECK constraints come into action. They evaluate the data to be inserted. In case the data does not meet the condition specified in the CHECK constraint, the insertion fails.
- Briefly about Pivot tables
- Pivoting data by means of tools (dbForge Studio for MySQL)
- Pivoting data by means of SQL
- Automating data pivoting, creating query dynamically
Briefly about Pivot tables
This article deals with the transformation of table data from rows to columns. Such transformation is called pivoting tables. Often, the result of the pivot is a summary table in which statistical data are presented in the form suitable or required for a report. (more…)
This is the first article in a series of articles about In-Memory OLTP. It helps you to understand how the new Hekaton engine works internally. We will focus on details of in-memory optimized tables and indexes. This is the entry-level article, which means you do not need to be an SQL Server expert, however, you do need to have some basic knowledge about the traditional SQL Server engine. (more…)