After several tries that led to basic familiarity with SQL Server, I finally bit the bullet and learned it. Really learned it – so that I can accomplish the same tasks in SQL Server as I would in Access. I’m talking here about the desktop version of Microsoft SQL Server, also known as SQL Server Express and specifically, the query language T-SQL. It’s free and can handle 5x the database size that Access can. This will cover you for most any commercial supply chain model. One thing that helped my learning a great deal was a book called Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. I highly recommend picking it up if this blog resonates with you. It’s available on Kindle as well as traditional format.
The disclaimer here is that I have a long programming history, having started my career coding COBOL programs at Exxon before moving into the operations research arena. Over the years, I’ve continued to write programs in many different languages to help expedite my work or do tasks that queries alone can’t (or require queries that become so convoluted that it’s not worth developing them). I’ve also seen a good deal of SQL code in passing and understood the concept pretty well going in. This made it easy to read Ben-Gan’s book and come away ready to use what I learned right away.
Here are some reasons I resisted SQL Server so long:
You just install Microsoft Office and you have Access. SQL Server is NOT like that. It’s a number of different applications, server setup, parameter settings, etc. It is absolutely more like an IT/database application than a desktop tool.
- Access is visual. You drag your tables into your queries, drag the fields into columns you want to see, and even drag fields around to create joins.
- Access queries require no programming. To use SQL Server, you have to learn to write T-SQL code. Why this scared me away, I’m not sure. I guess I just didn’t feel like learning something new when Access had done the job successfully for so many years.
- Queries can be easily strung together in macros to automate the running of several in sequence.
I finally learned SQL Server because I was embarking on a project that I believed would require a large model and also I began to feel left in the dust by people who did know it. No 20-something modeler is going to make me look out-of-date!
So I went into learning SQL Server resigned but reluctant. And I came away… a FAN! Here are some reasons why:
- Access queries do one thing at a time. It’s pretty difficult to see the flow of a set of queries used to populate a model table. Yes, you can create macros and thus see the flow one query to the next, but read on…
- SQL Server queries use a file-based approach for invoking queries and other database tasks so you can encapsulate multiple queries that perform a modeling task together, with comments that make the process clear. A SQL script is simply a text file with comments, commands, etc.
- Query listed…
- “Use the Customer ID list and shipment history to create the Demand table”
- I believe that once you get used to it, this approach is faster than Access’ window approach
- Given how you can organize and comment the task flow, I think it’s far easier to come back to the same process a few weeks later, visualize the flow and remember what you did and why. In Access, this requires opening many windows (queries, macros, field builders, etc.), which is pretty slow if you think about it
- You can invoke programming/functions for repetitive tasks. These functions are stored in the database and always ready for use
Still, there are some good reasons to stick with Access for supply chain & transportation network design and they go back to my original reasons for sticking to it so long. If your model/process will be used or supported by people without programming background and not in a position to take on programming, then stick with Access.
My recommendation is that if you’ve been contemplating but avoiding embracing SQL Server, go ahead and take the plunge. With just a bit of investment, you’ll be efficient with it in no time. In fact, I think you’ll be more efficient than with Access.