As I was working in my dev copy of the client’s database today, I was using SQL Server quite a bit via SQL Server Management Studio SSMS. I noticed some slower performance on a couple of features I added multiple months ago that utilized new tables. So as I usually do, I checked for keys and indexes on the tables involved. Turned out I had not remembered to add foreign keys or indexes where tables were being ordered and joined so I added them.

The takeaway for me here was that I really need to remember to add the table relationships when creating the tables.

Access is pretty friendly with creating tables in an Access file and adds some indexes automatically for you if you’ve added a long integer type field. But SQL Server doesn’t do that for you. Furthermore, when you generate scripts from SQL Server via SSMS, sometimes the Include Indexes option is turned off by default and your scripts won’t build indexes on your dev database or worse yet, the live database if you are initially deploying it.

Thankfully, in this case, the performance had not degraded to the point where the users were complaining about it so I am able to simply save the day and make the employees feel better about their database performance improving.

So, as a reminder to myself again… ALWAYS review new database tables and review them for indexes that will be needed for the future. In particular for foreign keys that will be used to join tables, and any other fields that will be used as search criteria or criteria to order records.