I can still remember learning to store data in files, reading one record at a time, testing for end of lines and end of files. It was slow and cumbersome, but for a teenager, it was amazing. We built applications that could help run Dungeon and Dragons games, and the ability to store information about our games was amazing. I even adapted some of these routines to store data about various chemistry experiments in school, making my report writing (and data fudging) must easier.

In time I stumbled upon the basic database structure of dBase, thinking this was vastly superior. I could control the structure of each table, but have the power of quick access and indexes. Of course, I learned to make sure I opened all the indexes with the data file, otherwise I might "lose" some data over time.

Those first database structures felt great, and even superior to SQL Server when it was just one person. Initial queries were much quicker under dBase type applications (Clipper, FoxPro, etc) than under SQL Server, though that quickly changed as the number of users grew. For the last two decades, I've been entranced and thrilled with the power of relational systems. They solve many problems well, and I find them fairly easy to develop against.

Not everyone feels the same, and there have been many different types of data stores built in the last decade, each designed to solve different types of problems. I ran across a post about the different specialty databases available in AWS from Werner Vogels, Amazon's CTO. The piece is interesting as he notes that one type of database can't meet all the needs of modern applications, at least, it can't meet the needs of very large scale systems like Amazon's eCommerce site. Instead, there are situations and problems better solved by specialty systems.

Amazon built a key-value, extremely fast database to back a system that mostly needed those types of simple lookups. Over the years, in response to their needs or those of their customers, they've added other types of databases in their AWS cloud. Mr. Vogels lists six databases in AWS, including relational stores. He talks a bit about where each can be used. He also cites some customers that have had success in different applications with different data stores.

Microsoft has done similar things in Azure and their data platform. Most of us know SQL Server as the relational engine, but we have an in-memory component and with 2017, we also get graph capabilities, albeit rudimentary ones. In Azure, they have CosmosDB, which includes a number of different types of data stores (key-value, document, graph, etc.). I know Google has multiple databases available as well.

As we build larger systems that reach out to more people, I wonder sometimes if we ought to be architecting to use multiple data stores from the beginning, choosing the type that works best for the problem and not choosing to just use one database for all parts of an application. I already have some customers that supplement their SQL Server with Redis, Elastisearch, AWS or Azure queues, cloud storage buckets/containers, and more.

I'm a practical person, who likes to pick the solution that works well for the problem I need to solve. I have no issue with using multiple technologies, and as my career as advanced, I think it's easier than ever to do so. If you haven't considered other data stores, perhaps you want to learn a bit about them and see if they could help improve your system. Keeping an open mind and considering alternatives is good, and I recommend it. What I don't think you should do is choose another technology because you like it or are interested in learning more about it. Pick the solution that works best.

Join the debate, and respond to today's editorial on the forums

The statistics Stats_ID will match the index_id. In this case, the Primary Key will be on index ID 2 since there already is a clustered inde when the PK is added.

Ref: Does a Primary Key always have stats_id 1? - http://www.sqlservercentral.com/blogs/discussionofsqlserver/2018/06/28/does-a-primary-key-always-have-stats_id-1/

sys.stats - click here

» Discuss this question and answer on the forums