Join Lynn Langit for an in-depth discussion in this video Exploring NoSQL features in SQL Server, part of NoSQL for SQL Professionals.
- As I've discussed in this course, in addition to separate NoSQL databases, the big commercial vendors have responded to the NoSQL threat by adding NoSQL features into their commercial databases. So who is leading the pack here? In my estimation, it's Oracle and SQL Server. Now there are some other open-source relational database systems that have NoSQL functionality, most notably MySQL and some implementations of PostgreSQL, just don't have the scope to really cover all that here.
We're gonna take a look particularly at the database system that I know which is SQL Server. There are also managed open-source systems so what I mean by that is they are cloud based, usually under Amazon, although Google and Microsoft have some implementations, and they can either be pure open-source such as MySQL or they can be a blend of a commercial product like SQL Server, which has a relational component, and a non-relational component.
It's just a really interesting time in the database world because all this change that's coming out of the open-source world around dedicated NoSQL databases is forcing innovation throughout the industry and giving you, the customer, a whole bunch of choices. Now in terms of SQL Server, Microsoft started bringing out NoSQL features back in the 2012 edition and we have even more features in 2014 so let's just take a minute and talk about 'em because one of the things that's a bit confusing about both Oracle and Microsoft's implementation of NoSQL features in their RDBMS products is they don't tend to call them NoSQL.
They're kind of hidden and they're kinda hard to understand what they do and how they compare to open-source or even commercial dedicated NoSQL databases. So the first feature is a SQL Server Analysis Services Tabular Model and this was introduced in 2012 and enhanced in 2014 and the idea is this is kind of an OLAP store or a read-only or a read-primary store and it is created first in memory. But an interesting aspect of it is technically it is a wide column store so it is a type of wide column store and in some cases, rather than using a different type of wide column store such as like a Cassandra or an Hbase, it makes the most sense for a customer to use this capability which does require a separate engine install.
You have to install the Analysis Services Engine in Tabular Mode, but particularly, if it is a Microsoft customer and it's a smaller big data project, using Tabular Model can provide value quicker using a NoSQL set of technologies without going to an external NoSQL solution. Along those same lines, Microsoft brought out in 2012 and enhanced in 2014 this idea of a columnstore index and this is an abstraction that sits on top of a relational table for OLTP and it allows you to have some of the benefits of a wide column NoSQL implementation, particularly in terms of performance optimization in terms of read, so query speed basically, by just adding this abstraction on top of your existing relational database.
Now there's quite a lot of restrictions around it and I'll put some references in the notes so that you can understand what the restrictions are and again, these implementation details really do matter in your technology selection. But note that Microsoft started with a non-clustered columnstore index which is a secondary index and in 2014 they expanded that pretty dramatically with a clustered and optionally writable columnstore index which is a true wide column solution. Now just to be complete and because a lot of these NoSQL solutions do have an in-memory component, Microsoft also added an in-memory OLTP component in 2014.
- What is NoSQL?
- What is Hadoop?
- Exploring Redis, HBase, MongoDB, and Neo4j
- Exploring NoSQL features in Microsoft SQL Server
- Working with NoSQL data in the cloud
- Applying NoSQL choices to business scenarios
- Considering how data will be input and output at your business