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?
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.
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.
Two new features in SQL Server 2016 make expanding your database’s historical data storage much easier. Here’s how to implement them.
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.
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.
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.
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, known from 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.
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.