Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server editions: Is Microsoft SQL Server Express right for you?

Which SQL Server edition is right for your IT shop? In this article by Bob Sheldon, find out if SQL Server Express is an option.

Editor's note: This is the first in a three-part series that will examine three popular versions of Microsoft SQL Server, along with the pros and cons of each. Check back next week for the second installment.

Deciding upon which edition of SQL Server 2012 to implement is not always a straightforward choice. You must balance the cost of the product against your business requirements, both now and in the future. Go too far on the cost-saving side and you end up sacrificing important functionality. If you make an error on the features side, you can waste a lot of money.

In this series, we look at three editions of SQL Server 2012: Express, Standard and Enterprise. We kick off the series with SQL Server 2012 Express, a great place to start for a very good reason; it's free. For only the cost of the time it takes to download and set it up, you get a complete database management system -- including lots of valuable extras -- without spending a dime.

For more on Microsoft SQL Sever Express

Why is SQL Server Express so popular?

Can you virtualize SQL Server Express?

The Express edition provides a full database engine you can freely deploy to a server or redistribute as an embedded part of an application. Express supports the same T-SQL language elements you find in any edition of SQL Server. Not only can you issue data manipulation language queries against the database, but you can also run data definition language statements to create such objects as views, triggers, cursors and stored procedures.

Express doesn't stop with basic T-SQL-- it also supports Full Text Search, native XML, the snapshot isolation level and the SQL Common Language Runtime. Plus, Express comes with a number of important tools built in, such as SQL Server Management Studio, Configuration Manager, the Advance Query Optimizer, Service Broker and the Import/Export Wizard.

However, the tools you get depend on which version of Express you download. SQL Server supports four editions:

  • Express: The core database engine, without any of the tools. Select this version if all connections to the database will be made remotely through your applications.
  • LocalDB: A lightweight version of the Express database engine, perfect for embedding into an application that needs a local system. You can also bundle this version with Visual Studio or other development tools. LocalDB runs in user mode rather than as a service, has few prerequisites needed to install it, and supports a zero-configuration installation.
  • Express with Tools: Your choice of either the Express or LocalDB database engine, along with a set of tools that let you set up and manage an Express installation.
  • Express with Advanced Services: The most complete edition of SQL Server Express. It includes all the management tools, plus reporting services and full text search.

Note that when you go to download SQL Server Express, you'll also find a fifth option: SQL Server Management Studio Express. This is a tools-only package that gives you the ability to manage SQL Server databases you might have already implemented -- including SQL Azure.

As you can see, Express gives you a lot to work with. Yet, it's just as important to understand what Express doesn't give you. Let's start with its size limitations. Express supports databases only up to 10 GB and can utilize only 1 GB of memory and up to four processor cores. So, if you're thinking of using Express, you should be thinking small. Also, Express doesn't include many of the advanced high availability and reliability features you find in other editions of SQL Server.

You'll find numerous other features unavailable in Express but included in other SQL Server editions. With Express, however, you still get much more than you pay for, and developers and independent software vendors (ISVs) have plenty of incentives to use this product. If you're new to SQL Server and simply want to learn how it works, you have much to gain. The best part is, you can upgrade from Express to a more sophisticated version of SQL Server, so you have little to lose by starting with Express and working your way up.

Next Steps

Microsoft SQL server 2016 gets an early review from beta testers

SQL Server Standard Editions and their meaning for IT

Dig Deeper on Microsoft SQL Server Tools and Utilities