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. Because of this, running SQL Server, Microsoft’s RDBMS, on a Linux system is now a possibility.
The first two articles in this series highlighted SQL Server’s ability to organize data into user-defined windows and its aggregate functions . Part 3 will focus on other methods of aggregating and organizing data using built-in SQL Server features – specifically, its grouping functions.For consistency, the same base data will be used as in the first two parts of this series.SQL Server’s Grouping FunctionsThe GROUP BY clause is the basis for SQL Server’s grouping functions: ROLLUP, CUBE, and GROUPING SETS. At its simplest, GROUP BY summarizes rows based on user-expressed conditions:
In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed . This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful.OVER defines the user-specified range on which a function is applied. This can be combined with many SQL functions, allowing easy separation of data into defined windows within a single SQL query.
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”.
Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row.Well-known aggregate functions includeSUM,AVG,MIN,MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as
Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores afixed amountof 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. CHAR and VARCHAR are SQL data types dedicated to storing character values. They are available in almost every database engine. Due to database and encoding particulars, the storage of character values in CHAR and VARCHAR columns differs.
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 JSONJSON is a language-independent format to store objects in attribute-value pairs. Due to its compactness and flexibility, it is increasingly replacing XML. Its simplicity makes it superior to XML as a data-interchange format; although JSON lacks the structured overhead of XML, that feature is unimportant for data interchange.
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 areAlways EncryptedandIn-memory OLTP.Always Encryptedis 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
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 useDatetimeandSmallDatetime, 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 SmallDatetimeDatetimeandSmallDatetimeare the original temporal SQL Server datatypes, and the only options available in SQL Server 2005 and earlier. During this time, SQL Server date support was widely criticized for its lack of options. Other database systems, such as Oracle, already featured individual datatypes for date and time, as well as specific functionality for time zones.
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.In this article we’ll explore the numeric data types available in SQL Server and analyse their advantages, disadvantages and general usage.
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 IndexesBy default, SQL Server will create the table’s clustered index during the creation of the primary key:This can be overridden by specifying the NONCLUSTERED keyword during creation:The HEAP exists because the table does not have a clustered index defined.It’s a common misconception that the primary key and clustered index are bound together; the primary key is a special type of unique constraint (it does not allow NULL values, while normal unique constraints do). The Clustered index is a strictly-ordered B-tree intended to maximize scan performance. Only one clustered index per table is permitted.
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 IntegerWhen defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.Globally Unique Identifiers
“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. Similarly, gaps may arise when two users increment the same sequence concurrently.
Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simpletimecallednow. What you should rather think about is a time comprised ofhere and now.The Earth rotates around its own axis. When it’ssolar 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.To make communication easier, at the end of the 19th century, the Earth was divided into 24 hour-wide
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.SQL Server usesGETDATE()andSYSDATETIME()to get a current date and time. They are nondeterministic functions: views and expressions that reference these columns can’t be indexed. Both functions accept no arguments and return the local current date and time. The difference is, when we use
From the September 30th to October 1st in BelgiumSQL Server Daystook 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 Daystook 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 !).
SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL. Is it “S.Q.L” [ˈɛs kjuː ˈɛl] or is it “sequel” [ˈsiːkwəl]?SQL… Where it all startedLet’s start at the beginning.Relational databases came into existence with E.F. Codd’s 1970 publication “A Relational Model of Data for Large Shared Data Banks.” While Codd’s ideas were remarkable for the time, in San Jose, California, two colleagues named
The Vertabelo journey continues … We now have almost10,000users and the number of Vertabelo advocates keeps growing strong.Vertabelo users come from over100countries 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.Guess what we find out?Among all supported popular databases (PostgreSQL, MySQL, Microsoft SQL Server, SQlite, Oracle, IBM DB2, HSQLDB),
The concept ofmaterialized 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. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”
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.ExampleLet’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in theskijump_resultstable. There is no index on theskijump_resultstable. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.