Author: Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.

Switching Out Table Partitions in SQL Server: A Walkthrough

Kenneth Igiri

Introduction

A few years ago we were tasked with a business requirement for card data in a specific format for the purpose of something called “reconciliation”. The idea was to present the data in a table to an application which would consume and process the data which would have a retention period of six months. We had to create a new database for this business need and then create the core table as a partitioned table. The process described here is the process we use to ensure that data older than six months is moved out of the table in a clean fashion. Read More

AlwaysOn Availability Groups: Quorum

Kenneth Igiri

SQL Server AlwaysOn Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations using SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. The key benefits of AlwaysOn that we have experienced are as follows: Read More

Database Normalization: A Primer

Kenneth Igiri

The Relational Model of data management was first developed by Dr. Edgar F. Codd in 1969. Modern relational database management systems (RDBMSes) are aligned with the paradigm. The key structure identified with RDBMS is the logical structure called a “table”. Tables are primarily composed of rows and columns (also called records and attributes or tuples and fields). In a strict mathematical sense, the term table is actually referred to as a relation and accounts for the term “Relational Model”. In mathematics, a relation is a representation of a set.

The expression attribute gives a good description of the purpose of a column – it characterizes the set of rows associated with it. Each column must be of a particular data type and each row must have some unique identifying characteristics called “keys”. Data change is typically more efficient when done using the relational model while data retrieval may be faster with the older Hierarchical Model which has been redefined in model NoSQL systems. Read More

Parameter Sniffing Primer

Kenneth Igiri

Introduction

Developers are often told to use stored procedures in order to avoid the so-called ad hoc queries which can result in unnecessary bloating of the plan cache. You see, when recurrent SQL code is written inconsistently or when there’s code that generates dynamic SQL on the fly, SQL Server has a tendency to create an execution plan for each individual execution. This may decrease overall performance by:

  1. Demanding a compilation phase for every code execution.

  2. Bloating the Plan Cache with too many plan handles that may not be reused.

Read More

T-SQL Datetime Data Type

Kenneth Igiri

Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

  1. Datetime
  2. Smalldatetime
  3. Date
  4. Time
  5. Datetime2
  6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server. Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Kenneth Igiri

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More

Concatenation in Transact-SQL

Kenneth Igiri

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

Read More

In-depth Exploration of Row Level Security

Kenneth Igiri

Introduction

Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many relationship between instances and databases. However, there are cases where the solution demands that data is consolidated into one table. In such a case, there may be concerns about how to restrict access to the data.

Row Level Security was introduced in SQL Server 2016 as a solution to scenarios similar to the above. It allows you to restrict access to rows in a table based on conditions defined in an inline Table Valued Function called a Predicate Function. When a Predicate Function is applied to a user table containing consolidated data, the system can be configured to return different data sets to different users depending on their roles which in turn depends on their job descriptions or departments for example.

Read More

Dealing with NULLs in SQL Server

Kenneth Igiri

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More

Query Store: Showing the Impact of Indexes on Inserts

Kenneth Igiri

Introduction

It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More