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.
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.
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.