If you were a fan of Linux and SQL Server, you basically had to have two operating systems at all times. But now, Microsoft has made a Linux version of their relational database management system. How does it work, and how well does it perform? Microsoft products used to be Windows-centric, but in recent years Microsoft CEO Satya Nadella has started moving the company towards cloud-based and open-source projects and enabling their software to run on other platforms.
Two new features in SQL Server 2016 make expanding your database’s historical data storage much easier. Here’s how to implement them. Storage. It’s a problem that anyone with a constantly-growing database has to face. We maintain great volumes of data that are rarely queried by business users. Most of these are historical or versioned data that someone checks once a year to see how “product X sales looked in 2005” or “record Z has changed over time”.
Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores a fixed amount of information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types. Before diving into the details, let’s start with some basic information.
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.
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.
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.
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.
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.
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:
This article reviews optimal placement of clustered and nonclustered indexes on OLTP databases, and explains how filtered indexes can be used to improve performance. Clustered Indexes By default, SQL Server will create the table’s clustered index during the creation of the primary key: CREATE TABLE PrimaryKeyTest (MyPK INT PRIMARY KEY) GO SELECT * FROM Sys.Indexes WHERE Object_ID = Object_ID ('PrimaryKeyTest') This can be overridden by specifying the NONCLUSTERED keyword during creation:
When designing a database, early decisions can have a huge impact on the performance and storage requirements. These decisions can be difficult to change later, as most subsequent work will depend on the physical model. This article highlights some common design decisions, flaws, and misconceptions. Creating a Primary Key: Uniqueidentifer or Integer When defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.
“Why is this query running so slowly?” It’s one of the phrases most commonly heard by DBAs and database developers when dealing with OLTP systems. Luckily, SQL Server provides a range of native options for determining exactly what’s occurring under the hood. Using execution plans, it’s possible to see the exact roadmap the SQL engine is following to retrieve data. This article will review the basics of reading and interpreting execution plans, then dig deeper into the internal processes and mechanics used by the SQL Server optimizer.
Generating unique integers is a very common task in database systems. Many applications require each row in a given table to hold a unique value. One way to tackle this problem is to use sequences. What are Sequences? A sequence is a database object which allows users to generate unique integer values. The sequence is incremented every time a sequence number is generated. The incrementation occurs even if the transaction rolls back, which may result in gaps between numbers.
Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simple time called now. What you should rather think about is a time comprised of here and now. The Earth rotates around its own axis. When it’s solar noon (the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.
As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.
From the September 30th to October 1st in Belgium SQL Server Days took place bringing together IT-Pros, developers, technical, non-technical people and everybody who wants to share their ideas, experience, enthusiasm and knowledge about Microsoft SQL Server. Event is organized for the 7th time and each time gathers great speakers. SQL Server Days took two full days with interesting sessions. First day was scheduled for deep dive trainings given by international and national speakers, the second one was more like technical conference for database administrators, developers and business intelligence professionals (Stay tuned for slides to download !
The Vertabelo journey continues … We now have almost 10,000 users and the number of Vertabelo advocates keeps growing strong. Vertabelo users come from over 100 countries and speak various languages. What unites them? The relational database. Let’s see what relational databases they use: We wanted to determine the most popular database engine among Vertabelo users based on one of three widely-used operating systems: Windows, Linux, Mac OS.
The concept of materialized views (MVs) is almost 15 years old; Oracle first introduced these views in the 8i version of its DBMS. However, some well known DB vendors (like MySQL) still don’t support MVs or have added this functionality only quite recently (it’s available in PostgreSQL since version 9.3, which was released just a year ago). In this article I’ll try to give you some tips about when you should use MVs in OLTP systems.
Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013. Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled “Indexes: The neglected performance all-rounder.” Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought.
Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way. Example Let’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in the skijump_results table. There is no index on the skijump_results table.