Author: Kenneth Igiri

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.

Basics of SQL Server Transaction Log

Kenneth Igiri Leave a comment
Total: 1 Average: 5

What is a Transaction Log?

There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.

In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.

Read More

MIN and MAX Aggregate Functions in SQL Server

Kenneth Igiri Leave a comment
Total: 3 Average: 5

SQL Server Functions

In database systems in general and in SQL Server in particular, functions are pieces of code that take zero or one input and return a single output or an array.

Table-Valued Functions which are typically user-defined can return an array, but in-built SQL Server functions are typically Scalar-Valued Functions. The third class of functions in SQL Server are Aggregate-Valued Functions. The MIN and MAX SQL Server functions are Aggregate-Valued Functions.

Read More

How to Implement Table Partitioning in SQL Server

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

Table partitioning is a technique used in SQL Server to physically organize the data stored in tables in different storage structures. In essence, it is splitting one large logical structure into smaller parts physically. The result is, we can improve performance for certain kinds of queries. More importantly, we can move data about using certain techniques.

Read More

SQL Server Express Limitations and Use Cases

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

SQL Server Express Edition is a free edition of SQL Server. Microsoft provides it for educational purposes as well as minor use cases. Students, hobbyists, and startups can use this edition to implement projects that do not require advanced features of SQL Server.

This article will examine the functionality of the SQL Server Express edition. But before we proceed to explore it, we’ll try to understand the concept of versions and editions in SQL Server.

Read More

Moving SQL Server Table to Different Filegroup

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

A table is a logical structure. When you create a table, you typically would not care which drives it sits on at the storage layer. However, if you are a database administrator, this knowledge may become essential if you need to move certain database portions to alternative storage or volume. Then, you might want definite tables to be on a particular volume or set of disks.

Filegroups in SQL Server offer that abstraction layer allowing us to control the physical location of our logical structures – tables, indexes, etc.

Read More

Fundamentals of Managing Datafiles in SQL Server

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

Datafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.

Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.

Read More

Use Cases for SQL Server MERGE Statement: Syncing Online and History Tables

Kenneth Igiri Leave a comment
Total: 1 Average: 5

INTRODUCTION

The SQL Server MERGE statement is an incredibly useful tool for carrying out DML operations based on comparing two tables or two data sets. Usage of this statement is actually like performing multiple operations in a single statement.

This article will explore three use cases that border on ensuring data between an online table and a history table in sync.

Read More

Add Columns to an Existing Table in SQL Server Database

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

A table is a two-dimensional logical structure and the fundamental means of storing data in relational database management systems. Its ‘row and column’ format is very much like the organization of the spreadsheet. 

Each new record introduced to a table is a row (also called a record or tuple), while rows are grouped into a finite set of columns (also called fields or attributes). Each column has a name and data type, and it serves as a reference for displaying result sets when querying data from relational database systems.

In this article, we explore the current options letting us add new columns to an existing table in SQL Server database. The use case may arise on many occasions. Particularly, it happens when there are updates for an application, and they demand adding new columns.

Read More

Views in SQL Server

Kenneth Igiri Leave a comment
Total: 1 Average: 5

Introduction

A view in SQL Server is a virtual table-like structure based on the result-set of an SQL statement. On the surface, a view is similar to a table with the signature structure of rows and columns. However, these rows and columns come from tables referenced in the query, which defines the View.

We use Views to focus on the concrete columns for the purposes they are created for. Views may also serve for security reasons. They filter out columns in the underlying tables which one does not want to make visible to certain users. Views filter columns like a WHERE clause filters rows.

Read More