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?


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

  • is there an agreement (and specifications/requirements) between your organization and the company that created the application and database? – ypercubeᵀᴹ Oct 3 '18 at 15:35
  • 1
    There is, but are clients expected to explicitly specify that the DB should have primary keys? From a DBA's perspective, that's just stupid. It's like putting down "must have wheels" as a purchasing requirement when buying a car. – Nelson Oct 3 '18 at 15:36
  • 1
    We could speculate but there are dozens of reasons they wouldn't have all the indexes that you now know will improve performance - don't know better - think indexes slow things down (this is actually quite common, even more so for constraints) - plan to implement them but haven't gotten to it yet - tested with "works on my machine!" scale or just very different usage patterns - and on and on. If you want an answer, you'll have to get it from them. – Aaron Bertrand Oct 3 '18 at 15:38
  • 3
    Also the indexes could have been accidentally dropped at some point. – David Browne - Microsoft Oct 3 '18 at 15:39
  • 2
    A primary key doesn't just make the database faster. A primary key is a constraint. Don't confuse this with a clustered index (or the right clustered index for your workload). These are separate concepts that are often conflated because of SQL Server's default behavior. – Aaron Bertrand Oct 3 '18 at 15:46

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.

| improve this answer | |
  • As a side note: I did work in FoxBase about 30 years ago, and as best I recall, each table and index was stored as a separate file - and you could only have ten files open at any given time. I believe that included index files. That would make not using indexes tempting. – RDFozz Oct 3 '18 at 18:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.