Express: a free SQL Server version

With the SQL Express Edition, Microsoft offers an almost fully fledged version of SQL Server. Of course, it has some limitations otherwise no one would be buying the paid versions.

But if you want to start learning SQL, you can use the SQL Express edition just fine. And even for some production environments, it might be sufficient.

So let's have a look at its limitations.

The size of the database

With the SQL Express edition you can go up to 10 GB per database. That might not sound like a lot. But open up notepad and start typing until you reach 1 MB and take that time 1000. That is a lot of text.

And basically that is what goes into your database.

So unless you start storing files into your database, it will take some years before you reach 10 GB. If you even ever get to that point.

This limitation though might prevent you from reading a backup from a production server to your local machine. In that case you can use the SQL Developer Edition for free but do note that you cannot use this version for production. It is only to be used for testing and developing purposes.

No SQL Agent

The SQL Express edition does not have the SQL Agent so no scheduling of jobs. In general this is not a problem since you would shut down your machine anyway when you are not using it. You still can run Stored Procedures manually. Which is even better since you do not have to wait for the job to run.

Performance Limitations

The SQL Express edition puts a cap on the number of processors and the amount of memory that can be used. In other words, adding processors or memory wil not make your SQL Server run any faster.

Again, in general this will not be a problem. Unless you have a laptop with 16 processors and 128 GB of RAM and want to use all the power you have. But even than, SQL Server will perform just fine.

The SQL Express edition also puts a cap on the number of queries that can run simultaneaously. Something you will not even notice when you are the only person working on the database. If you plan to use it for a production environment with multiple users, it will perform just fine for about up to 50 users. But that also depends on the quality of your queries and the database design.

If you would use it for a webshop where dozens and dozens of people are purchasing at the same time I would recommend a paid version. But when your business is doing so well, the costs for the license would be the least of your problems!

Other limitations

There are some other limitations that have got to do with the availability. In a true production environment you might want to set up a back-up SQL Server that automatically takes over when the main SQL Server is down. That would require some extra hardware so it isn't even always used for production environments.

Such fall back scenarios cannot be done with the SQL Express Edition. But again, there is no need for this when you are just testing or developing.

In my experience

I've been working with SQL Server for over 20 years now and the SQL Express edition was always sufficient for use on my local machine. Performance is usually pretty good, in any case good enough for what I need to do.

If I need to work on a customer database that is over 10 GB, I usually ask them to make a copy on their SQL Server and then I can work on that one. Or I install the SQL Developer Edition (you can install both the Express and Developer side by side on the same machine) and use it only for that database. Just to be sure I stay within the Terms and Conditions of the License Agreement.

So where to get it?

You can download both the SQL Express and Developer edition from the Mircosoft Website here.

Besides the SQL Server itself you will need to install the Management Studio. In pre-2017 versions this was a part of the SQL Server installation itself. From SQL Server 2017 on, they made a separate install because people wanted to update to the latest version of the Management Studio without updatins SQL Server itself.

You can download the SQL Management Studio also from the Microsoft Website.

Let the fun begin!

Once you start learning SQL you will be surprised that the language itself is not that difficult. But to be good at it you will need to "speak" the language often to really master it.

Once you start working on an existing database, you will be surprised at what information you can get out of it that is not available in the appliction that fills the database.

And once you know how SQL works and what you can do with it, you will be able to work with reporting tools (like PowerBI, Crystal Reports or Sumatra Software) gets a lot easier. T-SQL is the foundation for most of these tools so a good understanding of it will take you a long, long way!

One word of caution to wrap this one up. Be carefull because once you master the basics you will find it can be very addictive!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!