Database internals

All About Indexes: The Very Basics

by Emil Drkušić
5 Jul 2016 There are many things that impact database performance. The most obvious is the amount of data: the more you have, the slower your database will be. While there are many ways to fix performance problems (like removing old data or using denormalization), the primary solution is to properly index your database. In this series, we’ll explain several very important indexing concepts, starting with the basics and ending with best practices.
Read more
Database internals

Support, Functionality, and Limitations of JSON in SQL Server 2016

by Jeffrey J. Keller
10 May 2016 As JSON continues to increase in popularity, support from third-party products is burgeoning as well. According to the Microsoft team, JSON support was one of the most requested features on Microsoft connect prior to its official announcement. While some JSON functionality is available in SQL Server 2016, significant limitations may hamper development and storage efforts. The Basics of JSON JSON is a language-independent format to store objects in attribute-value pairs.
Read more
Database internals

Why Does Oracle Sometimes Not Drop an Index Associated with a Primary Key or Unique Constraint?

by Bartłomiej Jańczak
29 Mar 2016 Database schema migration is never an easy job. In fact, it can really be a headache, even when you’re working with a familiar system. For example, at times Oracle 10g may not drop the associated index for a primary key or unique constraint that has been dropped. In this article, I am going to explain when and why this happens. The Story: I’ve been working on the development of an e-commerce platform.
Read more
Database internals

Exploring SQL Server 2016’s Powerful New Features

by Jeffrey J. Keller
22 Mar 2016 With the release of SQL Server 2016’s Community Technology preview, we’re finally able to get an in-depth look at the exciting options SQL Server 2016 includes. Two of the most anticipated are Always Encrypted and In-memory OLTP. Always Encrypted is a new method of consistent encryption intended to greatly simplify data protection. In-memory tables, while technically included in SQL 2014, were painful to implement and had significant limitations; SQL Server 2016 removes many of these and makes in-memory OLTP a viable option.
Read more
Database internals

SQL Server 2008 Datatypes and the Death of Datetime

by Jeffrey J. Keller
2 Feb 2016 Beginning with SQL Server 2008, the datatypes which store date and time were greatly improved. Despite this, many legacy databases (and often newly developed ones) still use Datetime and SmallDatetime, the original datatypes. Aside from the dwindling possibility of backwards compatibility issues, there is no advantage to using these old datatypes; as this article will show, the new implementations are superior in every way. SQL 2005 and Earlier: Datetime and SmallDatetime Datetime and SmallDatetime are the original temporal SQL Server datatypes, and the only options available in SQL Server 2005 and earlier.
Read more
Database internals

Using Different MySQL Storage Engines in Database Design

by Pavel Zimahorau
15 Dec 2015 Any database architect designing a MySQL database faces the issue of selecting the proper storage engine. Usually, an application uses only one engine: MyISAM or InnoDB. But let’s try to be a little more flexible and imagine how different storage engines can be used. The Initial Data Model To begin, let’s build a simplified data model for a CRM (customer relationship management) system that we’ll use to illustrate the point.
Read more
Database internals

An Unemotional Logical Look at SQL Server Naming Conventions

by Jeffrey J. Keller
8 Dec 2015 In the database world, there are some things that are universally agreed upon. Increased RAM is largely beneficial to DMBS systems. Spreading out data and log files on RAID improves performance. Naming conventions are not one of those things. This is a surprisingly polarizing topic, with the proponents of various methodologies firmly entrenched in their positions. And very vocal and passionate in their defense of the same. This article will delve into some of the specific conventions and the arguments on both sides, while attempting to present a reasonable conclusion for each point.
Read more
Database internals

Interesting Changes in MySQL 5.7

by Adrian Więch
5 Nov 2015 The General Availability version of MySQL is still version 5.6, but the development release of MySQL 5.7 definitely introduces some exciting changes to the world of database management systems. Is it worth giving a try? In this article, we’ll have a closer look at a few brand-new features that may help you decide to do so. Native JSON Support JSON (short for Java Script Object Notation) is a format for storing information which can be a good alternative to XML.
Read more
Database internals

Analysing the SQL Server Numeric Data Types

by Leandro Toniut
25 Aug 2015 A common challenge for database modellers is deciding which data type is the best fit for a particular column. It is a problem which involves consideration of both the properties and the scale of the data that will be stored, and in no case is this more evident than when handling numeric values because of the large variety of alternatives that most relational databases provide for their storage.
Read more
Database internals

ColumnStore Indexes in MS SQL Server

by Jeffrey J. Keller
11 Aug 2015 Introduced in SQL 2012, ColumnStore indexes differ greatly from standard row-based indexes. Intended for OLAP systems, these indexes store data in a highly compressed, segmented fashion with the column as the basis (rather than typical row-based indexes). This type of column-based index allows for great performance gains in data warehouses where table scans, rather than seeks, are performed. ColumnStore indexes have evolved significantly over the last few SQL Server versions:
Read more