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.
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
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.
As an SQL consultant, I often work with others to create new database models from scratch or modify existing schemas. Aside from the technical aspects, collaborations on database models can be tricky. Moving from environment to environment poses its own challenges: security restrictions sometimes prevent direct access to SQL instances, and technical resources from across the globe can be difficult to seamlessly integrate.Deterrents these may be, but they are not insurmountable. By abstracting the modelling effort into an online system-independent tool, the focus can shift from fixing problems to developing a better database.
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.