What are the reasons to create a key-less, index-less database?

Question

TL;DR: why would you create a database with no keys of any kind (not even primary), and no indexes?

I joined a non-profit organization that was 14 months into migration to a custom built front-end for their financial processing. The organization has about 60 staff.

I have 6 years of software development experience, so they trust me enough to give me access to their database.

Lo and behold, the front-end SQL Server production database has absolutely no keys of any kind and no indexes. However, the interface actually works well. The external company that created the system are relatively responsive in creating new features, and the system actually works.

I've been working there for about 6 months. Most of the problems are relatively minor. I haven't seen any catastrophic errors due to mistakes on the company's part. Then again, there are no UAT (user acceptance tests) at all, and I have no idea how QA works at this external company. I only have contact with a sales/technical liaison.

I highly suspect this company is simply fleecing the non-profit for maintenance fees in the future. I got so sick of the slow DB that I added in a bunch of multi-column, non-clustered, non-unique indexes based on the most frequently used reports, and increased the DB speed by probably 100 times (I didn't bother to benchmark).

I have never seen this type of setup before, and the solution is so stupidly easy that I'm having difficulty understand why it wasn't done. I tried asking the sales contact but he just doesn't know what I'm talking about.

Am I correct with my assessment or is there a legit reason to do this?

---

Clarifications:

"Is the DB relationship in nature?"

I believe so. They run weekly and monthly DR/CR reports. It is very much an accounting DB. Even though there is no primary key setup, they have enforced the uniqueness at the application level.

"How old is the DB"

It is suppose to be a brand new, custom built system, running on SQL Server 2014. However, I also found out they put the DB in 2008 compatibility mode (compatibility code 100). I've asked about this but my contact haven't replied to me.

Answer 1

There are a number of possibilities listed in the comments. Several are not "legitimate" reasons not to have indexes or primary keys: ignorance on the part of the application developer, or accidental deletion of all indexes.

In a system where INSERT operations are prioritized over anything else, technically, indexes do slow things down. It may be that slow response for reporting, etc. was considered acceptable, as long as inserts completed as fast as possible.

Aaron Bertrand makes a critical note - a primary key is a constraint. It puts a restriction on the data, preventing duplicate values from being entered. The same holds true for a unique index.

With this is mind, there's at least one possible reason to avoid implementing those: the application is designed to manage the relation aspects of the data at the application level.

This is more likely if the application was originally developed a long time ago (say the 1980s or 1990s). I worked with a SQL Server DB for about 10 years that was supporting an application originally developed in FoxBase, and it had no foreign keys; all the relationships were maintained via triggers. They'd started using foreign keys for newer tables, but only started changing existing tables a few years ago.

Depending on how the code's written, it's actually possible that you could break the application by adding primary keys or unique indexes. It's unlikely, but not impossible.

Adding regular indexes would only break things if the application really needs to keep inserts as lean as possible. That may have been a limitation at some point, but it may be that it isn't any longer.

Note that this is just one possible scenario. As Aaron recommended, the best option is to communicate with the vendor. Checking to see how much tuning you can do to optimize your organization's workload isn't a bad idea, and respecting the application's creators (whether they deserver it or not :-) ) is a good way to start a relationship.